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

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.