locked
Creating and accessing temp table under For each Loop in SSIS RRS feed

  • Question

  • Hi All,

    I have created a global temp table in Execute SQL Task and joining that table in Data Flow Task to get some result set and insert into a table which is located on different server. I have set the RtainSameConnection as True and Delay Validation as True. But the requirement is that, I need to do same activity on multiple Databases using For Each Loop. But, due to RetainSameConnection is true so that I am getting data into Temp Table only from first database. I don't know Script. Is there any existing functionality to achive this task.

    Please help me.......

    Regards,

    Sanjay.

    Tuesday, May 7, 2013 12:39 PM

Answers

  • I guess the issue is since the table is created dynamically at runtime the underlying package indices are not aware of its metadata in advance, try setting the DelayValidation property to true. You can also use a normal table, too, just make sure to purge it before each run.

    To load data then from a table you can also use the Multicast Transformation. Example: http://www.c-sharpcorner.com/uploadfile/muralidharan.d/how-to-use-ssis-multicast-component/

    Using a cache transform is only different in that you would connect to it several times sequentially to grab the data for yet another destination, with the Multicast you can run parallel loads.


    Arthur My Blog

    • Marked as answer by Mike Yin Tuesday, May 14, 2013 6:38 AM
    Tuesday, May 7, 2013 3:03 PM
  • Hi Sanjay,

    As Arthur said, if you use physical temp table, created in db, then you dont need to set task properties such as delay validation and retain same conn etc. And if you are looping data flow in for each loop, simply put a truncate table statment as first task in loop (before data flow task).


    Thanks, hsbal

    • Marked as answer by Mike Yin Tuesday, May 14, 2013 6:38 AM
    Tuesday, May 7, 2013 8:10 PM

All replies

  • Hi Sanjay,

    If you made the temp table global then you do not need the Retain same connection set to true.

    If you want my advice using the Cache Transform Component you can avoid the temp tables all together.


    Arthur My Blog

    Tuesday, May 7, 2013 1:52 PM
  • Hi Arthur,

    Thanks for your reply, If I will not use the Retain Same Connection True then package will fail with error "Invalid object name '##TempTable'.". However, I am creating a Temp table in Execute Sql Task and using that temp table in Data Flow Task. Also, I have not worked with the Cache Transform and don't know how to use in this senario.

    Thanks,

    Tuesday, May 7, 2013 2:50 PM
  • I guess the issue is since the table is created dynamically at runtime the underlying package indices are not aware of its metadata in advance, try setting the DelayValidation property to true. You can also use a normal table, too, just make sure to purge it before each run.

    To load data then from a table you can also use the Multicast Transformation. Example: http://www.c-sharpcorner.com/uploadfile/muralidharan.d/how-to-use-ssis-multicast-component/

    Using a cache transform is only different in that you would connect to it several times sequentially to grab the data for yet another destination, with the Multicast you can run parallel loads.


    Arthur My Blog

    • Marked as answer by Mike Yin Tuesday, May 14, 2013 6:38 AM
    Tuesday, May 7, 2013 3:03 PM
  • Hi Sanjay,

    As Arthur said, if you use physical temp table, created in db, then you dont need to set task properties such as delay validation and retain same conn etc. And if you are looping data flow in for each loop, simply put a truncate table statment as first task in loop (before data flow task).


    Thanks, hsbal

    • Marked as answer by Mike Yin Tuesday, May 14, 2013 6:38 AM
    Tuesday, May 7, 2013 8:10 PM