Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

migrate ssis from 2005 to 2012

Posted on 2014-01-16
5
Medium Priority
?
3,271 Views
Last Modified: 2016-02-10
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?
0
Comment
Question by:cottage125
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 17

Assisted Solution

by:Barry Cunney
Barry Cunney earned 2000 total points
ID: 39788151
Hi cottage,
I would first try and get all packages into a single SSIS project.
You could try the following:

1. Create a new SSIS 2012 project using BIDS
2. In the filesystem, in the package directory, highlight all the packages and choose Copy
3. Come back to BIDS project again and select 'SSIS Packages' folder in Solution Explorer and choose Paste - this should paste all the packages into the SSIS project
4. Save, close and re-open the project
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39789159
As with so many things concerning SQL Server, "it depends" !

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
0
 

Author Comment

by:cottage125
ID: 39789346
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.
0
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 2000 total points
ID: 39794689
Hi Cottage,
We will try and address the 'Error 0xc0016016: test_package: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.....' issue first.
However first, please make sure that you have a good backup of all your original .dtsx files.

I think that this error is related to the 'ProtectionLevel' property on the individual packages.
Try changing  'ProtectionLevel' on test_package.dtsx to DontSaveSensitive
You will have to open the dtsx in BIDS 2005 to do this
Then add it back into the new project with all the other packages and try the same steps again.
Note if the SSIS Upgrade Wizard still gives any messages about  test_package.dtsx
0
 

Author Comment

by:cottage125
ID: 39797083
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.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question