Cursor Using Foreach Ado Enumerator in SSIS

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.

Advertisements

About Zahid

Team Lead at IMS Health (www.imshealth.com). A part-time consultant and trainer. Fields of expertise are ETL, BI Reporting (Microstrategy, Excel, ASP.NET Dashboards etc.) Data Warehouse, OLAP Cube, MDX etc.
This entry was posted in SSIS (SQL Server Integration Service) and tagged , , , , . Bookmark the permalink.

9 Responses to Cursor Using Foreach Ado Enumerator in SSIS

  1. Addemaan says:

    Hi there!
    I like your soulution.
    Can you please tell me what I need to change if I want to fill “FinalString” with integers instead of strings?

  2. Zahid says:

    I think the only thing u need to change is the type of the variable PanelName. In my example, it is String but for Integer, it has to be changed to Int32. Hopefully, this will do.

  3. Narmada says:

    HI Can you tell me How to implement Cursor in SSIS to push datas into SQL Table ..?

  4. Zahid says:

    Narmada,
    The SQL Task dragged inside the foreach loop can do the insertion. You can build a string for insertion and execute it each time the loop runs. Let me know if u need further details.

  5. RJ Samp says:

    this doesn’t compile:
    Select ”” + ? + ”” + ‘,’ + ? FinalString.

    Nor does this
    Select ”” + ? + ”” + ‘,’ + ? FinalString

    The “”” kind’s of stuff probably got botched in the HTML rendering…..but it’s tough to tell what you need here.
    Step 1:
    Select ‘ + ? + ‘ + ‘;’ + ? FinalString

  6. anonymous says:

    Hi Zahid,
    i’m getting error in execute sql task,syntax error near select “”+?””+’,’+? FinalString

  7. Libby says:

    HI Can you tell me How to implement Cursor in SSIS to push datas into SQL Table. Could you please provide and example.

  8. Libby says:

    HI Can you tell me How to implement Cursor in SSIS to push data into SQL Table. I need and example of the insert statement for the SQL Task dragged inside the foreach loop. Thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s