SSIS-2012 -PackageExecution take long Time


  • Hi Experts,
    I am working on SSIS 2012 .
    I have One DFT which has almost 5-6 Lookup and 6-7 MergeJoin in it and 6-7 Sort Transformation.

    Data coming from the source is almost 3-4 Million Records .
    When I Right Click on SSIS Package to Execute it ,It Take almost 2-3 Minutes to start the Execution.
    I saw DFT ,it shows Yellow Circle Moving .

    When Execution start ,My Package take hardly 20-25 Seconds of time to Load 4 million records from Source To Destination .
    But Why Before Execution start it Takes 2-3 minutes ??

    I tried  :
    1)Right Click on DFT and Delay Validation to TRUE  2)Right Click on Connection Manager -Retain Same Connection Property to TRUE...But Nothing HELPED .. :(

    NOTE: I have Two Different SQL server from Where I am Intergating Data .

    • Edited by MS308 Tuesday, July 16, 2013 6:25 AM
    Tuesday, July 16, 2013 6:16 AM


All replies

  • First of all: RetainSameConnection tells the connection manager to only use one connection, so I don't see how this can speed up your package.

    The reason it takes 2-3 minutes is because you have sort transformation. In order to sort the data, all of the data (yes, you read that well, ALL of the data) needs to be loaded in memory. Hence the long starting time.

    You need to sort in the queries themselves instead of using sort transformations. See here how:

    Sort Data for the Merge and Merge Join Transformations

    Another reason might be the lookup components. If you configured them to use full caching, all of the lookup data needs to loaded into memory as well. In order to speed this up, you need to write queries in your lookup component that only select the data you need to do the lookup. Keep the footprint small.

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Tuesday, July 16, 2013 7:25 AM
  • Hi,

    Thanks for the Reply.
    But in almost all the scenario I do have DataConversion or Derived Column after Source or Lookup Which are to be sorted\used in Merge Join,so I cannot always sort these columns in Queries :(

    Any Help on this .

    • Edited by MS308 Thursday, July 18, 2013 5:11 AM change
    Thursday, July 18, 2013 5:11 AM
  • Whatever you do in the data conversion or derived column components, do it in the source query. Then sort in your source query and then do your merge join.

    Or if possible, avoid the merge join at all and do the join in the queries.

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, July 18, 2013 7:00 AM