How to load data to Destination SQL table using Stored Procedure in SSIS

Question

How to load data to Destination SQL table using Stored Procedure in SSIS

Senthilkumar-456461

SSC-Addicted

Points: 491
More actions
June 27, 2007 at 3:55 am

#372497

Hi all,
I need to develop one SSIS Package which will run every day at 12 AM.
This package should extract data from Excel Sheet and Load into Sql Server table.
My Req: I want my package should do if the data already there in the table should update else insert.
Tell me what are the possible ways to do this.
Thanks
PT Senthilkumar

Viewing 5 posts - 1 through 4 (of 4 total)

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

bledu SSChampion Points: 12335 More actions · Answer 1

you could develop a .net assembly that reads the excel file, link it to a table valued function which you would use in you script. i am thinking you can use a cursor to traverse your records

Everything you can imagine is real.

Stefan K SSCommitted Points: 1786 More actions · Answer 2

Hi,

the way I usually do this is to do a lookup for the PK of the target table saving the value of an ID (autoincrement value). Then I do a conditional split. I the ID is NULL the value does not exist in the target table, yet. The standard output is in this case the existing values which get an own green connector. I insert the new data by an OLEDB target and do an update with an oledb command using parameters. The last part is not very nice, because you have to use ? parameters, but it works and is fast.

Cursors are usually slow.

Best regards,

Stefan

Kindest Regards,

SK

Anil-289324 Grasshopper Points: 15 More actions · Answer 3

select a sql script tool, create the sql connection and write a sql script to check if row count> 0 then go for update command by selecting the update db task tool else go for insert command; it will be the script of 5-7 line.

---Anil

Catherine Eibner Ten Centuries Points: 1364 More actions · Answer 4

looks like my last post didnt save so am adding again - apologies if it comes up twice!!

You may need to consider that you would not want to update every row if the data has not changed. This is especially important if you are replicating your data and dont want to replicate a whole stack of deletes & updates everyday when the data has not changed. I have found a checksum very useful for this - but also used a query that returns a row & column level comparison on key fields where there is a difference.

I have found the folloiwng articles useful:

http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/

http://sqljunkies.com/WebLog/ashvinis/archive/2005/06/15/15829.aspx

You will also find that popping "SSIS Insert & Update" into Google returns a good range of results with more info.

Hope that helps,

Catherine

Kindest Regards,

Catherine Eibner
cybner.com.au