Reputation: 628
I have SSIS package, in which flow is -
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
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.
The next step is, using the data flow, to stage the file to the staging table.
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
Upvotes: 1