SSIS 2008 Lookup Task for Child Table Population

I am creating an SSIS 2008 (R2) package to do a data transformation job.  I have child tables and a parent. The primary key of the child record is stored in a column in the parent.  I want to use SSIS (perhaps the lookup task will do this) to insert records into the child where they do not exist already, and put the appropriate key into the parent table.  Will the Lookup component do this?  

To illustrate, here is the current T-SQL from a stored proc that populates the child table.

How would I accomplish this using SSIS data flow tasks?  Obviously, I can output to a table and execute this SQL but I am wondering if the Lookup, Merge or other tasks would do this more efficiently?


-- This inserts only new records that do not exist
INSERT INTO dbo.Users_ChildTable (    
    FullName
   ,LastName
   ,FirstName
   ,MI
   ,Address1
   ,Address2
   ,PostalCode
   ,Department
   ,SSN
   ,CreateDate
)
SELECT DISTINCT
    a.UserFullName,
    a.UserLastName,
    a.UserFirstName,
    a.UserMI,
    a.UserAddress1,
    a.UserAddress2,
    a.UserPostalCode,
    a.UserDept,
    a.UserSSN,
    GETDATE()
FROM
    dbo.ParentTableData a LEFT JOIN
    dbo.Users_ChildTable u ON
        ISNULL(a.UserLastName,'') = ISNULL(u.LastName,'') AND
        ISNULL(a.UserFirstName,'') = ISNULL(u.FirstName,'') AND
        ISNULL(a.UserMI,'') = ISNULL(u.MI,'') AND
        ISNULL(a.UserAddress1,'') = ISNULL(u.Address1,'') AND
        ISNULL(a.UserAddress2,'') = ISNULL(u.Address2,'') AND
        ISNULL(a.UserPostalCode,'') = ISNULL(u.PostalCode,'') AND
        ISNULL(a.UserDept,'') = ISNULL(u.Department,'') AND
        ISNULL(a.UserSSN,'') = ISNULL(u.SSN,'') 
WHERE
    u.SSN IS NULL AND
    u.LastName IS NULL;
    
-- Now update the key field in the parent for the appropriate UserID

    UPDATE dbo.ParentTableData
    SET dbo.ParentTableData.UserID = u.UserID
    FROM
        dbo.ParentTableData a INNER JOIN
        dbo.Users_ChildTable u ON
        ISNULL(a.UserLastName,'') = ISNULL(u.LastName,'') AND
        ISNULL(a.UserFirstName,'') = ISNULL(u.FirstName,'') AND
        ISNULL(a.UserMI,'') = ISNULL(u.MI,'') AND
        ISNULL(a.UserAddress1,'') = ISNULL(u.Address1,'') AND
        ISNULL(a.UserAddress2,'') = ISNULL(u.Address2,'') AND
        ISNULL(a.UserPostalCode,'') = ISNULL(u.PostalCode,'') AND
        ISNULL(a.UserDept,'') = ISNULL(u.Department,'') AND
        ISNULL(a.UserSSN,'') = ISNULL(u.SSN,'')

Open in new window

richxyzAsked:
Who is Participating?
 
vdr1620Connect With a Mentor Commented:
You can use Lookup to do this.. you will need to do the Lookup on the key column and use OLEDB command Task to update the Values or OLEDB destination to insert values.

In the OLEDB command make sure the parameters are mapped correctly in the same order

http://www.sqlis.com/post/OLE-DB-Command-Transformation.aspx
0
 
richxyzAuthor Commented:
I was curious about how efficient, i.e. performance, doing it with the lookup task would be as opposed to my current stored procedure method.  That's why I said PARTIALLY.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.