cottage125
asked on
migrate ssis from 2005 to 2012
Hello,
We are planning to migrate from 2005 to 2012 and we have so many ssis packages but we dont have the project files for those packages. Developers creates SSIS packages but during prod deployment we only ask them to give .dtsx file and we put that file on server in C drive and our sql agent jobs points to those packages. Thats how we use it for more than 100 packages so we dont have any project files.
1. I want to migrate all these packages all at once to 2012. Is this possible and recommended?
2. Here they says u have to open project file in SSDT and I dont have that file.
http://www.bidn.com/blogs/DustinRyan/bidn-blog/2610/upgrade-2005-2008-ssis-packages-to-2012-like-a-boss
Can I create project on 2005 and put all these packages under 1 project? And if I do it that way will it work or any issues?
And If I dont want to create project then what is the easiest method for me to upgrade packages?
We are planning to migrate from 2005 to 2012 and we have so many ssis packages but we dont have the project files for those packages. Developers creates SSIS packages but during prod deployment we only ask them to give .dtsx file and we put that file on server in C drive and our sql agent jobs points to those packages. Thats how we use it for more than 100 packages so we dont have any project files.
1. I want to migrate all these packages all at once to 2012. Is this possible and recommended?
2. Here they says u have to open project file in SSDT and I dont have that file.
http://www.bidn.com/blogs/DustinRyan/bidn-blog/2610/upgrade-2005-2008-ssis-packages-to-2012-like-a-boss
Can I create project on 2005 and put all these packages under 1 project? And if I do it that way will it work or any issues?
And If I dont want to create project then what is the easiest method for me to upgrade packages?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Barry Cunney. I did exactly what you said and that save a lot of time.
After that do I need to edit each package for connection manager??
I tried without editing that. Now I copied that whole project folder to my sql 2012 server where I am going to upgrade and on this 2012 server I dont have 2005 installed at all.
So as per my link above, Visual studio conversion wizard popped up followed by SSIS Package Upgrade Wizard and I selected those packages and tried to upgrade but it failed to upgrade.
Here is the message for one of the package.
- Upgrading package test_package.dtsx (Error)
Messages
Information 0x40019316: : The provider name for the connection manager "test_db" has been changed from "SQLNCLI.1" to "SQLNCLI10".
Information 0x40019316: : The provider name for the connection manager "student_db" has been changed from "SQLNCLI.1" to "SQLNCLI10".
Information 0x40019316: : The provider name for the connection manager "test_db" has been changed from "SQLNCLI10" to "SQLNCLI11".
Information 0x40019316: : The provider name for the connection manager "student_db" has been changed from "SQLNCLI10" to "SQLNCLI11".
Information 0x40016019: : The package format was migrated from version 2 to version 6. It must be saved to retain migration changes.
Error 0xc0016016: test_package: 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 0xc0016016: test_package: Failed to decrypt protected XML node "DTS:Property" 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 0xc0016016: test_package: 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.
Warning: Data Flow Task: Found SQL Server Integration Services 2005 Script Component Script Component that requires migration!
Information 0x4001601a: Data Flow Task: The Script Component has been migrated. The package must be saved to retain migration changes.
Error 0xc001f429: Package Upgrade: The loading of the package test_package.dtsx has failed.
After that do I need to edit each package for connection manager??
I tried without editing that. Now I copied that whole project folder to my sql 2012 server where I am going to upgrade and on this 2012 server I dont have 2005 installed at all.
So as per my link above, Visual studio conversion wizard popped up followed by SSIS Package Upgrade Wizard and I selected those packages and tried to upgrade but it failed to upgrade.
Here is the message for one of the package.
- Upgrading package test_package.dtsx (Error)
Messages
Information 0x40019316: : The provider name for the connection manager "test_db" has been changed from "SQLNCLI.1" to "SQLNCLI10".
Information 0x40019316: : The provider name for the connection manager "student_db" has been changed from "SQLNCLI.1" to "SQLNCLI10".
Information 0x40019316: : The provider name for the connection manager "test_db" has been changed from "SQLNCLI10" to "SQLNCLI11".
Information 0x40019316: : The provider name for the connection manager "student_db" has been changed from "SQLNCLI10" to "SQLNCLI11".
Information 0x40016019: : The package format was migrated from version 2 to version 6. It must be saved to retain migration changes.
Error 0xc0016016: test_package: 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 0xc0016016: test_package: Failed to decrypt protected XML node "DTS:Property" 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 0xc0016016: test_package: 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.
Warning: Data Flow Task: Found SQL Server Integration Services 2005 Script Component Script Component that requires migration!
Information 0x4001601a: Data Flow Task: The Script Component has been migrated. The package must be saved to retain migration changes.
Error 0xc001f429: Package Upgrade: The loading of the package test_package.dtsx has failed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot All. I ignored those errors and then ran the package/project deployment model and then modified connection strings and i hope that should do it. Cant run the package yet but i hope this will fix it.
In this case it depends on how you want to maintain these packages going forward. I have seen migrations of solutions comprising many tens of packages, and migrations involving a lot of solutions each with just a few packages.
My personal preference is for a lots of solutions, each with just a few packages - depending on the functionality of the solution. The reasons include the ability to have multiple developers working simultaneously, each one on a solution, in parallel. Having multiple people working on a single solution is possible, but can be confusing, not to mention dangerous, even with source code control. In addition, whenever you open a solution SSDT will verify each connection and each use of each connection, which can take a very long time!
My recommendation would be to get some copies of SQL Server 2012 Developer Edition (about $50 each) and assign groups of related packages to developers. Each solution, then, can be developed to correspond to a SQL Server Agent Job, which should ease your future maintenance work and reduce the risks associated with working on software unnecessarily.
Finally, you should also look forward to your move to SQL Server 2014 or 2016. Do you really want to be back here asking this question again? Spend some time now getting things in shape for an easy transition then by storing all the solutions, etc.!
hth
Mike