Thursday, March 22, 2012

Error deploying SSIS package to SQL Server

I am getting strange error on deploying ssis package to SQL Server.

TITLE: Package Installation Wizard
-

Could not save the package "C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\Integration Services Project1\Integration Services Project1\bin\Deployment\DataTrans.dtsx" to SQL Server "(local)".

-
ADDITIONAL INFORMATION:

The SaveToSQLServer method has encountered OLE DB error code 0x80004005 ([DBNETLIB][ConnectionOpen (PreLoginHandshake()).]Encryption not supported on SQL Server.). The SQL statement that was issued has failed.


-

The SaveToSQLServer method has encountered OLE DB error code 0x80004005 ([DBNETLIB][ConnectionOpen (PreLoginHandshake()).]Encryption not supported on SQL Server.). The SQL statement that was issued has failed.


-
BUTTONS:

OK
-

=====================================================
=============================================

Could not save the package "C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\Integration Services Project1\Integration Services Project1\bin\Deployment\DataTrans.dtsx" to SQL Server "(local)". (Package Installation Wizard)

=============================================

The SaveToSQLServer method has encountered OLE DB error code 0x80004005 ([DBNETLIB][ConnectionOpen (PreLoginHandshake()).]Encryption not supported on SQL Server.). The SQL statement that was issued has failed.


-
Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServer(Package package, IDTSEvents events, String serverName, String serverUserName, String serverPassword)
at Microsoft.SqlServer.Dts.Deployment.DtsInstaller.SavePackageToSqlServer(WizardInputs wizardInputs, String packagePassword, Boolean bUseSeverEncryption, String serverName, String userName, String password, String packageFilePath, List`1 configFileNames)
at Microsoft.SqlServer.Dts.Deployment.DtsInstaller.InstallPackagesToSqlServer(WizardInputs wizardInputs)

=============================================

The SaveToSQLServer method has encountered OLE DB error code 0x80004005 ([DBNETLIB][ConnectionOpen (PreLoginHandshake()).]Encryption not supported on SQL Server.). The SQL statement that was issued has failed.


-
Program Location:

at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.SaveToSQLServer(IDTSPackage90 Package, IDTSEvents90 pEvents, String ServerName, String ServerUserName, String ServerPassword)
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServer(Package package, IDTSEvents events, String serverName, String serverUserName, String serverPassword)

Ashwani Sharma has the following solution:

As you may know, the SSIS runtime forces use of encryption when loading or saving packages from/to SQL Server. As part of that, certificates have to be present and validated by the transport layer. If certificates are not properly setup, users get errors during deployment to SQL Server that say something like this:

The SaveToSQLServer method has encountered OLE DB error code 0x80004005 ([DBNETLIB][ConnectionOpen (PreLoginHandshake()).]Encryption not supported on SQL Server.). The SQL statement that was issued has failed.

In the current CTP builds, if you get this error message, you'll need to get a provisioned certificate and install it. A couple of links that describe this well are http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q276553 and http://support.microsoft.com/kb/318605.

We've been working with several customers in the past who ran into this issue and we weren't able to figure out what was going on due to the infrastructure used here in Redmond. Thanks for your patience, guys!

Hopefully we'll take care of this during setup so that those of you who did run into this issue don't anymore.

|||

Hi all,

I'm having the same issue in the Release version of developer edition SQL 2005. Now, the only caveat is that I also have SQL Express installed from visual studion 2005, which was installed first. Would this have any affect on me deploying SSIS Packages to my machine?

|||

I am also receiving this error:

===================================

Save to SQL Server failed.

No description found (SQL Server Import and Export Wizard)

===================================

No description found


Program Location:

at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.SaveToSQLServer(IDTSPackage90 Package, IDTSEvents90 pEvents, String ServerName, String ServerUserName, String ServerPassword)
at Microsoft.SqlServer.Dts.DtsWizard.Execute.DoFileSaveStep(Exception& ex)


I am running the Release version of Dev SQL Server 2005.

Any Ideas?

|||

Hi,

I would recommend to open a new thread since this "old" one is based on a CTP and so your problem might not get the attention it deservs...

|||Yes, I have an idea! I just resolved this problem, and hopefully it will work for you as well.

Found an answer for a very similar error burried in a comment here: Oren Ellenbogen

Basically, this is a symptom of your MSXML registration being corrupted. Reregister MSXML by running these two commands:

regsvr32.exe msxml3.dll


regsvr32.exe msxml6.dll



I wasn't sure if the issue would be on the client or the server, as both of them would probably need to have MSXML working nicely. So I ran those two commands in both places, and then was (HAPPILY!) able to deploy my SSIS packages in comfort.

There are curse words involved, but I wrote about it in my blog. So I could put in images & the answer outside of a long, old thread on MSDN.|||

Kapil Aggarwal wrote:

Ashwani Sharma has the following solution:

As you may know, the SSIS runtime forces use of encryption when loading or saving packages from/to SQL Server. As part of that, certificates have to be present and validated by the transport layer. If certificates are not properly setup, users get errors during deployment to SQL Server that say something like this:

The SaveToSQLServer method has encountered OLE DB error code 0x80004005 ([DBNETLIB][ConnectionOpen (PreLoginHandshake()).]Encryption not supported on SQL Server.). The SQL statement that was issued has failed.

In the current CTP builds, if you get this error message, you'll need to get a provisioned certificate and install it.

is it absolutely necessary to get a provisioned certificate if the package is saved to sql server 2005? sql server 2005 provides a CREATE CERTIFICATE statment which creates and installs a certificate.

also, is configuring encryption on sql server 2005 different from previous versions of sql server?

|||

Hi,

The user who is trying to save the SSIS package in SQL Server MSDB database should be the member of all "dts" related roles like dtsadmin in msdb database.

Please try it will work.

Thanks,

Arijit

No comments:

Post a Comment