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