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. Source "Microsoft SQL Server Native client 10.0" Hresult:0x80040E14
Description: "Statements(s) could not be prepared".
Description:"Invalid object name "#ContactAddress".
SOLUTION:
1. Set the Delayvalidation property of the data flow task equal to true which delays the validation of the data flow task before execution.
2. Set the RetainSameConnection property of the OLEDB connection to true. This property if set to true use the same connection to all the task that uses this connection.
3. Create Execute task for creating the temp tables and call the data flow task after this.
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. Source "Microsoft SQL Server Native client 10.0" Hresult:0x80040E14
Description: "Statements(s) could not be prepared".
Description:"Invalid object name "#ContactAddress".
SOLUTION:
1. Set the Delayvalidation property of the data flow task equal to true which delays the validation of the data flow task before execution.
2. Set the RetainSameConnection property of the OLEDB connection to true. This property if set to true use the same connection to all the task that uses this connection.
3. Create Execute task for creating the temp tables and call the data flow task after this.
I must state that the blog post is really beneficial to anyone else who reads it because the information and knowledge it includes is vital. Continue to provide such useful knowledge through your posts and keep posting more on
ReplyDeleteBig Data Solutions
Business Analytics Services
Data Modernization Services
AI Service Provider