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.
Here is a solution that should help you out, I did this in SQL 2005,
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)
That should be it!
Let me know if you need any further info.