CODEWITHSUNDEEP
sql-serverssisetl
Nilesh
Nilesh

Reputation: 628

How to insert data into destination table using flat file source in SSIS

I have SSIS package, in which flow is -

  1. Get the data from flat file source and insert it into staging table.
  2. Use the staging table data for transformation using select and where clause and then insert filtered data in destination. table.

For 1st point, I have taken Data flow task to get the data from source and insert data into staging table. For 2nd point, I am confused, how should I do it. I am using Execute SQL task to run Select-Where query but the not getting how will I insert that query result into destination table. Which SSIS component should I use here. Or shall I change the entire flow for better performance. Kindly suggest. Thanks in advance.

Upvotes: 0

Views: 983

Answers (1)

ash
ash

Reputation: 3095

You are on the right track. Mostly, for a simple data import, I use this flow.

Let's say we have a destination table named FiscalYear.

The first thing I would do is create the staging table. If it exists, I drop it and recreate the table.

enter image description here

The next step is, using the data flow, to stage the file to the staging table.

enter image description here

For the last step, using Execute SQL task, and SQL-server Merge query, I insert or update the data. But to insert or update the data, you may have to have a unique identifier for each row that is in the file. This unique identifier is going to help you from inserting duplicates in case you run the package more than once.

This row unique identifier can be a single column or a combination of columns. In my case, I usually have a column named rowguid of type uniqueidentifier

enter image description here

Upvotes: 1

Related Questions