Have you ever needed to export different data sets to different flat files? Each of these data sets could be customer information for different clients – but they all require the same fields. I have run into this requirement a few times and have found that it is not as hard as it may sound. To evolve that requirement, I have also had the requirement to export multiple different files for each of the clients. For the sake of this post though, I will just stick to how to do the first level. (It is only a little more complex but not much different to add the extra requirement.)
SSIS to Save the Day
SSIS has a looping mechanism built in to help achieve this.
If you want to learn more about this particular data flow object, here is the MSDN resource on it.
When trying to create an SSIS package solution that meets our requirements, the first step (in my opinion) is to make sure you have an Object variable created for use by the Foreach Loop Container.
To use this variable in the foreach loop container, we first need to load data into it. This is accomplished by using an Execute SQL Task.
Note: There is a red-x in this image simply because I have not defined any properties for this object.
The Execute SQL Task needs to be given a data source and a SQL command to run. The result set of this task should be set to full result-set on the general page. On the result-set page, set the result-set to be stored in the object variable that we created already. On the same page, you should set the result name to a value of “0” (zero without the quotes).
Now we can start looking more at the Foreach Loop Container. When we open the properties of the Foreach Loop Container, we want to go to the Collection page and adjust two settings. The first is to select the appropriate type from the dropdown.
In this case, we will select ForEach ADO Enumerator. The next change to be made is the next drop down labeled “ADO Object source variable:”. In this box we will select the object variable that we already created.
Now we need to configure the Variable Mappings page. For this you can either create a new variable from this page, or use variables you have already defined. Let’s Create some variables from here.
Once that is established we can work on configuring the Data Flow task. For the sake of the example, we can do something simple. It is a good idea to make sure you have your connection managers created by this point. If they aren’t there – then let’s create some. First, let’s create an OLEDB connection. Second, we need to create a Flat File Connection Manager. I won’t cover how to create those connection objects. I will cover how to make a configuration change to the Flat File Connection Manager though. To make sure the files will be written differently – we should use an expression on this connection manager.
In your flat file connection manager, you will need to look at the properties tab in order to create an expression. In the Property Expressions Builder, select the ConnectionString Property from the drop down. In the expression side build something like this:
[codesyntax lang=”sql”]
"D:SomeDirectory"+User::TestSub+".txt"
[/codesyntax]
Now back to that Data Flow task I mentioned already. In your Data Flow Task, select the appropriate OLEDB Connection manager that you have created. Then select SQL Command in the data access mode dropdown menu. In the SQL Command text box, type an appropriate query to retrieve your data.
Example:
[codesyntax lang=”tsql”]
SELECT
InvDate
,CustFullName
,BillingAddress
,Amt
FROM [Orders]
Where Company=?[/codesyntax]
Then click on the Parameters button. From here, you will assign the appropriate variable to the Parameter that was created in the query. In our example, we should use the variable [ User::TestSub ].
That pretty much covers it. In a follow-up I will go through an actual setup that I have created. More screenshots, and slightly different setup than this baseline. Hope you enjoy.
