May 25, 2012 at 9:02 am
How do I copy our sql2005 msdb ssis packages to our sql2008 msdb.
Is there a script or method to do all of them all at once?
thanks.
May 25, 2012 at 9:11 am
Open them up in Visual Studio 2008, wait for them to be converted, then save them to msdb on your 2008 server.
John
May 25, 2012 at 12:29 pm
thank you for replying.
Do I have to open it up in VS 2008 and resave it to import into 2008?
Or can I just import into 2008 without doing this step?
and if so, really what I want is a script or method to do all 100 plus packages all at once.
Let me know, thanks.
May 28, 2012 at 1:38 am
Yes, open, convert, save.
There may well be a quicker method, but I'm not aware of it. The internet is a great resource for stuff like this, if you choose the right search term.
John
May 28, 2012 at 4:41 am
John Mitchell-245523 (5/28/2012)
Yes, open, convert, save.There may well be a quicker method, but I'm not aware of it. The internet is a great resource for stuff like this, if you choose the right search term.
John
I believe there is a migration wizard available for exporting multiple packages at once from one server to another. Migration wizard lets you select any/all the packages that you want to migrate.
June 4, 2012 at 4:03 pm
You can export them from SQL 2005 to disk using dtutil (2005 version) if having them as files as a middle-step makes more sense for your migration. You could then open them in VS2008 to upgrade them, and finally, use dtutil (2008 version) to deploy them to msdb in SQL 2008 as well.
Edit: just make sure you use the proper version of dtutil that corresponds to the server version
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 5, 2012 at 7:59 am
Can I just take them from sql 2005 msdb to 2008 msdb? I don't want to have to open every single one up and do anything...
June 5, 2012 at 8:57 am
I am not sure you can do that when storing packages in msdb, I would need to test it. However, if you are storing your packages as files and executing them using DTExec you may be able to get away without upgrading the packages. DTExec will attempt to upgrade the package on the fly, and if it succeeds it will execute the package. The recommendation is to open each one in BIDS 2008 so the format is upgraded, address any warnings or errors, save the package in the 2008 format, and then use it with the 2008 version of DTExec.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2012 at 12:18 pm
I haven't had to do this in a while, but you can restore your 2005 msdb onto your 2008 sql server overtop the one currently there. Once you do that, you can extract the stored procedures from a sql 2005 msdb and deploy them to your upgraded 2008 msdb database. This will allow you to run the SSIS packages without upgrading them to 2008. As you make changes to them in the future, they will get upgraded. I don't remember which 2005 stored proc you need, the main issue is the same procs are named slightly different in 2008. I am sure if you want the exact ones to extract you can find is somewhere online. I used this method when migrating a large warehouse to 2008 with a few hundred ssis packages.
June 6, 2012 at 12:25 pm
Chris Golla (6/6/2012)
but you can restore your 2005 msdb onto your 2008 sql server overtop the one currently there.
do not do this!
the msdb holds information relative to the sql server instance, migrate your packages in one of the previously mentioned manners
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 11, 2012 at 9:13 am
You can use DTS Backup 2000 http://www.sqldts.com/242.aspx to make this move. The DTS Backup 2000 product retains DTS package layout and annotations, with most other DTS export/import methods lose.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply