Solved

migrate ssis from 2005 to 2012

Posted on 2014-01-16
5
2,973 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
  • 2
  • 2
5 Comments
 
LVL 17

Assisted Solution

by:Barry Cunney
Barry Cunney earned 500 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 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Select2 jquery help 9 46
Full Text Search string 5 34
Server 2008 Cluster Fail-over Errors 5 53
Join vs where 2 11
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now