locked
problem in ssis foreach loop container using recordset RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi, I have a result set in a variable from execute sql task. Now I am using for each loop to loop through each row in the resultset variable. I have to get some more data from other tables based on the rows from resultset. so I am using data flow task. But now here I am confused that how to use recordset here in dataflow. And finally what i need is the flat file with the recordset data and the data fetched from another table using the rows in recordset. All in one flat file. Kindly tell me how to do.
    Wednesday, May 4, 2011 11:26 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    The typical way this is accomplished is very similar to what you've started.

    Use an Execute SQL Task to retrieve your "parent" rowset, and use that in a Foreach Loop Container.  The Foreach Loop Container uses the ADO Recordset enumerator, which can use the Variable Mappings tab to extract columns from each row into SSIS variables.

    Inside the loop, use a Script Task to write those variables out to your flat file as the parent "header" line(s).

    Follow that with a Data Flow Task that uses an OLE DB Source to extract data based on those same SSIS variables from the Foreach Loop.  You do this by constructing a SQL statement inside another SSIS variable through expressions.  The expression would use the variables from the foreach loop.

    Todd McDermid's Blog Talk to me now on
    • Proposed as answer by Jerry Nee Thursday, May 5, 2011 7:31 AM
    • Marked as answer by Jerry Nee Sunday, May 15, 2011 4:17 AM
    Wednesday, May 4, 2011 4:22 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    In order to do the same thing without Scripts, you can set up a couple Data Flows to write header and detail rows to flat file destinations independently inside the loop - one order at a time.  You can then use two Execute Process Tasks inside the loop to take those two files and concatenate them to the ultimate output file using "Copy Header.txt >> Output.txt" and "Copy Detail.txt >> Output.txt" type commands... make sense?

    Todd McDermid's Blog Talk to me now on
    • Marked as answer by Jerry Nee Sunday, May 15, 2011 4:16 AM
    Thursday, May 5, 2011 4:23 PM

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote

    Can you explain it a little more clearer?

    The data in that recordset shoul be a filter for data from othe tables?

    If so, than store the fields from the recordset into variable an use these for filtering.

    Wednesday, May 4, 2011 11:31 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Thanks for quick reply. Let say we have customer detail in one table and there order detail in another table. I have to show the data in flat file like in first row customer detail and from second rows his order detail. There can be multiple order detail for one customer. So let say for first customer there are 10 order detail. So after displaying all the order detail I have to print the second customer and his order detail. Like this I have to make a flat file. Each order detail is in different row.

    For this I am fetching all the customer detail from customer table using execute sql task and storing the result in a result set variable. Now I have to use this resultset to get the order detail and prepare the flat file as described in above paragraph.

    Wednesday, May 4, 2011 11:44 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I suppose, this will not work.

    The only way, I imagine:

    Store all Customer details in a Variables
    in DF fetch the related rows for order details per customer using the variable with customerid for filter
    add all variables to the recordset
    create a Script component for Destination and write the flattable in the component

    Wednesday, May 4, 2011 1:11 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Thanks for quick reply. Let say we have customer detail in one table and there order detail in another table. I have to show the data in flat file like in first row customer detail and from second rows his order detail. There can be multiple order detail for one customer. So let say for first customer there are 10 order detail. So after displaying all the order detail I have to print the second customer and his order detail. Like this I have to make a flat file. Each order detail is in different row.

    For this I am fetching all the customer detail from customer table using execute sql task and storing the result in a result set variable. Now I have to use this resultset to get the order detail and prepare the flat file as described in above paragraph.

    You only need to bring the final results, after you do your joins in the query,   don't bring the customers and join in SSIS.

    Where  the orders' data located? also in a table like the customer's details?  if YES< so you just need to join them in one query, then bring that qery to SSIS and dump it to a flat file destination.

    MCITP - BI 2008 http://asqlb.blogspot.com/
    • Proposed as answer by Jason Yousef Wednesday, May 4, 2011 3:43 PM
    • Unproposed as answer by Todd McDermid Wednesday, May 4, 2011 4:14 PM
    Wednesday, May 4, 2011 1:14 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Or simply write Stored proc which accept customer number and return the orders belongs to that custermer and them dump them onto flat file destination
    http://uk.linkedin.com/in/ramjaddu
    Wednesday, May 4, 2011 1:18 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    The typical way this is accomplished is very similar to what you've started.

    Use an Execute SQL Task to retrieve your "parent" rowset, and use that in a Foreach Loop Container.  The Foreach Loop Container uses the ADO Recordset enumerator, which can use the Variable Mappings tab to extract columns from each row into SSIS variables.

    Inside the loop, use a Script Task to write those variables out to your flat file as the parent "header" line(s).

    Follow that with a Data Flow Task that uses an OLE DB Source to extract data based on those same SSIS variables from the Foreach Loop.  You do this by constructing a SQL statement inside another SSIS variable through expressions.  The expression would use the variables from the foreach loop.

    Todd McDermid's Blog Talk to me now on
    • Proposed as answer by Jerry Nee Thursday, May 5, 2011 7:31 AM
    • Marked as answer by Jerry Nee Sunday, May 15, 2011 4:17 AM
    Wednesday, May 4, 2011 4:22 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Todd, Thanks for your helpful suggestion.

    Also thanks to all who has replied or seen this problem.

    But client has policy not to write script task in any ssis package. ( Strange...:-)). So I am planing to use execute sql task in for each loop. Get a single row from resultset. Write it in some temptable. Save ID in a variable.(we have a ID field in resultset). Now go to data flow task. Extract data based on the value in variable and write this data in temptable. In each loop the variable value will change.

    I am sorry if I am wrong here but I have not done this type of thing earlier in any ssis package. if possible please guide me how to proceed as even I am not sure how to proceed with my idea I have written above.

    Thursday, May 5, 2011 4:39 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    In order to do the same thing without Scripts, you can set up a couple Data Flows to write header and detail rows to flat file destinations independently inside the loop - one order at a time.  You can then use two Execute Process Tasks inside the loop to take those two files and concatenate them to the ultimate output file using "Copy Header.txt >> Output.txt" and "Copy Detail.txt >> Output.txt" type commands... make sense?

    Todd McDermid's Blog Talk to me now on
    • Marked as answer by Jerry Nee Sunday, May 15, 2011 4:16 AM
    Thursday, May 5, 2011 4:23 PM