Bret Stateham is giving this talk. He is a great presenter. Very animated and speaks clearly. He also explains complicated details very easily in a friendly manner. If you have a chance to hear this guy speak definitely take advantage of that!
Boy I wish I had a recording of this. I’d love my two DBA colleagues to see this. All but two of our SQL servers are 2000, simply because that’s the latest available when the systems were installed. It works great but you don’t have access to the great tools, such as SSIS, in 2005 or 2008. I’ve pinged Lynn Langit and Daniel Egan about possibly being a resource for recording these sessions in the future. You can read about the initial details in my blog post here. <update>OK, here’s an update. I’m 45 minutes into the session and Bret is hilarious! He definitely makes this topic a lot of fun by his passion and animated style. Lots o’ fun!</update>
This is really an intro to SSIS, which is exactly what I need. His Data Flow Task talk is the next session.
One data source we’re looking to need is an SFTP site. Currently this isn’t in SSIS but we do have a vendor’s connector from n Software. This will be fun to try out. If for some reason it doesn’t work out Bret said we can create one through the Script Task or even a formal task developed in C#. I may end up doing something like this to hit our GIS server using REST. I did see a Web Task and it’s quite possible that it can consume REST already. This means that we’d have to consume XML and possibly convert this to some other usable format if necessary.
Bret just gave a great tip that may have frustrated me for a while. The variables you create a scoped. If you have a task selected and you create a variable, that variable will be scoped to that task, meaning it will only be available to that task and will disappear after that task is finished. Chances are you want your variable to be accessible by other tasks in your package. In this case make sure you click an empty area of your design surface prior to creating the variable. That will scope it to the package itself.
In SSIS 2005 you could only write Scripts in VB .Net, however, in 2008 you have the ability to use C#.
Man! Debugging in SSIS is awesome! I can’t way to dive into this stuff at work.
Loops, Sequence Containers and Groups:
Loops are exactly as they sound. The allow you to take a set of tasks and loop through them, much like a subroutine.
Sequence Containers do not loop. They do, however, offer scope and environment properties. You can define variables that are only available the that sequence. You can also set transaction properties on that individual container. Maybe you have several different containers each with their own transaction level. Pretty neat.
Groups are simply a design feature. They are simply there for organizing the items on your design surface but have no properties and no impact on the package at all.
The For Each Loop allows you to specify the collection your picking items from. The possibilities include XML objects (for each node) and even SMO (SQL Management Objects) for things like “for each server do this”. That’s pretty cool.
Bret showed a great example where he needs to process files every day that may not have the same filename. For instance this may be an AS400 dumping csv files that were time stamped. He generates what the filename should be in a Script Task, saves that into a variable, and then his File Task uses that variable as it’s connection string. Sweet. We need to do this as well and this would really help.
This was a great talk. SSIS is definitely something I’ve been looking to get into ever since SQL 2005. It looks like this is something I can dive into right away. For the more complex things Bret recommended the book Professional SQL Server 2005 Integration Services from Wrox.
Unfortunately I can’t attend Bret’s next session since I’m attending Mike Roth’s Asterisk session. Mike and I are working on a side project together using Asterisk, so I should give audience support and also learn what the heck we’ll be using! 🙂