Hi, I have a execute sql task which will create the temp table and below to this task I have a DataFlowTask which will insert data into the Temporary table. Here I am facing one issue,even though the temporary table is getting created I am getting one error .Invalid Object name '##TempTable". WHy this is Happening. I have set the conncetion manager RetainSameConnection property to true.I have set the delay validation property of dataflow to True. Still the error is coming. Please help me.Thanks, A2H

first and foremost: Why you want to use Temp table? there are ways in SSIS which you can avoid temp tables. let us know what you want to do exactly and we help you in right wayhttp://www.rad.pasfu.com

Hi Reza, I have to delete some rows from Table A,Table B,Table C based on the Id field for Table D. Id from table D is fetched based on sql query with some where conditions.Result Set of this Sql Query I am putting into a TempTable. Delete command should have proper Transaction such as rollback and commit. what I am doing now is In an execute sql task I have sql query like this. Delete from Table A where id in(Select ID from TempTable) Delete from Table B where id in(Select ID from TempTable) Please tell me is there any other way we can acheive this.Thanks, A2H

you can use a data flow task, set your sql query in an oledb source then add an OLEDB COMMAND and set connection there, write the delete command for table A here, for example: delete from tableA where id=? which ? is parameter marker, then in parameter mappings tab, set input column with this parameter. let me know if you have problem in implementation.http://www.rad.pasfu.com

I have a execute sql task which will create the temp table and below to this task I have a DataFlowTask which will insert data into the Temporary table. Here I am facing one issue,even though the temporary table is getting created I am getting one error .Invalid Object name '##TempTable". Inside datflow task and inside oledb source use data access mode as "Table name or view name variable". Create a variable (package scoped) and give the variable's name as the temp table name. Now select the variable from the drop down box. It shoould work.Nitesh Rai- Please mark the post as answered if it answers your question

then add an OLEDB COMMAND and set connection there, write the delete command for table A here, for example: delete from tableA where id=? which ? is parameter marker, then in parameter mappings tab, set input column with this parameter. OLEDB Command will delete the records row by row and execute sql task will do a set based delete. So, if the number of records to be deleted are huge, Temp/staging table approach is good. Nitesh Rai- Please mark the post as answered if it answers your question

Hi, As you have already set RetainSameConnection property to true make sure you are using same connection manager for all the tasks which are using the temp table.

Hi All, I need temporary tables to do some Business validations. Can you please explain me to get rid of the error. Thanks, A2H

In Execute SQL task use a connection manager (CONN) and write the script to drop the gloabl temp table (if it exists) and create the global temp table like this: IF OBJECT_ID('##TemptableName', 'U') IS NOT NULL DROP TABLE ##TemptableName CREATE TABLE ##TemptableName ( Col1 int ) Open management studio and execute the above script under the scope of the database to which CONN is pointing. Now add a data flow task and configure the destination using temp table and CONN connection manager. Set the data flow task's delay validation as True and set the Retain Same connection property to true (which you already are aware of). Create a variable to store the temp table name and use this variables inside the OLEDB Destination (data access mode as table name or view name variable). Once the data flow taks is configured, drop the temp table from the management studio.Nitesh Rai- Please mark the post as answered if it answers your question

Hi Nitesh, I have did the same thing as explained above.Still I am getting same error "Invalid Object TempTable Name" Thanks, A2H

Which component is failing? Is the temp table global or local? Nitesh Rai- Please mark the post as answered if it answers your question

Global Temp Table: ##temptable name For a confirmation this is the way i did.I just ran the script which u give to me after making changes to reflect my temptable name. Then I created a variable with scope of package and give the name of temporary table.after that I mapped the columns.and save in the ssis package. then i deleted the temporary table thru sql server interface. Please help me.Thanks, A2H