February 8, 2011 at 10:46 am
Hi ,
I have a source table A with (ID,Name,Address,Phone)
And destination table B with (ID,Name,Address,Phone)
I need to insert rows into Table B based on two conditions
1)Insert new rows if ID not in table B
2)Update rows based if ID is in table B
How do I do that in SSIS ?
I am using a look up transform. if it fails then insert into oledb dsetination else go to cspl.
I am using look up and CSPL .
IN CSPL I am using an expression like Name != Name_LKP || Address != Address_LKP || Phone != Phone_LKP
and redirecting arow to oledbcmd
with the following statement
UPDATE test2
SET
Name =?,
Address = ?,
Phone = ?
WHERE
ID = ?
But neither new rows are inserted nor updated.
Please help
February 9, 2011 at 2:51 am
What I usually do:
1. Read from the source
2. Perform a lookup on the ID and take the ID with you.
3. A conditional split that checks if the ID from the destination table is NULL or not. If NULL --> INSERT, if it has a value --> UPDATE.
4. Write the INSERTS immediately to the destination
5. Write the UPDATES to a staging table.
6. After the dataflow, perform a set based update with an Execute SQL Task.
This setup assumes you have no duplicates in the source.
Why the set based update (a regular SQL UPDATE that is) and not the OLE DB Command? Because it is much more performant.
If necessary, you can add an extra component in the dataflow to check if an update is a "real" update, in other words, if some attributes have actually changed.
You can also use a checksum for this.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 9, 2011 at 8:27 am
Thanks I got that working . But I have another issue . The source table has nvarchar datatypes and the destination has varchar. I tried converting the source to DT_STR using advanced editor. But its not working.
February 9, 2011 at 8:54 am
Use a data conversion transformation in the data flow.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply