December 15, 2011 at 8:40 am
I have a table in the DB which stores the different file formats for a given state. I need to write out a excel file with patients data for each file format for the given state. I am using SSIS to achieve this.
I have a Execute SQL Task ( listAllFileFormats) in a Conrtol Flow container and it calls a stored procedure to 'myproc1' NY that returns all the fileFormats for NY state. I have assigned variable iFileList to store the resultset.
I need to pass each fileFormat to a stored procedure to get the data from the database and write out to a file.
I have started this step by placing a ForEachLoop on the Control Flow container right after Execute SQL Task ( listAllFileFormats) and have placed a dataflow in it. I am stuck here.
Any help would be greatly appreciated. Thanks
December 15, 2011 at 8:55 am
Try this:
http://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspx
Essentially, you:
1. Pass the values you want to loop through into a full record set
2. Map the record set to a variable
3. Reference the variable in your for each loop
4. Use the variable as a parameter in your data flow task
5. Ensure your flat file connection is dynamic (evaluate it as an expression).
Give it a shot, and let me know of any specific queries/challenges you come across.
_____________________________________________________________________
Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain
December 15, 2011 at 9:01 am
Thank you Ted for the reply. In the link that you have provided it is showing how to loop through the files in the folder. But I need to create multiple files based on the resultset returned by the Execute SQL Task ( List All File Format).
December 15, 2011 at 9:17 am
TedT (12/15/2011)
Try this:http://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspx
Essentially, you:
1. Pass the values you want to loop through into a full record set
2. Map the record set to a variable
3. Reference the variable in your for each loop
4. Use the variable as a parameter in your data flow task
5. Ensure your flat file connection is dynamic (evaluate it as an expression).
Give it a shot, and let me know of any specific queries/challenges you come across.
Thank you! I got past #3. Now I am stuck at #4 - how do I use the variable from my first execute SQL Task as a parameter in the data flow task inside the ForEachLoop container.
December 15, 2011 at 10:13 am
Within your datasource (assuming you're using an OLE DB source), set the data access mode to "SQL Command". THen use the ? symbol to indicate where the variable will appear; e.g.:
Select *
from SchemaName.TableName
where Criteria = ?
Then click on the 'Parameters' button, and map the parameter to your variable.
_____________________________________________________________________
Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain
December 15, 2011 at 10:15 am
Guras (12/15/2011)
TedT (12/15/2011)
Try this:http://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspx
4. Use the variable as a parameter in your data flow task
Give it a shot, and let me know of any specific queries/challenges you come across.
Thank you! I got past #3. Now I am stuck at #4 - how do I use the variable from my first execute SQL Task as a parameter in the data flow task inside the ForEachLoop container.
First, make sure the variable is scoped at the package level. If it is scoped at the Task level, you need to delete and recreate it. That's how you get it available to your ForEachLoop container and then Data Flow Task.
In the FEL container, look at the collection enumerator. You can populate expressions for directory / file name using your variable in that section (NOT the external Expressions section).
December 15, 2011 at 10:17 am
Thank you all! Now I am stuck in the final part which is creating the file name based on the package level variable. I created the connection string of the Destination Flat File Connection manager as expression but when I assigne the variable to it, it will not let me. It errors out.
December 15, 2011 at 10:21 am
Post the expression, the value of the variable at that time (to your best guess) and the exact error message you are receiving.
December 15, 2011 at 10:31 am
"C:\\" + @[User::i_fileFormatList] + ".csv"
I receive the following error when I click the evaluate expression button
The dat type of variable User::i_fileFormatList is not supported as an expression
December 15, 2011 at 11:52 am
Guras (12/15/2011)
"C:\\" + @[User::i_fileFormatList] + ".csv"I receive the following error when I click the evaluate expression button
The dat type of variable User::i_fileFormatList is not supported as an expression
It sounds like you need to use an Execute T-SQL task in side the FEL container to populate a normal string variable before it hits the data flow. Then use that string variable in your expression.
EDIT: YOu know how to use Result sets, correct?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply