June 24, 2015 at 12:47 pm
Hi All,
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.So using For each loop container we are storing the values of Ids in object variable and then 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 27, 2015 at 5:54 am
Any help ?
--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
🙂
July 4, 2015 at 5:45 pm
I use ado.net connections with Multiple Active Result Sets (MARS) resource set to True and Packet Size set to 32767
to utilize the Balanced Data Distributor - http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/127512/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply