How can I update in SSIS?

[kevin197]

I've wrote the package and have one XML source that goes to 4 Data Conversions and the each data conversion writes to a different sql table.

Everything work great apart from I don't want it to insert the data into sql I want it to update.

Can I do a update instead and if so how?

Thanks.

 

[mrdenny]

The easiest way is to drop the table to a staging table, then update the production table from the staging table.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[kevin197]

Thanks, I've put the data into a staging table but now I'm having problems with the upsert

A bit of back ground...

I've got a table called Product on a database and I've got the staging table on the same database called tempProduct

In Control Flow I've done a Execute SQL Task for truncating the tempProduct table.
Then a Dataflow task which opens a xml file, does a data conversion and puts the new data into the tempProduct table.

So far so good, Now I need to insert/update the new data in the table tempProduct into the old data in table Product.

I've got a Execute SQL Task coming out of the Dataflow task in Control flow with the following but it's not working.

IF (EXSITS (SELECT * FROM Product where Product_Id = tempProduct.Product_Id)
begin
UPDATE Product
SET Product_Id = tempProduct.Product_Id
FROM tempProduct
WHERE Product.Product_Id = tempProduct.Product_Id;
end
else
begin
INSERT into Product values (tempProduct.Product_Id)
end


Can you see what I've done wrong in the code or is there a better way to do this part?

 

[kevin197]

Ok, I've looked into it more and think I need to use a foreach loop, would this sound about right?

 

[MDXer]

To execute and update there are 2 ways.

1) Ole DB Command object you can write your update directly and map the columns or the better way is to use an SP and pass the columns into the SP as params.

2) Dump data into a "Staging table" that contains only the data to be updated and then execute a stored procedure that does a set based update.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[mrdenny]

No you don't need a FOR EACH LOOP. That will take for ever to execute.

UPDATE Product
SET Product_Id = tempProduct.Product_Id
FROM tempProduct
WHERE Product.Product_Id = tempProduct.Product_Id;

INSERT into Product 
(Product_Id, OtherColumns...
SELECT Product_id, OtherColumns...
FROM tempProduct.Product_Id
WHERE NOT EXISTS (SELECT * FROM Product WHERE Product.Product_Id  = tempProduct.Product_Id)

You don't need the IF EXISTS wrapping around everything. Also your update statement doesn't make much sense, but you are probably just using that as an example.

The first command will update any records which already exist. The second command will all in any records which don't already exist in the table.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog