Time to Upgrade…

Well, we’re back and it’s time to perform the actual DTS to SSIS migration. Before we get started let’s review a few key points of interest. First, the migration process from DTS to SSIS is the same regardless of which version of SSIS (2005 or 2008) you are migrating too. In the context of these blog posts we are discussing SSIS 2008, just keep in mind that these are the same processes to migrate to SSIS 2005 as well. Second, we need to review the available DTS to SSIS migration strategies as mentioned in my first post, The Key SQL Server 2008 BI Migration: DTS to SSIS 2008, Part 1 of 2:

·         Proactive (resolving DTS to SSIS issues prior to upgrade)

·         Reactive w/Package Migration Wizard

·         Reactive w/o Package Migration Wizard

There are various business and technical requirements that will assist you in this decision such as:

·         Can you still work in/edit the DTS Packages?

·         Can the majority of the DTS to SSIS issues be resolved in DTS or must you wait until the package is in SSIS format? (some DTS items do not have direct SSIS equivalents)

·         What is the average effectiveness of the Package Migration Wizard for your packages?

The DTS to SSIS Migration Process

At this point you have been given the ‘green light’ to perform the actual migration and you have selected a migration strategy. So now what? Well, first off don’t do anything on production! If you have not yet performed a DTS Package scan with SQL Server Upgrade Advisor (SSUA) for the effort estimation, that will be the first task to perform. Again, we want to minimize the load on the production server(s) as they still have to do their daily ‘jobs’. To perform a SSUA scan of your DTS Packages perform the following steps:

1.       Export the DTS Packages to the Structured Storage File Format.

a.       In SQL Server 2000 Enterprise Manager, open each package and do a Save As command to the Structured Storage File Format.

*If the production server has already been upgraded to SQL Server 2005 and is still running the DTS Packages you will need the SQL Server 2000 DTS Designer Components to save the package to a storage file.

*You can save each package to a new file or consolidate all of them into a single file. The SSUA facilitates scanning all files in a supplied directory so either method will work with the SSUA.

2.        Copy or Move the files to a SQL Server 2008 development server.

3.        Scan the DTS Packages w/SSUA 2008

*The SSUA requires the detection of the DTS runtime which gets installed as part of the SQL Server 2005|2008 backwards compatibility files prior to allowing you to scan individual .dts (structured storage) files.

4.        Note the SSUA scan results

With your SSUA scan results ‘in hand’ let’s move on. You should already have the DTS packages on a development server. At this point you should either use your current development server or another server to create a true test environment. By test environment I mean a SQL Server 2008 environment that mimics production. So items like files, directories, databases (with a sampling of data) etc are on the development server, this will facilitate the actual testing of your SSIS 2008 packages after being converted!

At this point our development environment has been built using SQL Server 2008. It is now time to migrate the packages in the development environment by leveraging your chosen migration strategy detailed above. Once you have your SSIS (.dtsx) equivalents of the DTS packages you need to perform testing of each individual SSIS package. Make sure they execute as expected producing the correct results. Once you are confident that the new SSIS packages are working as expected you should:

·         Enable SSIS Package Configurations for all external resources

·         Create a Package Deployment Utility

You are now ready to deploy, test, and ensure that the new SSIS packages run as expected in your production environment! Definitely coordinate the following steps with the server’s DBA. Depending upon the overall SQL Server 2008 upgrade strategy the DBA choose (in-place or side-by-side) you may or may not have both instances on the current production machine. If the production servers have not yet been upgraded then you must wait until they are upgraded to SQL Server 2008. Once the production server(s) are upgraded, deploy the packages with the previously created Package Deployment utility to the intended servers. Update any package configuration settings needed and test each individual package during periods of low activity on the production server (if possible). Once you have confirmed the new SSIS Packages work as expected your DTS to SSIS 2008 migration is complete!

Migrating from SSIS 2005 to 2008

If you are already running on SQL Server 2005’s Integration Services platform and you wish to get the new benefits of Integration Services 2008 you will still need to perform a migration (or upgrade), however it’s a relatively simple one. As of the time of this writing (SQL Server 2008 CTP5), SSIS 2008 does support being installed side-by-side with SSIS 2005. SSIS 2008 is for the most part a pure enhancement of the platform. I say for the most part because there are a few ‘gotchas’ (breaking changes) between the two builds:

·         SSIS 2008 uses Visual Studio 2005 Tools for Applications (VSTA) for its scripting environment

·         SSIS 2008 uses a newer build of the SQL Server Native Client

·         SSIS 2008 contains an updated OLEDB Provider for Analysis Services (10.0)

·         SSIS 2005 custom developed components will need to be altered to work in SSIS 2008

The script environment differences are the biggest change between the two builds of SSIS. When you open/add a SSIS 2005 package which contains a Script Task in SQL Server 2008 BIDS it will automatically attempt to convert the script for you. You can also attempt to execute the older SSIS 2005 package with the 2008 dtexec utility, this operation will also convert the script; however the script conversion is only temporary for the currently running instance of the package.

Removing the old DTS Packages & SQL Server Agent Jobs from Production

This is the step that is the easiest to forget about! You have now successfully migrated your older DTS packages to the SSIS 2008 format, we are all done right? No, that’s not right, we still have the original DTS packages running on our production server. To remove the older packages from a production environment perform the following steps (again make sure the DBA is aware of your actions):

·         Stop all corresponding SQL Agent jobs

·         Open each package and do a Save As command to export the production version to the structured storage file format (yes, I know we already did this, but you never know if any of them changed or not, good practice)

·         Backup, check in, etc the storage files just in case we ever need to refer to them again!

*Assuming the same schedules apply to the new packages you may want to just update each SQL Server Agent Job with the corresponding SSIS Package step replacing the older DTSRun steps. If you need to recreate the jobs too on a different server consider scripting out all of the jobs and then editing/recreating them in bulk with the updated job steps in the scripts.

Summary

In this post we have explored the various DTS to SSIS migration solutions and best practices processes. Once the migration is approved based upon the business and technical factors (see my prior post The Key SQL Server 2008 BI Migration: DTS to SSIS 2008, Part 1 of 2) you need to perform the actual migration. The DTS to SSIS migration summarized consists of:

1.       Choosing a DTS to SSIS Migration Strategy (Reactive/Proactive)

2.       Capturing SSUA DTS Package Alerts (all categories of notifications)

3.       Building a dev/test environment

4.       Migrating the packages using the selected DTS to SSIS Migration Strategy

5.       Testing/Correcting the resulting SSIS 2008 Packages in the dev/test environment

6.       Deploying and reconfirming the resulting SSIS 2008 Packages work in production as expected

7.       Removing the old DTS Packages from production w/optional SQL Server Agent Jobs

By using the prescriptive guidance found in both of my posts you will have a great head start on the DTS to SSIS migration process we all face as SQL Server customers!

Cheers

Derek Comingore