CODEWITHSUNDEEP
sqlsql-serversql-server-2008ssis
Iron_Tri
Iron_Tri

Reputation: 85

SSIS Update a particular column

I have a table in SQL Server with many fields. Two of which are IDNumber and Resolved(Date) Resolved is Null in SQL Server. Then I have data with IDNumber and and Resolved(Date) that I want to import and update the table. I want it to find the matching IDNumber and put in the updated resolved date. How can I do this?

Thanks!

Upvotes: 1

Views: 8579

Answers (1)

GarethD
GarethD

Reputation: 69769

When doing updates SSIS will update a row at a time, which can be quite costly. I would advise to use an Execute SQL task to create a staging table:

IF (OBJECT_ID(N'dbo.tmpIDNumberStaging') IS NOT NULL 
    DROP TABLE dbo.tmpIDNumberStaging;

CREATE TABLE dbo.tmpIDNumberStaging
(       IDNumber    INT NOT NULL,
        Resoved     DATE NOT NULL
);

Then use a Data Flow task to import your data to this table (not sure what your source is, but your destination would be OLE DB Destination. You may need to set "Delay Validation" on the Data Flow task to false and/or "Validate External Meta Data" to false on the destination because the destination is created at run time).

Finally use this staging table to update your main table (and drop the staging table to clean up)

UPDATE  YourMainTable
SET     Resolved = st.Resolved
FROM    YourMainTable t
        INNER JOIN dbo.tmpIDNumberStaging st
            ON st.IDNumber = t.IDNumber
WHERE   t.Resolved IS NULL;

-- CLEAN UP AND DROP STAGING TABLE
IF (OBJECT_ID(N'dbo.tmpIDNumberStaging') IS NOT NULL 
    DROP TABLE dbo.tmpIDNumberStaging;

Upvotes: 9

Related Questions