March 4, 2009 at 2:17 am
i have a csv file with a number of records. what i want do is, load the data from csv file and put it into sel server database using SSIS. i done it. but i couldn't manage to check the destination object for existing record. as a result every time i run the Package it will put the no.of records from csv into sql server destination regardless of the same data exists in sql server destination or not.
Can anyoone please help me?
March 4, 2009 at 4:45 am
I suggest you create a brand new table in SQL Server just to hold records that are imported (a 'staging' table).
Then amend your SSIS process to be something like the following:
1) Truncate the staging table
2) Import CSV data to staging table
3) Run stored proc to INSERT/UPDATE from the staging table to your main SQL Server table.
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 4, 2009 at 5:17 am
Thanks phil, i will try that way
March 5, 2009 at 7:29 am
If you have a key value on your .csv file, you could use a lookup task in your package before your OLEDB destination component. The lookup task would check your target table for each key value on the input file. If the value was found you could drop the record.
If you want to update existing records, the Slowly Changing Dimension task can help with that. It can perform the lookup and send new rows to the table and perform an update on desired fields on existing rows.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply