Post reply

load TableC from TableB based on value of TableA in SSDT/SSIS

  • September 23, 2014 at 4:39 pm

    #311090

    I have 3 tables-

    --Server 1(SQLSERVER)

    CREATE TABLE TableA (GROUP_ID INT

    ,STATUS VARCHAR(10))

    --Server 2(Oracle)

    CREATE TABLE TableB (GROUP_ID INT

    ,NAME VARCHAR(10)

    ,STATE VARCHAR(50)

    ,COMPANY VARCHAR(50))

    --Server 1

    CREATE TABLE TableC (GROUP_ID INT

    ,NAME VARCHAR(10)

    ,STATE VARCHAR(50)

    ,COMPANY VARCHAR(50))

    Sample data

    INSERT INTO TableA (1, 'READY'),(2,'NOT READY'),(3,'READY'),(4,'NOT READY')

    INSERT INTO TableB (1, Mike, 'NY', 'aaa'), (1, Rick, 'OK','bbb'), (2, Smith, 'TX','ccc'), (3, Nancy, 'MN','bbb'), (4, Roger, 'CA','aaa')

    I am trying to build a SSDT(SSIS 2012) package to load the data in TableC from TableB for only those GROUP_ID which has STATUS= 'READY' in TableA.

    I need to accomplish this by using a project level parameter or variable for TableA->GROUP_ID and STATUS because i will be doing this for about 60 tables and those values might change.

    I must build a SSIS package, it is a requirement.

    using linked server is not preferred. unless its impossible to achieve through SSIS.

    Any help would be appreciated.

  • September 23, 2014 at 4:53 pm

    #1747681

    INSERT INTO tablec SELECT B.* FROM TABLEA A JOIN TABLEB B ON A.GroupID = B.GroupID AND A.Status = 'READY'

    This is the TSQL, the SSIS approach can either use the TSQL directly, or you could use a lookup.

    Steve.

  • September 23, 2014 at 6:41 pm

    #1747689

    thank you for the response steve, but i won't be able to do join since the tables are in different servers. sorry should ve mentioned it. i have edited my post. and it must be done in SSIS.

  • September 23, 2014 at 7:28 pm

    #1747693

    Linked servers could do it.

    But for pure SSIS use a lookup on TableA, effectively let's you join.

    Steve.

  • September 23, 2014 at 8:12 pm

    #1747699

    Steve, i was just editing my post. Linked server are too slow for this task as we are dealing with a huge table in Oracle side. and yes i was researching on lookup component can you please give me further more advice on how i can achieve this by using Lookup!. thanks

  • September 23, 2014 at 8:16 pm

    #1747700

    🙂 like to add any more restrictions?? How big is 'huge'?

    As I've said a couple of times, go with a lookup.

    Steve.

  • September 23, 2014 at 8:45 pm

    #1747706

    There is a sample plus some videos etc (prob more on youtube) at the bottom of that page that shows how to use the lookup.

    Steve.

  • September 25, 2014 at 8:58 am

    #1748217

    'Lookup' didn't work as I couldn't figure out how to use parameters in 'lookup'. but I was able to get the result by using 'Merge Join' and 'Conditional Split' 🙂

Viewing 8 posts - 1 through 7 (of 7 total)

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