Monday, August 2, 2010

Quick way to migrate DTS packages to SQL Server 2005

Migration is done using the SQL Server Package Migration Wizard.
This works to an extent - more complex packages will require some customizations and code changes.

Go to \Microsoft SQL Server\90\DTS\Binn to locate DTSMigrationWizard.exe. Alternatively, expand the DB server in Management Studio, expand management and then legacy in Object Explorer. Right click on DTS and select the wizard.
Choose source/destination server, and DTS packages. Then click Finish and wait for the wizard to import.

So now the packages are migrated, how do you know whether it works?

In SQL Server Management Studio, connect to Integration Services. (File->Connect Object Explorer).
Expand Stored Packages of the server instance. The migrated packages should be under MSDB.
Start performing test runs and pray for no errors to occur!

There's a great article that shows more details, here.

Part 2:
After successfully importing the DTS packages, we still need to set up the jobs. Script the jobs from SS2000 and just run the queries to create jobs in SS2005 master database.

Some stuff to take note:
Create a new database maintenance plan and let SSIS and SS2005 take care of the rest, as opposed to migrating the job and DTS over from SS2000 and spend time troubleshooting.

If the migrated job failed with an error like this:
Message

Executed as user: XXXXX\SYSTEM. Description: One or more component failed validation. End Error Error: 2010-08-04 01:00:43.37 Code: 0xC0024107 Source: DTSTask_DTSDataPumpTask_1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:00:00 AM Finished: 1:00:43 AM Elapsed: 42.75 seconds. The package execution failed. The step failed.
Try changing the DTS package's protection level as stated here.
For my case I just re-import the package from my local File System and used "Rely on Server Storage and Roles..."

EOS.

No comments:

Related Posts Plugin for WordPress, Blogger...