How to consolidate multiple similar SSIS packages that export data to txt file

I am in the middle of migrating jobs and packages from an old server and I need your help guys on how to consolidate packages.

I have these very old DTS packages that extracts data to a text file using some version of a function for each state. The way it is implemented now is there are 50 packages for all states and one package that executes the other packages. For example for the state of Virginia there is one text file required and the following query is used to extract the data and this would be exported to c:\va.txt


and for the state of Maryland I have which would be exported to c:\md.txt


and the same for the other states too.

What I have in mind now is to build a single package something like this for all states but it doesnt seem the best solution. I got a feeling that there should be a neat and easy way like using Foreach Loop Container instead of building a package with 50 dataflows but I cant wrap my head around it now and I need help.

more ▼

asked Aug 09 '10 at 01:23 PM in Default

DaniSQL gravatar image

4.9k 33 35 39

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Hey DaniSQL,

Here is a solution that should help you out, I did this in SQL 2005,

  1. Create 3 variables all with scope of the package;

a) Name: allStates, Data Type: Object, Value: System.Object

b) Name: state, Data Type: String, Value: VA (arbitrary value, the value is changed by the package)

c) Name: textFile, Data Type: String. For this variable, in the properties window change the EvaluateAsExpression value to True, click on the ellipses for the expression property and enter this as the expression "C:\" + @[User::state] +".txt" (NOTE there should be a double backslash in the expression, the editor is taking it out)
click on evaluate expression and it should equal C:\AB.txt.

  1. Add a data flow to the control flow, and add a OLEDB source and a recordset Destination to the dataflow.

  2. configure you OLEDB source to your connection and use SQL command
    (I created a simple table for this test, but you can change to your function)
    but you get the idea, it is getting a distinct list of the states in your table
    or you can create another table with a list of the states if you like, the choice is up to you
    (The editor is doing funny stuff with the code, there are underscores in the query)

    SELECT DISTINCT(SUBSTRING(fas.primary_chap_product,1,2))
    FROM dbo.FN_all_states

  3. Connect the OLEDB source to the recordset destination,
    open the recordset and change the VariableName to User::allStates.
    Select the Input Columns tab and select states, click OK.
    This creates an object which we will loop through to get the states.

  4. Go back to the control Flow and add a Foreach Loop Container
    click on Collection, and change the Enumerator to Foreach ADO Enumerator.
    In the Enumerator configuration window, select User::allStates as the Variable.
    Select Rows in all the tables (ADO.NET dataset only) from the Enumeration Mode window.
    Select the Variable Mappings option on the left.
    select User::state for the variable and the index is 0 (gets the first column from the recordset)
    click OK.

  5. Add a data flow container to the Foreach loop container.
    in the data flow add a OLEDB source and a Flat File Destination.
    Open the OLEDB source and select the connection, and choose SQL command.
    Enter your query, and change the 'VA' to a ?

    SELECT *
    FROM dbo.FN_all_states fas
    WHERE SUBSTRING(fas.primary_chap_product,1,2)=?

Click on Parameters, and select User::state for Parameter0

  1. Connect the OLEDB source to the Flat File Destination (FFD).
    Open the FFD and click New connection.
    Select your format option.
    Click on Browse and Enter an arbitrary name like c:\AB.txt.
    click on Columns to make sure the inputs are OK, then click on OK.
    Select the mappings and it should default to the mappings.
    click OK.

  2. Now we have to change the flat file connection manager to the variable textFile.
    click on the Flat File conenction manager and click on the Ellipses for the expressions
    Select ConnectionString from the Property window, and click on the ellipses.
    Drag the User::textFile down from the variables into the Expression area, and Click on Evaluate Expression.
    It should say c:\AB.txt.
    Click ok twice. and go back to the control flow.

  3. You are now ready to run the package, however it will be a good idea to right click on the For each loop container
    select edit Breakpoints, and select the Break at the beginning of every iteration,
    just so you can see each file created at a time, click on the green arrow to continue each iteration.

That should be it!

Let me know if you need any further info.
more ▼

answered Aug 09 '10 at 09:19 PM

Daniel Ross gravatar image

Daniel Ross
2.9k 10 12 13

BAH!, I hate this editor, In 1 c) the expression should have a double backslash in the expression
Aug 09 '10 at 09:24 PM Daniel Ross
@Daniel: I cant thank you enough for the elegant solution and it works like a charm. I have found some other well written blogs on related topics like this one ( http://bi-polar23.blogspot.com/2007/08/loading-multiple-excel-files-with-ssis.html ) and this one ( http://agilebi.com/cs/blogs/jwelch/archive/2007/06/03/multi-file-outputs-part-2.aspx ) and I think many people in similar situation would definitely be benefited. Thanks again.
Aug 10 '10 at 11:30 AM DaniSQL
@Daniel Ross, I tried to make this work for excel instead of flat file and Im getting error and couldnt make it work. what am I missing?
Feb 06 '12 at 09:15 PM DaniSQL
It will work what ever the destination is. What does the error message say? I've found, most of the time it is because the variables are not set to evaluateAsExpression in their property.
Feb 07 '12 at 03:32 AM Daniel Ross
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 09 '10 at 01:23 PM

Seen: 2032 times

Last Updated: Feb 07 '12 at 03:32 AM