Zahid's blog on Data Engineering and BI

Recently, I have been trying to make a cursor using SSIS components and came up with this idea. The trick is explained below:

Let’s say, we have a record set and we need to loop through the records to concatenate/append one after another in a sequential manner. For example, you have 6 records:

A

B

C

D

E

And, you want to make a comma separated list of records like this :     ‘A’,’B’,’C’,’D’,’E’,

First, you need to drag a Data Flow Component onto the designer area. Double click on it and it will take u to Data Flow Tab. Then u have to drag a Source component. After u set the connection string and everything inside the source component, u can write a query which will produce the record set. Next, u have to declare a package variable (User::Panels) with Object data type. Now, take a “Recordset Destination” and connect the source with it.

Now, double click on the Recordset Destination and the Advanced Editor will open. In the Custom Properties section, there is a field called VariableName. Choose the Object type variable (User::Panels) as the value of that field.

So, the record set will be stored inside that variable.

Next, a Foreach loop Container will iterate through the records stored inside that object variable. So, go back to the control flow tab and drag a Foreach loop container. Connect that with the previously designed data flow task. Double click the container and Editor will open. Go to the collection tab and select Foreach ADO Enumerator as Enumerator. Select ADO object source variable from the drop down list below. See the figure:

Now, the foreach container will read records one by one and keep it in another variable. We need to go to Variable Mappings section to mention that variable (User::PanelName). So, u go to variable mapping section and select another variable (string type for our example). Index will be 0, since we will read the first field (there is only one field in our example) of our records set.

Now, comes the final part. Drag an Execute SQL task inside the Foreach Loop Container and set its connection. Next, go to the Parameter Mapping section of the SQL task and set two variables. The first one is the same as the one used in Foreach loop container (User::PanelName in our example). The second one is a new string type variable (User::FinalString) which will store the final concatenated string.

Next, go to the general section, set Single row as the ResultSet and write down the below query in SQL statement.

Select ”” + ? + ”” + ‘,’ + ? FinalString.

This query gets the first variable and append the FinalString variable to its end and stores the final result in FinalString variable.

It works like,     FinalString = Panelnames + FinalString.

The default value for FinalString is blank (”).

Let’s say, the first record in PanelName variable is A. So, the first iteration will make it………… ” ‘A’,” and keep it in FinalString variable. If the next record from the loop is B, the query makes it………………………..     “‘B’,’A’,” and keeps the new string in FinalString variable. This way, we append each record to the other as we move through the loop. Now, the question is, where are we telling that the result should be kept in the FinalString variable? Yes, that’s why we need to go to the Result Set section of the Execute SQL task editor and map the result to FinalString variable there. Below is the screenshot of the General tab and Result tab of the Execute SQL Task Editor:

Now, u r good to go and execute the package.