I am moving our SLX 7.5.3 from SQL 2000 to 2005 (actually upgrading from 7.2.1 at the same time on new servers) which includes several DTS packages I am trying to convert to SSIS some of which use the SLX provider. The new SQL server is a 64 bit 2008 R2 box.

I've converted them and they actually execute fine in Integration services. I then used the configuration file method to schedule them in SQL agent, however, I cannot get them to run. If I use native SQL connections, they work. As soon as I try use the SLX provider for the connections I get an error (see below). I want to use the provider so it creates the ID's.

Appreciate if someone could point me in the right direction

Thx - Leon

Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.  End Error  Error: 2011-02-04 13:54:06.78     Code: 0xC0016016     Source:       Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available 

 **UPDATE** Also tried saving directly to server for access and roles but still generates an error when running - "DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "SERVERNAME.SLX_PROD75.admin" failed with error code 0xC0202009"

Regarding your first error - that's a standard SSIS thing which probably arises because you have got the Protection Level of the package set to EncryptSensitiveWithUserKey.

If you change it to EncryptSensitiveWithPassword (and check the on-line help to find out why and what the implications are) you'll get past this error. I'm guessing that your native SQL connections were 'trusted' and therefore did not require sensitive information to be stored in the package.

Regarding error #2 - try copying the 'Advanced Properties' of the SLX OLEDB Connection string to the 'Advanced Properties' property of the SLX OLEDB connection manager.

Thanks Phil - took me a while to sort the package protection out but I finally got there. I believe my issue is related to trying to run this on a 64 bit server after reading a few MS articles (http://msdn.microsoft.com/en-us/library/ms141766.aspx)

What I ended up doing is installing the DTS support package which is in the SQL 2005 feature pack and will run them as DTS packages in the agent until SLX supports 64 bit (hopefully by the time we move to SQL 2008!)

You do have the option of running the packages in 32-bit mode on your 64-bit server. Post back if you are interested and I will provide details.

Phil

I thought I could too but the 32 bit runtime checkbox is not available in the step setup on my install

Just to check, have a look in the file system for

C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe

(SSIS 2008 - similar path, I imagine, in 2005)

This is the 32-bit version of the executable which runs SSIS packages.

If it's there, check here for details and syntax.

Test reply (sorry )

It's there but I don't think the /x86 option is available in SQL 2005 - standard edition at least anyway. The 32 bit runtime check box is not there and if I add the parameter manually to the command line it fails with a "parameter not valid" error

Thx

L

Here's something I copied from technet that may be of interest - note the ungainly way that you specifically have to specify a command line with the path to the 32-bit dtexec:

Running Integration Services Packages on 64-bit Computers

Sometimes you may want to run packages in 32-bit mode in order to run uncompiled scripts, run SQL Server 2000 DTS packages, or use a managed .NET Framework Data Provider or native OLE DB provider that is not available in a 64-bit version.

Selecting 32-bit or 64-bit Package Execution in SSIS Designer

In the Project Properties of an Integration Services package, you can select 32-bit or 64-bit execution by setting the value of the Run64BitRuntime property on the Debugging page. By default, the value of this property is True. When the 64-bit version of the Integration Services runtime is not installed, this setting is ignored.
Note: The Run64BitRuntime project property applies only at design time.

Selecting 32-bit or 64-bit Package Execution in a SQL Server Agent Job

When you configure a SQL Server Agent job with a step of type SQL Server Integration Services Package, the job always runs the package in 64-bit mode on a 64-bit computer, because this step type invokes the 64-bit version of dtexec.exe to run the package. This assumes that you have installed and are running the 64-bit version of SQL Server 2005 and SQL Server Agent on the 64-bit computer. If you have installed and are running the 32-bit version of SQL Server 2005 and SQL Server Agent on the 64-bit computer, this step type invokes the 32-bit version of dtexec.exe to run the package.

If you want to run a package from a 64-bit SQL Server Agent job in 32-bit mode, select a job step type of Operating system, and enter a command line or use a batch file that invokes the 32-bit version of dtexec.exe. You can use the dtexecui.exe utility to create the command line, and then copy and paste the command line into the job step.

This definitely looks like the key - thanks for all your help Phil. Wonder if Sage has any plans for moving to 64 bit anytime soon. 

At least my SSIS knowledge has increased a hell of lot this week!

Cheers - Leon

My pleasure Leon - post back if you have any other questions and I'll do my best. Gives me something to do while waiting for SLX Web deployments