|
Return to Newsletter Contents...
SSIS Looping with an ADO Recordset (Part 2)
by:
Sean Beal, Senior Consultant,
CRCP
Last month, we began creating an SSIS
(SQL Server Integration Services) package that leverages the Foreach Loop
container to populate a text file from an ADO Recordset.
We configured variables, a SQL task, and a Foreach Loop container.
This month, we’ll complete our package by defining the Data Flow objects
within the loop that will actually create the desired destination file.
This article assumes a basic working knowledge of SSIS and the Business
Intelligence Development Studio (or Visual Studio 2005).
To begin, we need to create our first
Data Flow object by dragging it into the Foreach Loop container and defining the
source and destination connections.
From the Toolbox, select a Data Flow Task and drag it into the enclosed area of
the Foreach Loop container. The Data
Flow will have a default name of ‘Data Flow Task’; click on this text to rename
it to ‘Add Company Header’. The
result should look similar to Figure 1.
Figure 1
Double-click on the Data Flow task to
configure the source and destination connections.
Let’s begin with the source,
which in our case will be an OLE DB Source connection; select this object from
the ‘Data Flow Sources’ section of the Toolbox, and drag it onto the Data Flow
workspace. By double-clicking the
OLE DB Source object, we can access the OLE DB Source Editor, and configure our
connection.
Figure 2 shows the OLE DB Source
Editor. We need to specify our
actual database connection (in this case, the same connection we used last
month) and our data access mode.
We’ll choose ‘SQL command’ for this example, which will require us to enter a
SQL command text.
Figure 2
Notice the query entered.
We’ve used a question mark to indicate a parameter; this is how we will
link in the records from our outside loop.
In last month’s article we created a variable called company_id and
assigned it to the first column of the result set through which we are looping.
We’ll assign that variable now to this parameter by clicking on the
‘Parameters…’ button and selecting the company_id variable (see Figure 3).
Figure 3
Now we need to configure our
destination. From the Toolbox, in
the ‘Data Flow Destinations’ section, select a Flat File Destination and drag it
onto the Data Flow workspace.
Connect this destination to the OLE DB Source by clicking on the OLE DB Source
and dragging the green arrow onto the Flat File Destination object.
Once the source and destination are connected, we can double-click the
Flat File Destination object to access the Editor.
Our first step when dealing with a
Flat File destination is to configure the Flat File connection manager.
In order to do this, click on the ‘New…’ button next to the dropdown (see
Figure 4). For this example, we’ll
accept the default of ‘Delimited’ format, which will allow us to proceed to the
Flat File Connection Manager Editor.
Here we will need to enter a Connection manager name and specify an actual file
name and path (see Figure 5). We’re
going to call this the ‘Company Header Text File’ and specify a physical file of
‘companies.txt’ located in My Documents.
Click ‘OK’ when done.
After we’ve configured the Flat File
connection manager, we need to ensure that we uncheck the ‘Overwrite data in the
file’ option of the Flat File Destination Editor.
Remember, we are writing to this file once for each record in a loop; if
we overwrite it each time, we will be left with only the final record of the
loop!
Figure 4
Figure 5
At this point we can (and probably
should!) test our Package. Select
Debug - Start Debugging from the toolbar in order to run the package in debug
mode. If all goes well, you should
end up with a text file similar to Figure 6:
Figure 6
We’ve accomplished a basic loop, and
this could be all that we need in some cases.
However, last month we mentioned that the requirements for this text file
were a little unusual in that we wanted parent and child records in the same
file, sequentially. In order to do
this, we need to add another Data Flow task to our Foreach Loop container, this
time to retrieve an address record for each Company. First, we’ll return to the
Control Flow workspace by clicking on the ‘Control Flow’ tab and drag another
Data Flow object into our Foreach Loop container.
We’ll call this ‘Add Address Detail’ and connect it to the ‘Add Company
Header’ Data Flow we just completed. The result should look similar to Figure 7.
Figure 7
Next we’ll configure our source and
destination connections as we did previously.
Our source will be another OLE DB Source, and we will use another SQL
command, this time querying the Company Address table.
We will link our query with a parameter just as before; the only
difference will be the query itself, which has a different set of columns (see
Figure 8).
Figure 8
We’ll add another Flat File
Destination object after connecting the source to the destination as before.
We will create a new Flat File connection
manager, again with the delimited file format.
However, we will select the same physical file and path as we did before
(companies.txt). This allows
us to specify different columns, but insert them into the same text file.
Again, be sure to uncheck the ‘Overwrite data in the file’ option of the
Flat File Destination Editor.
We can now test our completed package.
We will want to first remove all the previous text from our companies.txt
file, or delete the file so that a new one will be created.
After we debug the package, the results should be similar to Figure 9:
Figure 9
That’s it!
We’ve used SSIS and the new Foreach Loop container to loop through an ADO
Recordset and create a text file with both parent and child records.
Hopefully this example will spark ideas about how SSIS can be used to
meet the data manipulation requirements of your environment.
Until next time, happy looping!
Go to Top |
Return to Newsletter Contents
|
