SSIS: Dealing with temp tables
Temporary tables are the temp space available in the database to store data for further modifications. It is extensively used in the TSQL programming which is very convenient for the programmers. When it comes to SSIS we need to deal it with differently as the temporary tables life time is for only one session. SSIS throws error if the dataflow task used TSQL with temporary table. For e.g create one data flow task with below query which uses temporary table #ContactAddress to store data from person.Contact table of Adventureworks. #ContactAddress is the source table for ContactAddress in which data comes from the SSIS package. give the below query in one SSIS task and load the data into the table. create table #ContactAddress ( contactId int, firstname nvarchar(100), ) insert into #ContactAddress select ContactID,FirstName from Person.Contact ERROR: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occured. Error code: 0x80040E14. An OLEDB record is available