0

I'm relatively new to Visual Studio and writing SSIS packages. I am using SSIS packages to automate running some SQL on a schedule to produce flat files. The package contains one data flow task, which has an OLE DB Source, then uses a multicast, to send the output to two flat file destinations (same data, two different locations).

When I Start the package, on the Control Flow screen, I get the spinning yellow throbber. And it sits there, and spins, and spins, for a very long time before any records start getting written in the Data Flow tab. In looking at the Progress tab, it seems to be in Pre-Execute during this time.

Running the SQL straight up in SSMS is a lot faster. What can I do to make the Pre-execute phase in VS SSIS go faster?

send2steph
  • 1
  • 1
  • PreExecute phase is usually validation of your source. Best guess would be that the source procedure?, maybe view are complex/slow to generate metadata. That or if it's a table, perhaps a different process locks the table such that validation can't get a schema lock on it? Were it me, I'd run [sp_whoisactive](https://github.com/amachanic/sp_whoisactive) or something similar to look at the wait types while your package is attempting to validate the metadata and then update this post. Alternately, run the package on a server with SQL Server installed - skips the VS debugger. – billinkc Apr 28 '21 at 01:41
  • The query is rather complex. It takes 20-ish minutes to complete in SSMS. But in VS it's taking around two hours. – send2steph Apr 28 '21 at 04:31
  • Still taking forever after going through and setting all the Delay Validation properties to True on the package, task and Connections per: https://stackoverflow.com/questions/15508594/ssis-data-flow-task-hangs-on-excecution-of-pre-excecute-phase?rq=1 http://thesqlgirl.com/2016/09/17/ssis-package-validation/ – send2steph Apr 28 '21 at 04:52
  • @send2steph it could be that is taking long to acquire the network connection to the source server. That would explain why still takes long even when delaying validation. One way to test it is to set the connection timeout to 15 seconds or so and see if it timeouts. – Jayvee Apr 28 '21 at 08:29
  • Yeah, Delay Validation is only going to push the penalty time to later in the package phase. What source is the very complex query using? If SQL Server, what version? Inline query or is it a stored procedure? – billinkc Apr 28 '21 at 14:29
  • I have the same issue with both stored procedures and running a SQL command in an OLE DB Source Editor. Microsoft SQL Server 2017 (RTM-CU22) (KB4577467) - 14.0.3356.20 (X64) Aug 20 2020 22:33:27 Copyright (C) 2017 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor) – send2steph Apr 28 '21 at 21:58

0 Answers0