Post reply

Capture value from INSERT statement in SSIS Execute SQL Task

  • January 18, 2013 at 9:20 am

    #268830

    I have the following query in an SSIS Execute SQL Task (SQL Server 2008):

    INSERT INTO mobile.Employee

    (FirstName,LastName,PhoneNumber,Title,EmployeeID,Location)

    (SELECT givenName,sn,telephoneNumber,title,cn,

    physicalDeliveryOfficeName

    FROM OPENQUERY(adlink,

    'select givenName,sn,telephoneNumber,title,cn,

    physicalDeliveryOfficeName

    from ''LDAP://myserver/dc=mydomain,dc=com''

    where objectCategory=''user'' and objectClass=''user''')

    WHERE whenCreated >= getdate()-1

    This query may return one or more rows. After this task is executed, I need to pass all the EmployeeID's that have been inserted to two more Execute SQL Tasks (or I can add more T-SQL code to this Execute SQL Task; I don't care which). My question is, how do I do this? I guess I could create a table variable in my first Execute SQL Task and populate it by running my query again, then use that table variable to do the other things I have to do. But this seems clumsy. I'm looking for a solution that won't require me to run my query twice, for starters.

    Thanks!

  • January 18, 2013 at 1:04 pm

    #1578860

    As it turns out, this was so simple and obvious I'm embarrassed. :blush: Here's my code:

    DECLARE @EmployeeIDs TABLE(FirstName VARCHAR(30), LastName VARCHAR(30), PhoneNumber VARCHAR(50), Title VARCHAR(50), EmployeeID VARCHAR(6), Location VARCHAR(30))

    INSERT INTO @EmployeeIDs

    SELECT givenName,sn,telephoneNumber,title,cn,

    physicalDeliveryOfficeName

    FROM OPENQUERY(adlink,

    'select givenName,sn,telephoneNumber,title,cn,

    physicalDeliveryOfficeName,displayName,whenCreated

    from ''LDAP://myserver/dc=mydomain,dc=com''

    where userAccountControl <> 514

    and objectCategory=''user'' and objectClass=''user''')

    WHERE whenCreated >= getdate()-1

    INSERT INTO mobile.Employee

    (FirstName,LastName,PhoneNumber,Title,EmployeeID,Location)

    (SELECT FirstName,LastName,PhoneNumber,Title,EmployeeID,Location

    FROM @EmployeeIDs)

    Now I can add more T-SQL to do what I need from in my next two operations by pulling EmployeeID from @EmployeeIDs

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply