none
SSIS-Lookup Match output to Temp Table

    Question

  • Hi Experts ,
    I am working on SSIS 2008,
    SQL table are the Source and Destination.
    Task is to Load Data from Source to Destination,If match then Update Destnation Table and not Match then Insert  into Destination Table .

    I have Taken OLEDB source ,Which Refer Source Table ,
    -Lookup ,Conatin Destination table ..
    -If Lookup Data not match "NO MATCH " then I insert into Destination Table

    My Requirement : IF Match ,Then I want to Insert those records into TEMP table .

    I know we Can use ##TEMP Table in SSIS  ,
    Make retain Same Connection Property to TRUE
    and Delay Validation TRUE ..

    But How Could I insert MaTCH data coming from LOOKUP to ##Temp Table ,
    What approach and Transformation should I use ??



    above approach does not work ,as I cannot see  Temp Table to Load Match Data? Also above this DataFlow Task I have create Execute SQL Task to Create ##temp Table..HELP ?
    • Edited by Rihan8585 Friday, November 01, 2013 6:25 AM
    Friday, November 01, 2013 6:14 AM

Answers

  • I want to Update match Data in Destination Table and Insert Non Match Data.
    Few Days back I read an Artile  on WWW.SQLservercentral.com i.e

    http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/102924/
    SEE Option 3 (INSERT THEN UPDATE)


    Where it was asked to Load Match Data into Temp Table and then use Temp Table to Update Destination Table Match Data..(Performance will be  Faster as Compared to OLEDB command .)
    I do not want to use OLEDB command ,because Performance is worst and I have approx 46 Million rows coming from Source.

    
    NOTE : I cannot create\not allowed to create  Physical Staging\temp Table ,I want to use ##temp Table ,so that I would not increase number of tables in my DB.

    Since the source data is having 46M records and I assume same big number will be in destination table for doing look up then this approach will not efficient. I would suggest the following:

    1. load the source data into raw/staging table.
    2. use the T-SQL MERGE statement to perform the upsert between final destination table and raw/staging table.

    Thanks, hsbal

    Tuesday, November 05, 2013 4:28 PM
  • POINT 2)
    I can not use t-sql MERGE statement bcoz my Source and Destination table are in Different Database and I do not want to HARDCODE DB name with Table Name  in T-sql Merge,That is why I am looking for SSIS solution.

    Why dont you want to use the db name in 2 part naming? This is absolutley correct and can be done.

    INT 1)
    USE RAW\STAGING TABLE : This is what my Question was, How to create Temp Table to Load MATCH data comming from LOOKUP ?? So that I can use this Temp Table to UPDATE MATCH DATA in DESTINATION TABLE .

    I said this may not be efficient because Look up component will have to store all the destination records in memory and then identify delta. This step will be less efficient if the destination table has millions of rcds and to me it is not worth to use LOOK Up.


    Thanks, hsbal

    Thursday, November 07, 2013 8:05 PM

All replies

  • hi 

    in ssis package you have 2 

    1-you can use recordset .

    RecordSet must use variable and 

    Variable must data type  object .

    2- you can use Raw File Destination.

    Friday, November 01, 2013 9:36 AM
  • Can you Please Explain it more ? or any other Solution to this ?
    Tuesday, November 05, 2013 6:02 AM
  • Hi Rihan,

    up to my understanding you need to create the temporary table structure first, then you need to load data into that temp table.

    try the below link, it might sort out the error you are getting.

    http://stackoverflow.com/questions/5631010/how-to-create-a-temporary-table-in-ssis-control-flow-task-and-then-use-it-in-dat

    or 

    http://sqlscrapbook.wordpress.com/2010/07/14/load-data-into-a-temp-table-using-ssis/

    Thanks,

    Anilkumar

    Tuesday, November 05, 2013 8:08 AM
  • Hi Anil,
    Thanks for the Reply..

    If you check my Entire Thread I have already mentioned that I have  Taken Execute Sql Task to create Temp Table ,and Set the required properies Like "Retain Same Connection" and "Delay Validation"..
    Even after Creating ##temp Table in Eexcute SQL task before Data Flow Task I do not see ##temp Table to LOAD MATCH Data into temp Table coming from LOOKUP ..
    IMAGE is just to give the Idea about Requiremnt ..
    Any Help?
    Tuesday, November 05, 2013 8:47 AM
  • Can i ask reason for temporary table? what will you be using this temporary table data for?
    Tuesday, November 05, 2013 9:08 AM
  • I want to Update match Data in Destination Table and Insert Non Match Data.
    Few Days back I read an Artile  on WWW.SQLservercentral.com i.e

    http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/102924/
    SEE Option 3 (INSERT THEN UPDATE)


    Where it was asked to Load Match Data into Temp Table and then use Temp Table to Update Destination Table Match Data..(Performance will be  Faster as Compared to OLEDB command .)
    I do not want to use OLEDB command ,because Performance is worst and I have approx 46 Million rows coming from Source.

    
    NOTE : I cannot create\not allowed to create  Physical Staging\temp Table ,I want to use ##temp Table ,so that I would not increase number of tables in my DB.
    • Edited by Rihan8585 Tuesday, November 05, 2013 9:55 AM
    Tuesday, November 05, 2013 9:52 AM
  • Have you considered using a Raw File as the destination?
    Tuesday, November 05, 2013 4:15 PM
  • I want to Update match Data in Destination Table and Insert Non Match Data.
    Few Days back I read an Artile  on WWW.SQLservercentral.com i.e

    http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/102924/
    SEE Option 3 (INSERT THEN UPDATE)


    Where it was asked to Load Match Data into Temp Table and then use Temp Table to Update Destination Table Match Data..(Performance will be  Faster as Compared to OLEDB command .)
    I do not want to use OLEDB command ,because Performance is worst and I have approx 46 Million rows coming from Source.

    
    NOTE : I cannot create\not allowed to create  Physical Staging\temp Table ,I want to use ##temp Table ,so that I would not increase number of tables in my DB.

    Since the source data is having 46M records and I assume same big number will be in destination table for doing look up then this approach will not efficient. I would suggest the following:

    1. load the source data into raw/staging table.
    2. use the T-SQL MERGE statement to perform the upsert between final destination table and raw/staging table.

    Thanks, hsbal

    Tuesday, November 05, 2013 4:28 PM
  • Another problem you're going to encounter when using a destination table as the lookup are deadlocks, this especially true for large data sets. I'd create an in-memory lookup cache of the destination rather than doing a direct lookup on the table.
    Tuesday, November 05, 2013 4:38 PM
  • Since the source data is having 46M records and I assume same big number will be in destination table for doing look up then this approach will not efficient. I would suggest the following:

    1. load the source data into raw/staging table.
    2. use the T-SQL MERGE statement to perform the upsert between final destination table and raw/staging table.

    Thanks, hsbal

    Hi Harry ,
    Thanks for the Reply
    POINT 1)
    USE RAW\STAGING TABLE : This is what my Question was, How to create Temp Table to Load MATCH data comming from LOOKUP ?? So that I can use this Temp Table to UPDATE MATCH DATA in DESTINATION TABLE .

    POINT 2)
    I can not use t-sql MERGE statement bcoz my Source and Destination table are in Different Database and I do not want to HARDCODE DB name with Table Name  in T-sql Merge,That is why I am looking for SSIS solution.

    Wednesday, November 06, 2013 6:27 AM
  • POINT 2)
    I can not use t-sql MERGE statement bcoz my Source and Destination table are in Different Database and I do not want to HARDCODE DB name with Table Name  in T-sql Merge,That is why I am looking for SSIS solution.

    Why dont you want to use the db name in 2 part naming? This is absolutley correct and can be done.

    INT 1)
    USE RAW\STAGING TABLE : This is what my Question was, How to create Temp Table to Load MATCH data comming from LOOKUP ?? So that I can use this Temp Table to UPDATE MATCH DATA in DESTINATION TABLE .

    I said this may not be efficient because Look up component will have to store all the destination records in memory and then identify delta. This step will be less efficient if the destination table has millions of rcds and to me it is not worth to use LOOK Up.


    Thanks, hsbal

    Thursday, November 07, 2013 8:05 PM