SSIS to import and update data if exist on the destination

Question

SSIS to import and update data if exist on the destination

Jim5130

Mr or Mrs. 500

Points: 589
More actions
May 25, 2015 at 9:11 pm

#298607

Hi,
I am working on SSIS wehre I need to work on a flat file as a source and needed to import it to database. If the destination table have the record already, I need to update it and if not exist, I just need to import the whole data. Any suggestions?

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

SQL.AP Hall of Fame Points: 3244 More actions · Answer 1

I would suggest, load your flat file into a staging table first, then use Merge to insert/update records in your destination table.

Otherwise, you can use SCD transformation, but performance wise it is slow if your source has large amount of data.

____________________________________________________________

AP

SQL.AP Hall of Fame Points: 3244 More actions · Answer 2

Here is one more option which you can use.

Using Merge in SSIS -

https://itssmee.wordpress.com/2010/10/03/ssis-insert-and-update-rows-in-a-table-based-on-the-contents-of-a-excel-file/[/url]

____________________________________________________________

AP

Emil B SSCertifiable Points: 5568 More actions · Answer 3

Anshul.P (5/26/2015)
Here is one more option which you can use.
Using Merge in SSIS -
https://itssmee.wordpress.com/2010/10/03/ssis-insert-and-update-rows-in-a-table-based-on-the-contents-of-a-excel-file/[/url]

Just be careful when using Ole DB Command as it processes a single row at a time, something like cursor.

Jim5130 Mr or Mrs. 500 Points: 589 More actions · Answer 4

Hi Anshul,

I'll try all your suggestion and let you know if it is ok. Thanks.

Jim5130 Mr or Mrs. 500 Points: 589 More actions · Answer 5

If I put it on stating table, and if the destination table is too large, will it takes time to insert/update? What is the best practice to do this?

Jim5130 Mr or Mrs. 500 Points: 589 More actions · Answer 6

HI Anshul,

I am following the link yo gave but it seems the Conditional split is not working with me. My reference column data is a varchar and does not allow NULL value as it is the transaction number. I am getting this error right now:

"The value violated the integrity constraints for the column"

https://itssmee.wordpress.com/2010/10/03/ssis-insert-and-update-rows-in-a-table-based-on-the-contents-of-a-excel-file/

SQL.AP Hall of Fame Points: 3244 More actions · Answer 7

Jim5130 (5/27/2015)
My reference column data is a varchar and does not allow NULL value as it is the transaction number. I am getting this error right now:
"The value violated the integrity constraints for the column"

Doesn't matter, the conditional split would work.

There must be some other issue which is causing this error. I hope you are using it right.

In the Conditional Split, One is - ISNULL

and the other is - !ISNULL

____________________________________________________________

AP

Jim5130 Mr or Mrs. 500 Points: 589 More actions · Answer 8

Hi Anshul,

I think the issue is with the Merge Join, where it seems it does not work properly. I use left outer join same as the instruction on the link but it seems it does not work. Please see my attached screeshot

SQL.AP Hall of Fame Points: 3244 More actions · Answer 9

Can you give screenshot of the conditional split as well?

and the screenshot of the destination.

It seems you are refering wrong columns in your destination.

____________________________________________________________

AP

Jim5130 Mr or Mrs. 500 Points: 589 More actions · Answer 10

Hi Anshul,

Thanks again and please see the attachment.

Added a new attachment included the remaining process

Jim5130 Mr or Mrs. 500 Points: 589 More actions · Answer 11

Got some clues after the merge join flow, the first column become NULL, am I missing something on the merge join?

SQL.AP Hall of Fame Points: 3244 More actions · Answer 12

In your Merge transformation, you're only selecting HSBC_TRADE_REF column from Sort Db.

1. You need to select HSBC_TRADE_REF column from Flat File as well.

2. Map the HSBC_TRADE_REF column coming from Flat File to your destination column.

Because there would be no existing record for this ID in DB that is why you are getting "NULL".

Hope It would resolve your issue.

@Edit: Re-phrased

____________________________________________________________

AP

Jim5130 Mr or Mrs. 500 Points: 589 More actions · Answer 13

Hi Anshul,

I tried it but it does not import the new row. My unique column is the HSBC_TRADE_REF. For sure I made a unique entry which does not exist on the destination table.

SQL.AP Hall of Fame Points: 3244 More actions · Answer 14

It should work. Did you update the Conditional Split as well?

In Conditional Split, you have to use Key column coming from Database, not the one from flat file.

____________________________________________________________

AP