Solved

migrate ssis from 2005 to 2012

Posted on 2014-01-16
5
3,038 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

823 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