March 26, 2007 at 9:08 am
Hi,
i need help on this
I have a SSIS package in SQl server 2005. This package picks up the Data from Excel File
and Insert into SQL Server Database. Its running properly,
But now I face a problem i.e. the records that insert into database may already existS ,
so Is there any way to avoid the existing Data into database by using SSIS package.
how can i handle this situation? can somebody explain in detail how to deal with it ,i am new to SSIS.
March 26, 2007 at 10:28 am
Its a lengthy process to describe in a post, however, to point you in the right direction, do some research on both a lookup and a merge join / conditional split.
There's a good article here which analyses the performance to be gained from both.
Hope it helps
Kindest Regards,
Frank Bazan
March 26, 2007 at 11:37 am
still not cleared .is there anybody who can guide me on this
March 26, 2007 at 4:32 pm
Basically, you use a lookup component to see if the record exists. If it does the record will come through the green/success output. If it doesn't it will come through the error output.
Connect your error output to your destination and you will then be inserting into your target table only those records that are new.
If you choose to use a merge join, you create two datasources. One is your excel spreadsheet, and the other is your target table. If you set the merge to be a left or a right (depending on how you've set out the datasources) outer join you will know if the record exists because the column that you select from the target will be a null where the record doesn't exist. Then you use the conditional split to separate the output from the target column into those records where it is null and those that aren't. The nulls are new records and should go to your destination table.
If you need help with the actual components, there is quite a lot on msdn or books online.
Kindest Regards,
Frank Bazan
March 27, 2007 at 4:45 am
The Slowly Changing Dimension task in SSIS will do exactly what you want to. You can set this up to do both updates and inserts. It will ignore records that have no changes.
David
March 27, 2007 at 7:14 am
Does the SCD work with an external connection manager, such as an Excel connection? When configuring the SCD, I only see an OLE DB connecftion option.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply