January 16, 2014 at 3:06 pm
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.
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?
January 16, 2014 at 3:23 pm
I would first create a new SSIS 2005 project and add some existing packages to it. Start small, don't add everything all at once.
Start with the easiest packages.
Then open your 2005 project with SSDT 2010 or 2012. The upgrade wizard will automatically start.
Take a back-up of everything and document any troubles you run into.
(I expect most of the troubles to be with configurations and script tasks)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 17, 2014 at 2:55 pm
Thanks I did that and
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.
January 19, 2014 at 5:49 am
The information messages are normal, that's just the native client version number being upgraded in the connection strings.
The errors mean you have a protection level with a password, set it to DontSaveSensitive.
The script component needs to be upgraded manually. Copy paste the code into a new scriopt component and change some of the code if necesssary.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 19, 2014 at 4:51 pm
I know it doesn't help with this migration but, right after you get it done, I'd strongly recommend that you insist on source control for anything and everything that's on the server(s).
For me, you just added more reasons to the list of reasons why I don't like SSIS. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 20, 2014 at 1:50 pm
Jeff Moden (1/19/2014)
For me, you just added more reasons to the list of reasons why I don't like SSIS. 😉
That statement is not justified at all.
Why don't you like SSIS here? Because it needs upgrading between versions?
I can imagine TSQL being rewritten when upgrading SQL Server 2000 to 2008.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 21, 2014 at 7:58 am
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.
January 21, 2014 at 8:18 am
I love SSIS, but I can see why others do not. The biggest complaints I hear are from folks that are not primarily in Business Intelligence....
January 23, 2014 at 8:40 pm
Koen Verbeeck (1/20/2014)
Jeff Moden (1/19/2014)
For me, you just added more reasons to the list of reasons why I don't like SSIS. 😉That statement is not justified at all.
Why don't you like SSIS here? Because it needs upgrading between versions?
I can imagine TSQL being rewritten when upgrading SQL Server 2000 to 2008.
You could be right. It sounds like the OP is saying that they have to migrate all of their packages and that could be a mistake on my part (could someone verify that, please?). In comparison, we only had to re-touch a small handful of stored procedures, etc, to migrate from 2000 to 2005. The same will hold true when we upgrade from 2005 to 2012. Sure, we'll re-touch some of the objects to take advantage of some of the 2012 features but we could get by without doing that.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2014 at 12:09 am
Jeff Moden (1/23/2014)
Koen Verbeeck (1/20/2014)
Jeff Moden (1/19/2014)
For me, you just added more reasons to the list of reasons why I don't like SSIS. 😉That statement is not justified at all.
Why don't you like SSIS here? Because it needs upgrading between versions?
I can imagine TSQL being rewritten when upgrading SQL Server 2000 to 2008.
You could be right. It sounds like the OP is saying that they have to migrate all of their packages and that could be a mistake on my part (could someone verify that, please?). In comparison, we only had to re-touch a small handful of stored procedures, etc, to migrate from 2000 to 2005. The same will hold true when we upgrade from 2005 to 2012. Sure, we'll re-touch some of the objects to take advantage of some of the 2012 features but we could get by without doing that.
When you moving to a new SSIS version, all packages need to be upgraded. They are Visual Studio objects so they have version numbers inside them and also the native client used in the OLE DB connections associated with that version needs to be upgraded. However, this is a pretty painless operation done by the wizard. The only real upgrading that you need to do is the .NET scripts (there were some changes in the code libraries) and deprecated pieces (such as DTS and ActiveX components).
SSIS 2012 has a new model of doing this, and you can choose to use this new model or stay with the old one. If you choose the old one, you have almost nothing to upgrade (except those items I mentioned earlier). If you choose the new model, you have more upgrading to do as some of the functionality changes.
You can compare this by rewriting old TSQL code to make use of the shiny new windowing functions 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 24, 2014 at 11:34 am
MS definitely did a decent job of giving you backwards compatibility. We did our migration in three rather painless phases.
1. Co-opt with the main RDBMS migration.....doing the bare minimum conversion of SQLNCLI to 11.......allowing you to maintain existing functionality in MSDB database and keep things seemless. This happened VERY quickly and did not hinder the rest of the "MAIN RDBMS MIGRATION". End users were happy, and life went on :-D.
POST RDBMS Migration Phases:
2. After 2012 migration, test package deployment vs project deployment model.
3. Deliver any NEW ETL functionality after deployment.
DBAs and developers were able to get up to speed on SSISDB between phase 1 and phase 2, and it was rather painless for us all. Step 2 was optional, but I thought it best for us to do it.
January 24, 2014 at 2:20 pm
sneumersky (1/24/2014)
MS definitely did a decent job of giving you backwards compatibility. We did our migration in three rather painless phases.1. Co-opt with the main RDBMS migration.....doing the bare minimum conversion of SQLNCLI to 11.......allowing you to maintain existing functionality in MSDB database and keep things seemless. This happened VERY quickly and did not hinder the rest of the "MAIN RDBMS MIGRATION". End users were happy, and life went on :-D.
POST RDBMS Migration Phases:
2. After 2012 migration, test package deployment vs project deployment model.
3. Deliver any NEW ETL functionality after deployment.
DBAs and developers were able to get up to speed on SSISDB between phase 1 and phase 2, and it was rather painless for us all. Step 2 was optional, but I thought it best for us to do it.
I did my T-SQL upgrade from 2000 to 2005 rather painlessly, as well. I restored the database and the code was done. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2014 at 2:48 pm
Haha...I THOUGHT you were going to say:
"The fact that you NEEDED 3 phases proves my point."
That's close enough 😉
January 24, 2014 at 3:19 pm
I was definitely thinking that. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply