Sarah Ellis
asked on
Upgraded package runs slowly intermittently - maybe related to communication or network?
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:
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!
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.
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.
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!
ASKER
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.
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.
>> 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)
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)
ASKER
It turned out to be a firewall issue. Thank you for your help. It's running fine now.
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..
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..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.