February 23, 2011 at 1:30 pm
I need to migrate roughly 100 SSIS packages (stored in MSDB) from a SQL 2008 machine to a new machine running 2008 R2. I have considered manually importing each package (yawn) as well as writing a sql script against the sysssispackages and sysssispackagefolders tables to copy the data from one machine to the other (seemingly dangerous). One new approach that I have just considered is running the SSIS packages upgrade wizard from the new machine. I realize that this was probably designed for inporting and upgrading 2005 packages, but am wondering if this might be a viable option.
After migration of all database objects, the machine names will be changed so the new machine will assume the name of the production server.
Could someone please advise me on the most efficient and reasonable approach to take. I would prefer not to do this manually if I can help it.
February 28, 2011 at 7:23 am
Anyone have input on this? I have seen a few similar threads with this particular scenario unanswered. Any advise is appreciated.
August 25, 2011 at 3:50 pm
A good option is to use DTUTIL. Since you are copying from SQL, I think it will be tricky to create a batch file to do this, so you may have to write a DTUTIL copy statement for each package. But your queries of the SSIS package tables should help you get a list of packages and quickly write the commands.
August 25, 2011 at 8:42 pm
This is exactly what I ended up doing. I wrote a batch file consisting of a couple hundred DTUtil statements to copy the packages. I also included code at the top of the file to check for the existence of the different package folders on the server and create them if needed.
August 26, 2011 at 9:27 am
I bulk imported them to new server before by using ssis to dump sysdtspackages in msdb. I did not remember exactly how, but it is way to go if tons of ssis packages needed to migrate.
September 19, 2011 at 12:03 pm
Run this on the source SQL Server as text output:
select 'DTUTIL /SQL "'+f.foldername+'"/"'+ name +'" /DestServer [YOURSQLSERVERDEST] /COPY SQL;"'+f.foldername+'"/"'+name+'" /QUIET' from msdb.dbo.sysssispackages p
inner join msdb.dbo.sysssispackagefolders f
on p.folderid = f.folderid
Then modify for additional folders (or omit the one added above) and run in command line on the source SQL Server.
September 19, 2011 at 5:28 pm
Restore MSDB on the New Machine..
Thank You,
Best Regards,
SQLBuddy
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply