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! š
Technorati Tags:
code Camp 2009,
SSIS