SQL Scrapbook

Short and simple SQL Server posts

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.

Advertisements

Written by sqlscrapbook

July 14, 2010 at 8:04 pm

Posted in SSIS

Tagged with

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: