Server X (hosting db A 400 GB ) and Server Y (hosting db B 400 GB ) located 2000 miles from our office Much better to use an Agent Job and job steps to control package execution flow. What might cause slow execution of in Data transformation when SSIS executed as part of "Execute Package Tasks" ?įrankly, I fail to see why that task ever gets used for anything but totally trivial package executions. Since it should be "one time" migration operation I was plan automate it by running all SSIS as set of "Execute Package Tasks" Server has 24 GB of RAM and 4 CPU, database 1 and database 2 located on 1TB SSD drive I can see in in debugger of SSDT that data transformation tasks in SSIS3 becoming extremely slow when executed in step as Execute Package Task SSIS3 execution time 20 min (Some Data flow sources have tables with 25M row, total number of row inserted by this SSIS ~ =120M)Įxecution of each SSIS as "Execute Package Task")Įxecute Package Task 1 (SSIS1 )- execution time 2 minutesĮxecute Package Task 1 (SSIS2 )- execution time 3 minutesĮxecute Package Task 1 (SSIS3 )- execution time 13 Hours!!! On each Data flow Task I set AutoAdjust BufferSize =True and and for every table with Row count > 100000 I set Maximum insert commit size = 100000 Originally every SSIS build and Saved using "Import Data" wizard from from SSMS 2017Įach SSIS contain sequential control flows Preparation SQL Tasks (Truncate tables) and Data Flow Tasks (each Data flow has 5 source->destination transformations) Using SSDT 2017, all SSIS executed in Debug move from SSDT on Server A I build ~ 6 SSIS, each SSIS load one or more schema (number of tables and each SSIS in as low as 17 and as much as 60) I need to load 160 GB from ~250 tables Server A database 1 to Server A database 2 I am creating data migration scenario for SQL 2017
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |