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

Question

I have SSIS package, in which flow is -

Get the data from flat file source and insert it into staging table.
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.

Answer 1

The simplest solution is to create a stored proc to transfer your stage table to the target table, and use execute SQL statement to execute the proc.

Answer 2

Hi @Dadu Gosavi ,

After loading data to the staging table, you may use execute-sql-task to load the select and where query.

Use query such as

INSERT INTO DestinatinTable (Columnvalue1, Columnvalue2)  
SELECT Columnvalue1, Columnvalue2  FROM StagingTable WHERE ...  

If I misunderstand your need, please correct me.

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.