Global Temp Table Issue in SSIS 2005

• SSIS_2008

  Mr or Mrs. 500

  Points: 534

  More actions

  March 23, 2009 at 1:23 pm

  #130032

  I have an IS package which need to execute an 'Execute SQL task' in control flow, I used a global temp table to achieve the necessary functionality in this task. In the connection manager I changed the default properties of 'Retain Same Connection' to True. I have to use a OLE DB Source data flow task to get the columns from the 'Execute SQL task' .If I execute the global temp table query in SQL SERVER management studio, its working fine. After that if I execute the IS package its run successfully. The issue is whenever I end the session in SSMS, and then try to execute the IS package ,its giving me the error that package cannot find the global temp table which I named as '##TEMP_DATA'.

  It’s a complicated SQL query, that's why I have to use the temp table in the 'Execute SQL task' in control flow. Can anyone give me any solution for this issue, please?

  Any help will be appreciated.

  Thanks.

• SSIS_2008

  Mr or Mrs. 500

  Points: 534

  More actions

  March 24, 2009 at 3:17 pm

  #965648

  I solved this isuue by using RetainSameConnection =TRUE for the connection manager and setting DelayValidation =TRUE in the dataflow.

  Thanks,

  Sabina