Tuesday, December 9, 2014

Migrating SQL Server 2005 DTSX Packages to SQL Server 2012

Migrating SQL Server 2005 DTSX Packages to SQL Server 2012

Login into the SSMS of sql server 2005 Expand the Management à Legacy à Data Transfarmation Services tab in object explorer shown as below pic it displayes all the dtsx packages located in source server i.e. SQL Server 2005




Right click on Data Transfarmation Services and select Migration Wizard

In Package Migration Wizard – Choose Source Location Screen the source as Microsoft SQL Server and give the server name and select the authentication mode(either windows authentication or SQL Server authentication) .  And click on Next button


Next  screen In Package Migration Wizard – Choose Destination Location, select the destination as DTSX File specify the Folder name where you saving  these packages. And click on Next


Next  screen In Package Migration Wizard – List Packages, here select the DTSX Packages to you want to migrate. And click Next


Next  screen In Package Migration Wizard – Specify a Log file, give the path and log file name, it will log all migration information. And click Next

Next  screen In Package Migration Wizard – Complete the Wizard, This will provide the summary of migration. Finally click on Finish.

Next  screen In Package Migration Wizard – Migration the Packages, Last screen indication what are the packages migration any errors/warnings given information.
And you can find the DTSX files which are selected to migrate in Destination folder shown in  above image. And copy the DTSX package files into destination server i.e SQL Server 2012


Login into the Destination server i.e. SQL Server 2012  . select Project Conversion Wizard from program files    

It will start Integration Services Project Conversion Wizard click Next on initial screen
In Locate Package screen select the Source as File System and give files location copied from SQL Server 2005 in Folder.  And click on Next


It will display the list of all available DTSX packages in the folder. Select the packages which you want to migrate. Shown as in below screen. And Click on Next


It will ask the Path and Project Name to store upgraded packages as ispac (Integration Services Project Deployment File)  format. Click Next till Review Screen.


Click on Convert button in Review & click on close button in success screen, it creates DTSUpgrate2005.ispac in above mentioned output path.



Select the Deployment Wizard in Integration Services

In Integration Deployment Wizard – Select Source Screen Select Project Deployment file radio button and in path section give the .ispac file location to deploy all dtsx package files

In Integration Deployment Wizard – Select Destination screen Enter the destination server name in Server name and select the path by clicking Browse button.


Click next and click Deploy in Review screen.


It will create dtsx files in Integration Services Catelogs à SSISDB à your prject name folder




Note  :If any DTSX package file will have any deprecated task or files used while converting you will get the errors.

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists

If you received below error while attaching a .mdf file in cluster environment please follow below steps to resolve the issue ERROR Ca...