October 6, 2011 at 3:16 am
Hi,
I have a requirement to create an SSIS package to commit data to a table based on the condition that if the data already exists for the primary key column, then it should be updated or if its not then it should be inserted.
The data is populated through a SQL code in the source component. However, in the destination component the data should only be copied over if it does not exist, if it exists then the data should be updated.
I have tried doing this through lookup component and also through the following code:
if exists(select * from table where primarykeycolumns = ???)
begin
Update table set columns = values
end
else
begin
Insert into table (columns) values (values)
end
Could someone pls give any suggestions ?
Thanks,
Paul
October 6, 2011 at 3:21 am
use a lookup component and send the non-matches to a OLE DB connection for the inserts,
for the updates either;
write the update statement in a SQL component with parameters and assign these parameters the correct columns in you data flow.
UPDATE tableA set ColA = ?, Colb =?
or even better, Create a staging table for the updates , insert the updated data into the staging table, and do the update in a set based manner using the UPDATE statement with an INNER JOIN. (if you were on 2008 you could use MERGE)
October 6, 2011 at 4:03 am
Raunak Jhawar (10/6/2011)
If SQL 2008 and upwards...please follow the link here
Please describe how to use that within an SSIS package.
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
October 6, 2011 at 4:31 am
Raunak Jhawar (10/6/2011)
Phil Parkin (10/6/2011)
Raunak Jhawar (10/6/2011)
If SQL 2008 and upwards...please follow the link herePlease describe how to use that within an SSIS package.
Execute SQL Task/Script Task
This is the 2005 forum so Merge is not available, i think it is just confusing things offering solutions that are not going to work..
October 6, 2011 at 4:33 am
mistake admitted.
Raunak J
October 6, 2011 at 4:41 am
steveb. (10/6/2011)
Raunak Jhawar (10/6/2011)
Phil Parkin (10/6/2011)
Raunak Jhawar (10/6/2011)
If SQL 2008 and upwards...please follow the link herePlease describe how to use that within an SSIS package.
Execute SQL Task/Script Task
This is the 2005 forum so Merge is not available, i think it is just confusing things offering solutions that are not going to work..
As there is no 2008 SSIS forum, I think you're being a bit harsh. All SSIS questions seem to be posted here, regardless of platform.
My point was that using MERGE is a T-SQL solution, not really an SSIS solution.
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
October 6, 2011 at 4:41 am
Don't be so hard on yourself, especially as steveb also mentioned it in his post! In any case, I've seen plenty of people posting in the wrong forum, so it's definitely worth a mention. Furthermore, this is actually a SSIS 2005 forum, and in SSIS 2005 you can have connections to 2008 servers (as far as I know).
John
October 6, 2011 at 4:46 am
Phil Parkin (10/6/2011)
steveb. (10/6/2011)
Raunak Jhawar (10/6/2011)
Phil Parkin (10/6/2011)
Raunak Jhawar (10/6/2011)
If SQL 2008 and upwards...please follow the link herePlease describe how to use that within an SSIS package.
Execute SQL Task/Script Task
This is the 2005 forum so Merge is not available, i think it is just confusing things offering solutions that are not going to work..
As there is no 2008 SSIS forum, I think you're being a bit harsh. All SSIS questions seem to be posted here, regardless of platform.
My point was that using MERGE is a T-SQL solution, not really an SSIS solution.
Yeah Sorry Raunak my mistake I thought he was on 2005.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply