|
Return to Newsletter Contents...
SSIS Looping with and ADO Recordset (Part 1)
by:
Sean Beal, Senior Consultant,
CRCP
Recently, we had the opportunity to work with a client who
needed an import text file created from data contained in a SQL Server database. The import text file was unique in that a single file needed to contain
both parent and child records, sequentially.
Due to the relatively low volume of data and the unique logic needed to
facilitate this, it was decided that this might be a good opportunity to use the
new Foreach Loop container in SQL Server 2005 Integration Services (SSIS).
Enumerating records or files in SQL Server 2000 Data
Transformation Services (DTS) was tricky at best.
The Foreach Loop Container in SSIS makes this much, much easier, and
provides functionality for looping through a collection of files, an ADO
Recordset, an Array and more. In
this example, we’ll be using an Execute SQL Task to create an ADO ResultSet;
we’ll then use package variables to pass information to the Foreach Loop
Container, where we’ll add lines to the text file based on the current record’s
value.
This article assumes that you have created a new SSIS
package and connected to a data source (for help on this, see the Help menu in
the SQL Server Business Intelligence Development Studio). After this, the first step is to create the two variables we will use;
we can add variables from the Variables window (View | Other Windows |
Variables). The first variable will
be of the data type Object, and will
be used to hold our ResultSet. The
second variable will be of the data type
String, and will be used to hold the ID of each record as it is enumerated.
We’ll call our object variable ‘company’ and our string variable ‘company_id’
(see Figure 1a).
 Figure
1a
The next step is to create our ADO ResultSet using the Execute SQL Task (figure
1b). Add an Execute SQL Task to the
package by dragging it onto the workspace.
We’ll name the task ‘Get Companies’.
Double click the task to edit the task properties, enter the connection
created earlier, and enter the SQL statement you wish to use in the SQLStatement
property. For this example, we will use
a stored procedure called rsp_company_list.
Finally, change the ‘Result Set’ property from ‘None’ to ‘Full Result
Set’. This will allow the results
of our stored procedure to be output to our object variable.
Finally, click on the ‘Result Set’ option in the left hand pane to go to
the Result Set page. Click ‘Add’ to
add a new item to the list. Change the default Result Name to 0, and select the ‘company’
variable created earlier as the Variable Name.
This assigns the first result set (0) from our stored procedure to the
object variable ‘company’ (Figure 2a).
Click on ‘OK’ to exit the Task Editor.
 Figure
1b
 Figure
2a
We can now add our Foreach Loop container to the package
by dragging it onto our work surface.
Connect the Execute SQL Task by selecting it and dragging the green arrow
to the Foreach Loop Container.
Double click the Foreach Loop Container to edit the properties, and select the
‘Collection’ option in the right hand pane.
We need to change the Enumerator type from the default of ‘Foreach File’
to ‘Foreach ADO Enumerator’. We
will also need to configure the Enumerator to use the company variable as its
object source (Figure 2b).
Figure 2b
Click on the ‘Variable Mappings’ option in the right hand
pane to map our string variable (company_id) to the desired column of our result
set (in this case 0, since we want the first column of a zero based list).
The result should look like Figure 2c.
 Figure
2c
At this point, we’ve defined two variables and used them in
conjunction with a SQL Task to feed a Foreach Loop Container.
Next month, we’ll define the processes and tasks within the container
that occur for each record passed in.
See you then!
Go to Top |
Return to Newsletter Contents
|
