SSIS Packages Build Deploy Install Execute

Question

SSIS Packages Build Deploy Install Execute

Koneru Murli Krishna

Mr or Mrs. 500

Points: 555
More actions
October 29, 2006 at 10:57 pm

#153264

1) When I first developed the packages, I was able to run build, deploy, install & execute them from command prompt.
2) From stored procedure I got error:
Error: 2006-10-24 14:58:54.62
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTSassword" 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
3) Experts suggested to use package protection as Encrypt with password but still I could not succeed.
4) Now, we recreated the only package from which we kept password as before but while validation during install phase bottom 3 errors from below point 5) are reported.
5) Now from command prompt, I get following errors:
Error: 2006-10-29 22:31:07.72
   Code: 0xC0016016
   Source:
   Description: Failed to decrypt protected XML node "DTSassword" 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: 2006-10-29 22:31:07.94
   Code: 0xC0202009
   Source: pkgDept Connection manager "ISYS0221\ISPL001.IDB.sa"
   Description: An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: An existing connection was forcibly closed by the remote host.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'sa'.".
End Error
Error: 2006-10-29 22:31:07.94
   Code: 0xC020801C
   Source: Data Flow Task tblVarianceTransactionTypeSource [1]
   Description: The AcquireConnection method call to the connection manager "ISYS0221\ISPL001.IDB.sa" failed with error code 0xC0202009.
End Error
Error: 2006-10-29 22:31:07.94
   Code: 0xC0047017
   Source: Data Flow Task DTS.Pipeline
   Description: component "tblDept" (1) failed validation and returned error code 0xC020801C.
End Error
6) All this while right click on individual packages in solution explorer and it works fine.
Please suggest ways to overcome these errors and successfully execute my packages.
November 1, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

kaddie abdul SSC Eights! Points: 866 More actions · Answer 1

Well, the first most obvious (although it's extremely annoying) question is whether you have fully updated SQL Server 2005 to include the latest version of SP1 and the latest Hotfix? If you have, then your @@version should return SQL Server 9.0.2153.

Even though, you were able to run your packages before, it's possible that you are triggering something that may have been fixed by the latest version of SP1 (I believe that it was released in April 2006 and a lot of people don't have it installed on their systems yet).

The second question is whether you have tried to execute the package from the database server using SSIS? It appears that you have executed it using BIDS, however I believe that the command prompt is going against the Integration Services service which means that it is using the version of the package that you deployed to that server and not the one that BIDS is using.

Go to your Management Studio. Select Connect, Integration Services, and then select the server on which you deployed the package.

Go to the Stored Packages folder, select the location to which you deployed the package (i.e., File System or MSDB), right-click on the package and select "Run Package".

This will bring up an execution window that contains all of the different properties that have been set for the package when it is run from that database. You can also modify those properties before executing the package and there's also an execution log that may provide you with more details to help you successfully troubleshoot your package.

I'm sorry that I couldn't be of more help, but maybe this will point you in the right direction.

catch22fan Grasshopper Points: 17 More actions · Answer 2

The message you are seeing is caused when you have compiled a package without supplying the package protection key. The key can be in several different forms but from the error you are getting it appears that it will "remove sensitive" data ... which is basically the password in connections for connections that use SQL authentication. I would suggest that you modify the dtsconfig file located in the C:\Program Files\Microsoft SQL Server\90\DTS\Packages directory where you deployed the package and add back in the passwords for your connections. It also helps to use Server Level security (you can't compile this way), you'll need to do this when you deploy the manifest on the server.