SQL Scrapbook

Short and simple SQL Server posts

Posts Tagged ‘Temp Table in SSIS

Load data into a temp table using SSIS

leave a comment »

Very recently i had to load a flat file into a temp table. I know this is not a good approach but i had no other option (atleast at that time). It was then i found out that this is not very straight forward. Below listed are the steps i followed.
Again in short, this is what i want to accomplish. Using a data flow task (DFT) in the SSIS package, i want to load data from a file to a temp table. As you can see the source of the DFT is a flat file and destination is a temporary table.
1) Create a OLEDB connect manager pointing to the SQL Server database, Create a flat file connection manager pointing to the source flat file.

2) For the OLEDB connect manager, in the properties pane set the RetainSameConnection property to true. This is an important part when working with temp tables in SSIS.
3) Configure an execute sql task to create a global temp table. This is my sample create statement

CREATE TABLE ##LoadTempTable(
[ProductKey] INT NULL,

[ProductName] VARCHAR(50) NULL,

[ProductType] VARCHAR(50) NULL

4) Execute the create statement you used in the execute sql task (step 2) in the management studio to create the same table there. This way we will have the table available for design and column mappings.
5) Drag a DFT and set the DelayValidation property to true. Configure the source connection. Drag and edit the OLEDB destination connection. Specify the connection manager name.
6) In the data access mode select table or view. In the Name of the table or view field click new. You will see a create statement like CREATE TABLE [OLE DB Destination]…
7) Change the name of the table to your global temp table name.
8) Edit the mappings and you will be able to run the package.

Written by sqlscrapbook

July 14, 2010 at 8:04 pm

Posted in SSIS

Tagged with