We are having a SSIS Package, which is taking very long time in the Pre-Execute stage once in a while.
Background:
We have to pull sales transaction information from a humongous database.
What we are doing in our SSIS package is:
- Execute SQL Task: Create global temporary table to hold the transactionIds with unique clustered index
- Data Flow Task: Select query to pull transaction information by joining with the global temporary table created in Step No. 1 and load to target table in another SQL Server.
What is our issue:
Step No.2 is stuck up in the Pre-Execute stage for long time. When I run sp_whoisactive on the source server, there is nothing running in the humongous database.
When I query SSIS catalog, it comes as given below. The last row, Pre-Execute is stuck for more than 7 hours. The Package generally completes within an hour.
| message |
|---|
| Load staging table from HumongousDB:Validation has started. |
| Load staging table from HumongousDB:Information: Validation phase is beginning. |
| Load staging table from HumongousDB:Validation is complete. |
| Load staging table from HumongousDB:Information: Prepare for Execute phase is beginning. |
| Load staging table from HumongousDB:Information: Pre-Execute phase is beginning. |
What we have already done in our package:
- DelayValidation to True in the Data Flow Task & connection managers for source, destination
- ValidateExternalMetaData to False in Data Flow Task source & destination
Our SSIS Catalog SQL Server version is: Microsoft SQL Server 2016 (SP2-CU15) (KB4577775) - 13.0.5850.14 (X64) Sep 17 2020 22:12:45
Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2012 Datacenter 6.2 (Build 9200: ) (Hypervisor)
can you please guide us on what can we do to avoid this issue ?