none
Result set of a execute sql task as parameter of another execute sql task ?

    Question

  • Hi, Im having some problems over here. I want to use the result set (Full set stored as an Object) of an Execute Sql Task as input parameter of another Execute Sql Parameter. I want to do some insertions on the second task with the result table of the first one.

    Is this even possible ? Or do I have to use a foreach loop or a script task ?

     

    Thanks !

    Friday, April 15, 2011 12:03 AM

Answers

  • Are your source and target different between these two Execute SQL Tasks?

    If not you may get away with a temp table and it will be a SQL only approach.

    If not you can dump the data to a file and load into the other database table to further consume.

    In both cases you can get away from the ForEach Loop (which can be a slower approach to process huge amount of data), besides, by going the SQL only way you avoid consuming too much memory and the process is going to be faster because there will be no dataset object involved.


    Arthur My Blog
    By: TwitterButtons.com
    • Marked as answer by alonvalle17 Friday, April 15, 2011 3:00 PM
    Friday, April 15, 2011 2:27 PM
    Moderator

All replies

  • Use for each loop.
    Nitesh Rai- Please mark the post as answered if it answers your question
    Friday, April 15, 2011 12:47 AM
  • in addition to what Nitesh said,

    set enumerator in foreach loop as ADO Enumerator

    and in the variable mappings tab of foreach loop editor, set another variable with index appropriate ( the index in number of column starting from 0, based on what columns you inserted into the object variable in previous execute sql task )


    http://www.rad.pasfu.com
    Friday, April 15, 2011 5:58 AM
    Moderator
  • When you want to perform inserts from the result set of one table to another table, what is that cannot be done by the data flow task?
    Request to please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.
    Friday, April 15, 2011 10:38 AM
  • thanks for the replies, the thing is that I dont know if passing a result set from an "Execute SQL Task" stored as an object to another "Execute SQL Task" to do some inserts, because I was looking to an alternative to the foreach loop.

     

    But if there is no way to go around that, than foreach loop will be.

     

    Thanks

    Friday, April 15, 2011 2:19 PM
  • Are your source and target different between these two Execute SQL Tasks?

    If not you may get away with a temp table and it will be a SQL only approach.

    If not you can dump the data to a file and load into the other database table to further consume.

    In both cases you can get away from the ForEach Loop (which can be a slower approach to process huge amount of data), besides, by going the SQL only way you avoid consuming too much memory and the process is going to be faster because there will be no dataset object involved.


    Arthur My Blog
    By: TwitterButtons.com
    • Marked as answer by alonvalle17 Friday, April 15, 2011 3:00 PM
    Friday, April 15, 2011 2:27 PM
    Moderator
  • its actually a really really small table, so I guess the foreach is really the way to go. thanks everyone for the replies, you've helped me a lot.

     

    Thanks !!!

    Friday, April 15, 2011 3:00 PM