Using Temp table in SSIS package

When we are working on the complex SQL script (in Execute SQL task) sometimes we need to use the temp table. I mean this table is very useful when we need to store and manipulate an interim result set during ETL or other data processing operations.

Let’s see this demo. How we use this temp table.

In execute SQL task I am creating the temp table.

Create table #temp ( productid int identity(1,1), productname varchar(50) ) insert into #temp values ('Pen'),('book'),('copy'), ('pencil') select * from #temp drop table #temp

 

Now I am taking script task and using this sql script.

 

Click ok.

Ok still it showing error.

 

This error throw due to #temp object is not found.

To overcome with this issue we need to set Delay validation property.

Read: Delay validation properties in SSIS

By default this value is false

  

We need to set true

 

See error went

Now I am running this package.

Hope it is useful.