July 15, 2013 at 5:56 am
Hi,
I have tables user and userprofiles. I get datafeed every night in csv file. I need to create ssis package to overwrite data everyday. I need to update the existing user data,add the new user data and disable the user if he is not in the datafeed.
In the past I used to import this data into a new table called userdatafeed and overwrite the data using a stored procedure. But now the requirement is to implement this SSIS package.
What data flow items I can use to achieve this?
July 15, 2013 at 2:15 pm
You could use a lookup component to find out if a record in the data feed is a new record or an updated record.
You can insert new records with an OLE DB Destination and update records with an OLE DB command (although this becomes slow for large numbers of rows).
However, it will be quite difficult to find out if rows exist in the destination table but not in the data feed without using some sort of staging area.
For this reason I think the easiest and also the most performant solution would be to store the data of the data feed into a temporary table and use a MERGE statement inside an Execute SQL Task to do your data manipulations.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply