Upgraded package runs slowly intermittently - maybe related to communication or network?

Sarah Ellis
Sarah Ellis used Ask the Experts™
on
First, I'm a novice with SSIS, having mostly just maintained existing packages.  I have a project that I upgraded from SQL Server 2008 R2/Visual Studio 2008 to SQL Server 2016 (deployment model) in VS 2017.  All of the packages work running locally on my machine just as they always have, with the exception of one.  

This loops through oledb source servers  using a connection string built by variables received from a query.  Then it starts the control flows, which each has a series of data flow queries.    The weird thing is sometimes it runs fine.  Most of the time I get errors like this:

[NetComply - CollectionMembers [212]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Protocol error in TDS stream".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Communication link failure".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "TCP Provider: An existing connection was forcibly closed by the remote host.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Communication link failure".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "TCP Provider: An existing connection was forcibly closed by the remote host.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Communication link failure".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "TCP Provider: An existing connection was forcibly closed by the remote host.
 [NetComply - UserLogon [266]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Communication link failure".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Communication link failure".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "TCP Provider: An existing connection was forcibly closed by the remote host.
 [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on NetComply -ConfigLog returned error code 0xC0202009.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.
[NetComply - AuditRsltDisks [167]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Where it stops.It stops at different places.  You can see that some rows are transferring successfully, then it just stops.  Bare in mind that the exact same package in 2008 runs fine on my same machine.  It's hitting the same source  servers.  The only difference is that the destination server of the data flow is a new SQL Server 2017 that is a copy of our 2008 R2 production server.  When it does run successfullly, it runs really slowly - like 1.25 hrs vs 12 min in 2008.

It seems to be related to communication.  But this is out of my wheelhouse.  Any ideas?  Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> using a connection string built by variables received from a query

Kindly double check for any hardcoded connection strings in your package instead of variables passed correctly..

>> it runs really slowly - like 1.25 hrs vs 12 min in 2008.

This might happen due to missing indexes or outdated statistics on your SQL Server 2017 machine..
Kindly rebuild the indexes on the involved tables and update statistics(if required) which should help resolve it out.
Sarah EllisSenior Business Intelligence Developer

Author

Commented:
Hi Raja,
Thank you for your response.  

>>Kindly double check for any hardcoded connection strings in your package instead of variables passed correctly..

The variables are working, since it does intermittently run completely and cycles through all of the source servers.

>>This might happen due to missing indexes or outdated statistics on your SQL Server 2017 machine..
Kindly rebuild the indexes on the involved tables and update statistics(if required) which should help resolve it out.

The 2017 machine has been freshly restored from our 2008 production and the destination database is a Staging db with almost no indexes at all.  The first step of the package is to truncate all of the Staging tables.   One place it really hangs up is  in Pre-Execution.  It just sits there for a long time doing nothing.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> The variables are working, since it does intermittently run completely and cycles through all of the source servers.

Then I doubt that there might be some intermittent network issues in your environment causing it to disconnect and fail..
Kindly confirm whether you are running SSIS package on the Server exactly or from some other servers..

>> One place it really hangs up is  in Pre-Execution.

Kindly try rebuilding the package which can help out a little bit in terms of connection manager issues to your SQL Server 2017 database(if any)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Sarah EllisSenior Business Intelligence Developer

Author

Commented:
It turned out to be a firewall issue.  Thank you for your help.  It's running fine now.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Thanks for the update..
Just wondering, why firewall blocks only few times and not the other times, can you double check on that one..
If the issue is resolved, kindly request you to close the question accordingly..
Senior Business Intelligence Developer
Commented:
I don't understand it myself.  Someone else was handling the firewall.  They said there was a missing rule.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial