Freigeben über

You get an error when you attempts to save SSIS packages to the MSDB package store if the network packet size >=16388 Bytes

  • Artikel

In SQL Server 2005 if you configure network packet size to 16388 or greater, and attempts to save SSIS packages to the MSDB package store, It will fail with the following error message:

The SaveToSQLServer method has encountered OLE DB error code 0x80004005 (Communication link failure).The SQL statement that was issued has failed.

Attempting to import a package using Management Studio's Integration Services Connection fails with error:

Communication link failure. SSL Provider: Packet size too large for SSL Encrypt/Decrypt operations [50]. (Microsoft Native Client)

The same behaviour can be seen if we are deploying the package from the Visual studio.

Steps to reproduce

1. Reconfigure Network Packet Size to 16388:

sp_configure 'network packet size (B)', 16388

go

reconfigure with override

2. In SQL Management Studio, drill into the server and then Management, and then right-click on Maintenance Plans and choose New Maintenance plan.

3. Accept the name and click OK.

4. Click the Save Selected Items button on Management Studio's toolbar.

Results: Error: The SaveToSQLServer method has encountered OLE DB error code 0x80004005 (Communication link failure).The SQL statement that was issued has failed.

Resolution

Dropping the run value for Network packet size to 16387 or less, and the maintenance plans save as packages and the packages import successfully.

You can do that using

1. Go to server properties by right clicking the instance NameàAdvancedàNetwork Packet Size

Or

2. sp_configure 'network packet size (B)', <numeric value less than 16388>

go

reconfigure with override

 

Reason:

The reason is that when SSIS is loading and saving packages into SQL Server MSDB, the connection used is encrypted with a server generated self-signed SSL certificate so that the packages and any secrets such as connection manager passwords within the SSIS package are not visible as plain text on the network. That form of encryption is incompatible with the large network packet size in SQL 2005.

Note: This issue is fixed in SQL Server 2008

 

Author : Lalitesh(MSFT) , SQL Developer Engineer , Microsoft

Reviewed by : Jason(MSFT) , SQL Escalation Services , Microsoft

Comments

  • Anonymous
    March 25, 2010
    Resolved my issue and saved my precious $$$ my thanks to you !

  • Anonymous
    July 08, 2010
    rick.foster@tusd1.org  It is not fixed in SQL Server 2008, I started out having no problem opening Maintenance packages and then the "Name is null" error started occurring on one package, it was solved by reconfiguring the network packet size to 16383.  Also the workaround to go directly to SSIS does not always work, for some reason not all maintenance packages are listed under MSDB, maybe it is caused because there are multiple packages coming from mulitple SQL instances as SSIS does not have instances.  Rick

  • Anonymous
    May 12, 2015
    Nice

  • Anonymous
    December 13, 2015
    SQL Server 2008 R2。not fixed?