Hello Experts, I got 400 SSIS packages in SQL 2005 32bit on server A and now required to migrate those to SQL 2008R2 64 version on server B. Can someone post step by step process to make this success? Server A is on VM and server B is on physical. All packages are scatered and some stored in MSDB and some in file system. ServerA\f:\IMPORT\PACKAGES\individual package name and then .dtsx files. Also looking for changing the server name A to B or old server IP to New Server IP. While migrating packages I need to change the owner to SA so that would be the next step. Thanks for the Help...Manu ------- Please click the Mark as Answer if my post solves your issue.

OK i cant provide a step by step but you can do the mentioned 1- FILE BASE PACKAGES - you just need to move (COPY) the p[ackages to the final 64 bit box - assuming that you are calling your packages through a SQL job , you have to use the 32 bit DTExec.exe (yes i know you are in a 64bit inviroment) for packages that work with 32 bit sources (excel or Access 2000 are 2 good examples) -- yes although you are switching to a 64 bit inviroment , you package target is a 32 file (excel) so you have to use the 32 bit DTExec.exe file 2- For SSIS in BI in MSDB - you have to migrate the packages to the new server in the MSDN - to provide the list use SELECT * FROM msdb.dbo.sysdtspackagefolders90 -- to get the list of packages and folders use .... ------- List of SSIS package --SELECT tblOut.FolderName AS OuterFname -- , tblIn.FolderName AS InFname -- , * --FROM msdb.dbo.sysdtspackagefolders90 AS tblOut --INNER JOIN msdb.dbo.sysdtspackagefolders90 AS tblIn --ON tblIn.ParentFolderID = tblOut.FolderID SELECT * FROM msdb.dbo.sysdtspackagefolders90 AS tblOut Begin Tran SELECT FolderID , ParentFolderID, FolderName , CAST ('' AS nVarChar(Max)) AS 'SSISMainPath' INTO #tempTable FROM msdb.dbo.sysdtspackagefolders90 ---------------- DECLARE @FolderID uniqueidentifier DECLARE @ParentFolderID uniqueidentifier DECLARE @FolderName nVarChar(128) DECLARE @MainFullPath nVarChar(Max) DECLARE LoopCursor CURSOR FOR SELECT FolderID , ParentFolderID , FolderName FROM #tempTable OPEN LoopCursor FETCH NEXT FROM LoopCursor INTO @FolderID , @ParentFolderID ,@FolderName WHILE @@FETCH_STATUS = 0 BEGIN SET @MainFullPath = '' ---------------------------------------------------- ------ Looping the main table DECLARE @inFolderID uniqueidentifier DECLARE @inParentFolderID uniqueidentifier DECLARE @inFolderNameP nVarChar(128) SET @inFolderID = @ParentFolderID SET @inParentFolderID = @ParentFolderID SET @MainFullPath = @FolderName SET @inFolderNameP = @FolderName WHILE Len(ISNULL(@inFolderNameP, '' )) > 1 --OR @inParentFolderID <> '00000000-0000-0000-0000-000000000000' BEGIN SELECT @inFolderID = FolderID , @inParentFolderID = ParentFolderID , @inFolderNameP = FolderName FROM msdb.dbo.sysdtspackagefolders90 WHERE FolderID = @inFolderID --SELECT @inFolderName SET @MainFullPath = @inFolderNameP + Cast ('\' AS nVarChar(1)) + @MainFullPath SET @inFolderID = @inParentFolderID --= @inFolderID END --------------------------------------------------- UPDATE #tempTable SET SSISMainPath = @MainFullPath WHERE FolderID = @FolderID ; FETCH NEXT FROM LoopCursor INTO @FolderID , @ParentFolderID, @FolderName ; END ; CLOSE LoopCursor ; DEALLOCATE LoopCursor ; SELECT PF.SSISMainPath, P.* FROM msdb.dbo.sysdtspackages90 as P INNER JOIN #tempTable AS PF ON P.Folderid = PF.Folderid ; DROP Table #tempTable ; --------------- Rollback Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

1- File base packages 2- SQL base packages (MSDB) -- you must have the same folder and in the msdb as you had in the 32bit enviroment (i gave you the script) -- again some pacages will need to use a 32 bit DTExce.exe file see http://msdn.microsoft.com/en-us/library/ms162810.aspx you need to call the package by using the folder ...... drive>:\ Program Files(x86)\Microsoft SQL Server\100\DTS\Binn -- to move the packages from MSDB box 1 (32bit ) to MSDB box2 (64 bit ) use http://msdn.microsoft.com/en-us/library/ms162820.aspx a good example is EXEC xp_cmdshell 'dtutil /SOURCESERVER SQLServer32bitName /SQL "\Folder1inMSDB\PakagName" /DestSERVER SQLServer64bitName /COPY SQL;"\pakageName -- dont change the package names or any XML config file names 3-- CONFIGURATION - keep the same DB if you can -- dont change the name of the packages -- redirct the SQL jobs that are using the packages to use the right DTEXEC. SQL2008 has an extra option to point to 32 dtexec. -- if the SQL server name has changes you have to reconfig all the configuration tables and files (XML config) to point to the right SQL servername -- your SQL jobs may be loging Error logs make sure you have the right permission for the jobs to write the txt files (see advance option in SQL jobs) ***** MOST OF ALL - you need to test this one by one , you need to makes scripts and categoraze your work, it took me 5 days for that and i did the deployment in 2hrs for 200 packages -- do everything one step at a time -- good luck Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Check http://blogs.msdn.com/b/mattm/archive/2008/06/12/use-32bit-runtime-option-for-sql-agent.aspx for the extra option for SQL 2008 JobsSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Let me check couple of SSIS packages to test it first. I will update This thread later. Question, Currently packages are in 2 different places (MSDB and File System), Would I be able to migrate all packages into MSDB or to FileSystem? Manu ------- Please click the Mark as Answer if my post solves your issue.

keep them as they are, so if you have 1- 100 pacakge as file base in drive D:\SSIS make the same folder in the same drive on the second box and just copy all of the folders and subfolders OR can use 2- if you have 50 Pacakge in the MSDB in BI in SQL server in Box 1 (32bit), make the same folders and subfolders in MSDB ( i gave you the script and you use dtutil) in the second box why? because the more you change ( i.e. server name , db name, SMTP maybe you are emailing from packages, folder name etc...) the more you have to modify and change.Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).