SQL Update based on Conditional Split

Question

Post reply

SQL Update based on Conditional Split

bkirk

SSCrazy

Points: 2824
More actions
March 9, 2009 at 4:10 pm

#202128

My current SSIS package filters a text CSV source file through a Conditional Split for both Insert and Updates.
At present i have been able to send the 2 split results to separate text file outputs to make sure i have the correct data branches.
Here is what i need to do:
1) INSERT new records to a SQL Destination table (this i have easily completed successfully)
2) UPDATE existing matched records to the SQL table based on ProductID.
How do i run and Update SQL command to this table without first importing to a TEMP table in SQL?
Can i use some sort of simple SQL UPDATE command passing through the row data from the Conditional Split output for updates?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jay1234 SSCommitted Points: 1624 More actions · Answer 1

Use OLE DB Command Task to issue updates against your table in Data Flow task.

For more info look into

http://technet.microsoft.com/en-us/library/ms141138.aspx

http://www.sqlis.com/post/OLE-DB-Command-Transformation.aspx

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Another option which you may or may not have, is to put all the data in a staging table on the same SQL Server instance, and then use a Left Join to do the inserts and inner join to do updates. Something like:

UPDATE real_table

SET col1 = S.col1,

col2 = S.col2,

etc...

FROM

real_table R JOIN

staging_table S ON

R.key_columns = S.key_columns

INSERT INTO real_table

SELECT

column_list

FROM

staging_table S LEFT JOIN

real_table R ON

S.key_columns = R.key_columns

WHERE

R.key_column IS NULL

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jonathan Mallia SSCertifiable Points: 5192 More actions · Answer 3

Try looking into slowly changing dimensions (SCD) within SSIS itself... That should help you

bkirk SSCrazy Points: 2824 More actions · Answer 4

I've found a solution which works perfectly without using a TEMP or Staging table. It is a bit complex but works very well and is very adaptable for future changes.

Here is the high level summary of the Data Flow. The process is broken down into 4 main stages:

1) 2 data sources at the beggining

a) Source 1 - CSV Flat File containing all Product data

b) Source 2 - OLEDB Select SQL statement on Products Table

2) Merge Join (Left Join) on the data. This will create 'one-to-many' resultset of data where the ProductID's from both sources are identified. (Note: The number of rows returned will equal the number of rows in the Flat File source.)

3) Conditional Split with 2 output streams

You can identify the insert/update records from the above resultset as follows:

a) Insert - 'NULL' ProductID from the OLEDB Source (plus any other criteria for an insert)

b) Update - matching ProductID's from both the Flat File & OLEDB sources (plus any other criteria for an update record)

4) OLEDB Commands for Insert and Update streams

a) Insert conditional split stream - INSERT SQL command using Parameter fields mapped from the passed in data rows.

b) Update conditional split stream - UPDATE SQL command using Parameter fields mapped from the passed in data rows.

(eg. UPDATE Products SET ProductName = ? WHERE ProductID = ?)

This is just an alternative way i have found to process Insert/Update SQL statements using raw data files without the need for any TEMP SQL tables or coding.

Hope this helps.:)

vijaya249 Old Hand Points: 361 More actions · Answer 5

Hi to all:),

I m totally new to this ssis n wanted to know how u pple solve

insert/update records in destination

means wht kind of flow u created

if possible give me sme example of code

cz as i did i got new records ie inserted but it gets duplicated each time n i used staging tables for that purpose

cud anyone help me in this issue

i really unable to solve this kind of thing

Thanks,

Vijaya

bambang SSChasing Mays Points: 642 More actions · Answer 6

Dear Vijaya,

You should try using Lookup component in data flow task before u do any

insert/update.

Lookup component has 2 path, for error (red) and valid (green).

If it's a new record, redirect it by using error path and insert into destination table.

If it's an old record, use valid path and update data in destination table.

I usually use this method to insert or update in my destination table 🙂

vijaya249 Old Hand Points: 361 More actions · Answer 7

Hi bambang

thnx for reply

I was thinking that d problem may be solved

but the my problem is like

there is one feed file (we can call it input file)

n nw i wanted to load that data into two different destination they may be flat file or oledb tables depends on some criteria

may be afetr sme days the input file having same records n sme new records n sme updated records

in that situation wht i have to do i dnt knw

cz i crete a data flow for that where i merge all data n sort/aggregate on them

it results in avoiding duplicate records but after first execution when

i go for second time run with same input file it going to be duplicated

so in this scenario

can anybody help me

Thanks,

Vijaya