Been a bit quiet on here as have been getting ready for our SQL Server April 05 MSDN Update across 6 cities in Australia, this getting ready business is an interesting combination of stress knowing you have to have everything ready for a hard deadline and a fascination when it clicks how something works, made even more challenging when the software in question is in a state of flux as it goes through refinement from customer feedback etc…
Well I struck one on these moments getting ready for the SQL Server Integration Service (SSIS) session I’m running. One of the compelling features in the SSIS which replaces DTS is the notion of a “foreach” loop, ie Foreach file in a directory, foreach row in a table etc do something to effect some data flow task.
Well between IDW 11 (which from memory equates to about the Dec 04 CTP build of SQL 2005) the way you pass parameters from a Foreach loop to data connector has changed, you used to do it by setting a Mapping property.
The way you now approach this now is by setting a Package Scope Variable to the value returned by the Foreach using the Foreach “Variable Mapping” dialogue.
I wanted to take the filename returned from the Foreach container task and assign it to the connection string of a data connector. To do this you now open the “Expressions” dialogue box from the data connectors Property window where you can set the value of a number of variables for this control, one of them being the Connectionstring.
From here you can assign the value of the Package Scope Variable you set in the Foreach loop to the connectionstring simply by simply dragging the variable to the expressions box.
LOL, I’m embarrassed to admit how long this too to figure this, yeah, ok, may be I’m just a bit slowL
btw, if you are anything like me, you go along to a session and watch a demo thinking that it would be cool to recreate it to learn some more, well if you are interest I’ve put my demo crib note for my SSIS Session here.