Insert,Update,Delete in SSIS - newbit in SSIS

Question

Insert,Update,Delete in SSIS - newbit in SSIS

Patrick.I

Hall of Fame

Points: 3105
More actions
August 3, 2009 at 8:40 pm

#209040

I'm using OLEDB Desitnation to insert some data from a csv file.
But i'm receiving files and i need to check if the record exist update the record and if it doesn't insert new records
How do get this done is SSIS.
Any resources how i can use Lookup Transformations for this or which other ways can i achieve this?
Thanks in Advance

Viewing 10 posts - 1 through 9 (of 9 total)

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

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

Patrick.I (8/3/2009)
I'm using OLEDB Desitnation to insert some data from a csv file.
But i'm receiving files and i need to check if the record exist update the record and if it doesn't insert new records
How do get this done is SSIS.
Any resources how i can use Lookup Transformations for this or which other ways can i achieve this?
Thanks in Advance

Check these posts:

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

http://www.sqlis.com/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspx

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Patrick.I Hall of Fame Points: 3105 More actions · Answer 2

Thanks and i'm following this article

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

I have added a

Lookup -- i use this to redirect rows if there is not match

and a

Conditional Split component.-- using for example log_number != LookUplog_number

But my question is this.

I have some data in the table already and i just want to load new data from a CSV file.

But i want to update or insert.

So if there is a row already existing do nothing if it exists and there is a change UPDATE if there are new rows from the csv file

then INSERT new records..

What component do i need to UPDATE and INSERT this records. Is it Oledb Command?

And if yes hwat does the sql command look like?

I reckon they would need to contain some parameters or variables etc...

I just need some info on this.

THnaks a lot

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

Check the comments below after the post. One of the approaches mentioned is this:

"Use the OLE DB Command to do the updates, an OLE DB Destination to do the inserts."

--

It is too case-specific to be possible to provide general answer to your question about what SQL statement you should use.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

Hi Patrick

not knowing the volumes of data you have, the following method may not be appropriate, but this is one method I use to import up to 30K records into my main table of 12M records.

The vast majority of my import is either new or amended records.

I am not interested in what data has changed.

The import file already contains a PK.

I have a main table and a staging table.

The staging table is a duplicate of main table

import file into staging table

delete all records from main table that match staging table using the PK

insert all records from staging table into main table

truncate staging table

...repeat

regards Graham

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Patrick.I Hall of Fame Points: 3105 More actions · Answer 5

Thanks.

Check the comments below after the post. One of the approaches mentioned is this:

"Use the OLE DB Command to do the updates, an OLE DB Destination to do the inserts."

Yes i know i can do an update e.g

update table set columna = @parama where columnb= @paramb

But how to do this in SSIS but give me a sample update statement.Can i use something like this like in sql how do i represent a param in SSIS @parama?

The same goes for INSERT.

Patrick.I Hall of Fame Points: 3105 More actions · Answer 6

Gah,

I was thinking about this method already if i decide not to use SSIS. 🙁

By the way my records could be over half a million records.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

Patrick.I (8/5/2009)
Gah,
I was thinking about this method already if i decide not to use SSIS. 🙁
By the way my records could be over half a million records.

Hi Patrick...I do use this method in SSIS for multiple files in one process, using many "Execute SQL task"

Basically I write the complete TSQL for import/delete/insert for each file and paste it into the "Execute SQL Task"...then create another Execute SQL task for the next file....continue until finish all files, then schedule as neccessary

Out of interest...how many of your 500K csv records that you import are not new or amended?

regards Graham

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Andy Leonard SSChampion Points: 10144 More actions · Answer 8

You may want to check out my article on Incremental Loads. http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/62063/

:{> Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 9

Patrick...stumbled across this...may be worth a look at the Word docs

http://www.sqlservercentral.com/Forums/FindPost765551.aspx

regards gah

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day