locked
SSIS (2008) recordset handling question.... RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    From SSIS I need to call a stored proc which returns a record set (filenames of files in a directory + extra info), and then, based on the "extra" info, possibly delete some of the source files in the original directory.

    The stored proc returns a record set. What is the way to store the result so i can iterate over it? (I have seen "ADO.NET Destinations" and "Recordset Destination"  but I don't know how to use either or which one is more appropriate.) Suggestions appreciated.

    TIA,

    Barkingdog

    Monday, April 30, 2012 10:46 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Take a look at this.  http://www.select-sql.com/mssql/loop-through-ado-recordset-in-ssis.html.  It tells how to load a recordset variable and then read the columns from that recordset to do something.  You could use a File System Task to do what you want with the file.

    By the way, you can put an empty Sequence Container in your For each loop.  Then you would connect that Sequence container to different File System Tasks so that you can do different things based on the other values.  http://www.select-sql.com/mssql/loop-through-ado-recordset-in-ssis.html

    Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

    • Marked as answer by edm2 Tuesday, May 1, 2012 1:46 AM
    Monday, April 30, 2012 11:18 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi There

    You can use SQL task to get the result from the database through stored procedure and put the results inside object type variable. I have shown the screenshots for that.

    After putting results inside the object type variable use for each loop container and do configuration like shown in the figure.

    You can put the result set column in SSIS variable using indexes as shown in the figure. After that you will be able to do whatever you like by looping through

    Many thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    • Proposed as answer by Syed Qazafi Anjum Monday, April 30, 2012 11:29 PM
    • Marked as answer by edm2 Tuesday, May 1, 2012 1:47 AM
    Monday, April 30, 2012 11:29 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Take a look at this.  http://www.select-sql.com/mssql/loop-through-ado-recordset-in-ssis.html.  It tells how to load a recordset variable and then read the columns from that recordset to do something.  You could use a File System Task to do what you want with the file.

    By the way, you can put an empty Sequence Container in your For each loop.  Then you would connect that Sequence container to different File System Tasks so that you can do different things based on the other values.  http://www.select-sql.com/mssql/loop-through-ado-recordset-in-ssis.html

    Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

    • Marked as answer by edm2 Tuesday, May 1, 2012 1:46 AM
    Monday, April 30, 2012 11:18 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    A recordset destination will allow you to do what you need to do.  Look here for an example which includes iterating through the recordset.

    http://msdn.microsoft.com/en-us/library/cc879316(v=sql.105).aspx

    Chuck

    Monday, April 30, 2012 11:20 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi There

    You can use SQL task to get the result from the database through stored procedure and put the results inside object type variable. I have shown the screenshots for that.

    After putting results inside the object type variable use for each loop container and do configuration like shown in the figure.

    You can put the result set column in SSIS variable using indexes as shown in the figure. After that you will be able to do whatever you like by looping through

    Many thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    • Proposed as answer by Syed Qazafi Anjum Monday, April 30, 2012 11:29 PM
    • Marked as answer by edm2 Tuesday, May 1, 2012 1:47 AM
    Monday, April 30, 2012 11:29 PM