question

SahaSaha-5270 avatar image
0 Votes"
SahaSaha-5270 asked ZoeHui-MSFT commented

SSIS package error in MSDB

Using SSIS package Upgrade Wizard to upgrade package and got the following error

Information 0x40019316: : The provider name for the connection manager "servername.X.sa" has been changed from "SQLNCLI10.1" to "SQLNCLI11".


Information 0x40016019: : The package format was migrated from version 3 to version 8. It must be saved to retain migration changes.

Error 0xc0016016: OGPFileLoad: 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.

Error 0xc001f429: Package Upgrade: The loading of the package X has failed.

Added sa password to the selection but could not upgrade the package.


sql-server-generalsql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered ZoeHui-MSFT commented

You should open that project in SSDT - and upgrade it there, addressing any required changes that are needed and then redeploy the package. If you don't have the SSDT project available - export the package to a dtsx file and create a new project.

Instead of using MSDB you should implement the SSISDB Integration Services Catalog. The features and functionality of the catalog are much easier to manage and deploy your packages/projects.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@JeffreyWilliams-3310 Do you have a link where it describes this process? I have never worked with package upgrade hence trying to learn the process.

0 Votes 0 ·

I don't have a link - sorry. But it isn't that hard:

1) Open SSDT (SQL Server Data Tools) - or Visual Studio with the data tools installed for Integration Services
2) Create a new Integration Services Project
3) Export the package to a dtsx file
4) Add the exported dtsx file to the project - using Add Existing Item
5) Make the necessary changes to the package/project to address to upgrade errors
6) Deploy the project/package

Based on your original post - it seems using the upgrade wizard from SSMS didn't work. Which is why you need to open the package in VS and upgrade it there - so you can address the issues the upgrade wizard was not able to fix.

0 Votes 0 ·

99157-pacage.jpg


Open the solution in visual Studio 2008 but cannot export the package to dtsx file. Any suggestions.

0 Votes 0 ·
pacage.jpg (26.4 KiB)
Show more comments
ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @SahaSaha-5270.

You could refer the official documentation for details.

upgrade-integration-services-packages-using-the-ssis-package-upgrade-wizard

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.