September 4, 2023 at 10:14 am
Hi All,
is it possible to migrate SSIS packages from SQL 2005 to SQL 2022.
All of our SSIS packages are in file system .dtsx file.
I do not know the development team is having project file etc. If not, they will create new project file for SQL 2022.
September 5, 2023 at 11:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 5, 2023 at 4:32 pm
My advice would be to do more staged upgrade and migrate it over to an SSIS catalog. Like upgrade from 2005 to 2008 R2 to 2012 to 2014 to 2017 to 2019 to 2022 and when you get to 2012, change it over from the file system to an SSIS catalog.
Migrating directly across unsupported versions (2005 to 2022 directly) is VERY likely to cause issues.
The development team will also likely need to upgrade their Visual Studio to use newer SSDT and migrate it up to 2022, but that too isn't a direct thing either. It MAY be able to go straight from 2005 to 2022, but I expect you will have a lot of issues if you go directly that way. I imagine you'd need to use multiple visual studio versions and SSDT versions to do an upgrade to 2022.
Now, it MAY work to do a migration from 2005 to 2022, but I highly suspect you will have issues. What I would do either way is set up a test environment and do a test migration and see how things go. AND make sure to run ALL of the SSIS packages against test systems once migrated to 2022 so you can ensure that they still work. ONLY after I have tested things and am 100% confident that the upgrade didn't break anything, would I want to re-point things to prod and shut down the old SSIS instance.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 5, 2023 at 4:43 pm
Yikes.
I have had problems with packages migrating from 2008 to 2016. (development problems)
Definitely go through a stepped migration as suggested. I ran into lots of problems with the way SSIS interpreted long unicode strings - strings that were longer than 4000 characters that were not longer than 8000 bytes were assumed to be LOBs, more modern SQL would only consider them LOBS if they actually exceeded 8000 bytes. There were also some weird code page differences in how 2008 SSIS wrote text to a flat file that had to be experimented with.
September 6, 2023 at 3:52 am
Hi,
Thanks both for your response. It helps me a lot. We have test server built for testing everything for 4 weeks by running SSIS job etc.
I have tested database restore from SQL 2005 to SQL 2022, I can able to restore it.
For SSIS, I will speak with development team to migrate step by step. They have project file and currently going to test opening packages using VS 2005, since it was built by VS 2005. They're going to test migrating from SQL 2005 (Windows 2003) to SQL 2022 (windows 2022) if it fail ad having lot of issues then maybe staged migration or new design to the SQL 2022.
I believe we cannot use visual studio (VS) for SQL 2022, we need to use SSDT.
September 6, 2023 at 1:37 pm
I would not even approach it as a stepped migration - instead, I would evaluate the existing processes and rewrite them in a project deployment model.
In many cases, the original SSIS package does everything in a single package. For a project deployment that may not be the best option. I have often found that separate smaller packages and/or parent-child packages in a single project work much better and are easier to manage and maintain.
You also need to make sure all of that extra logging included is removed or updated to work with the catalog. Most of those packages included logging that isn't necessary when deployed to the catalog - and in many cases that logging just won't work as setup and configured.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 7, 2023 at 8:54 am
Thanks for the update Jeffrey. We will consider that as well.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply