CODEWITHSUNDEEP
sql-server-2008ssis
starklord
starklord

Reputation: 291

How to perform join and updates from two or more tables using SSIS ?

I am working on an ETL task on SSIS. In this task I am trying to update a table 'Vendor' where I want to use an another table for some references and conditions in 'where' clause. Here is what I want to do - 

ALTER TABLE Vendor ADD Imported BIT
Update  Vendor
SET     Imported = 1
-- (133 row(s) affected)

Update  Vendor
SET     Imported = 0
where   VendorName in (Select VendorName from MyDatabase..Vendor)

Update  l
SET     VendorName = t.VendorName,  -- Vendor name looks imported one has proper Capital letters in name, use the one from to-be-imported one
    Address1 = t.Address1,
    Address2 = t.Address2,
    City = t.City,
    State = t.State,
    Country = t.Country,
    PostalZip = t.PostalCode,
    Phone = t.NewPhone,
    Email = t.Email     
From    MyDatabase..Vendor l, Vendor t
where   l.VendorName = t.VendorName
and t.Imported = 0
-- (2 row(s) affected)

Please suggest me what should be the suitable procedure on SSIS package. I can use Execute SQL Task for the same but want to use an alternate approach as in Execute SQL Task I have to create a separate task for each SQL statement. Your precious replied will be welcomed.

Upvotes: 1

Views: 51

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

To do a multi-statement Execute SQL Task like this, you have to end each separate statement with a semi-colon. Do this, and it will work.

Upvotes: 1

Related Questions