Post reply

Need help in performance of SSIS

  • June 28, 2015 at 11:11 pm

    #319495

    Hi All,

    Note- I have posted this in another forum, not sure if it was wrong one so reposting it again.Please bear with me.

    I have an SSIS package with following design.

    1. I have 15 DFTs. Each DFT is having SQL server source and destination but on different servers.

    2. It is simple load based on ids in a source table which is common for all source tables.we are storing the values of Ids in object variable and then using For each loop container loading all DFTs for every id from source table to destination table

    3.Across all DFTs we are processing almost more than 10 million records.

    4.I have used settings as Default max Buffer size as 20 MB and rows as 20000.Rows per batch 20000 and Max Insert Commit Size is 200000.

    5.Set the packet size of connection to maximum.

    6.I am loading parallel DFTs by setting MaxConcurrentExecutables to 6.

    While executing this package from My machine using BIDS having 4 GB RAM and 120 GB hard disk it is taking almost 1.5 - 2 hrs. The same amount of time it was taking on UAT server.But off late on UAT server having 128GB RAM and almost 180 GB free space with same connection strings and config settings, it is taking 15-16 hours to execute.

    On UAT this package is trigerred by windows services along with other 40-50 SQL Server jobs.But due to this SSIS job others jobs are also not executing.

    The strange observation is that when the service is getting stopped the package execution gets completed within 5 mins which I can check through logs and by verifying records count from source tables.

    Any idea about this ?

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
     🙂

  • June 28, 2015 at 11:14 pm

    #1808662

    Will loading the id values in a temp table and making join with source to avoid For each loop container help me in it ?

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
     🙂

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

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