Migrating DTS Packages to SSIS 2016
By Tom Nonmacher
Migrating DTS packages to SSIS 2016 can be a daunting task, particularly for databases that have been heavily invested in SQL Server 2012 or SQL Server 2014. However, with careful planning and execution, the transition can be a smooth one. This blog aims to provide a step-by-step guide on how to successfully migrate DTS packages to SSIS 2016.
The first step in the migration process is to understand the structure and function of your current DTS packages. This involves examining the SQL Server 2012 or SQL Server 2014 database schemas and noting down the specifics of each DTS package. It's important to understand the source and destination of each data transformation, the data types involved, and any specific transformations applied during the process.
-- T-SQL code example illustrating a typical DTS package
EXEC master..sp_addlinkedserver @server = N'MyServer', @srvproduct=N'MySQL 5.6', @provider=N'MSDASQL', @datasrc=N'MySQL 5.6 ODBC Driver';
EXEC master..sp_addlinkedsrvlogin @rmtsrvname=N'MyServer',@useself=N'False',@locallogin=NULL,@rmtuser=N'myuser',@rmtpassword='mypassword';
SELECT * FROM MyServer...MyTable;
Once you have a clear understanding of your current DTS packages, the next step is to install and configure SSIS 2016. It's crucial to ensure that your new SSIS environment matches the requirements of your DTS packages. This includes checking that the correct versions of SQL Server and other dependencies are installed, and that all necessary permissions are correctly set up.
The next step is to create new SSIS packages that replicate the functionality of your old DTS packages. This is typically done using the SQL Server Data Tools (SSDT) that comes with SSIS. SSDT provides a graphical interface that allows you to design and test your new SSIS packages. The exact process of creating new SSIS packages will depend on the specifics of your DTS packages, but the basic steps are usually the same.
-- T-SQL code example illustrating a simple SSIS package
DECLARE @returncode int
EXEC @returncode = xp_cmdshell 'dtexec /f "C:\MyPackage.dtsx"'
IF @returncode <> 0
BEGIN
RAISERROR('Failed to execute SSIS package.', 16, 1)
END
After creating the new SSIS packages, the next step is to test them thoroughly. This involves running the packages in a test environment and comparing the results with those of your old DTS packages. It's important to ensure that the new SSIS packages are producing the correct results and that they are performing well in terms of speed and resource usage.
Once the new SSIS packages have been tested and validated, the final step is to deploy them to your production environment. This typically involves copying the package files to the target server, configuring the package execution environment, and setting up any necessary job schedules. Once the new SSIS packages are live, you can finally decommission your old DTS packages.
Migrating DTS packages to SSIS 2016 can be a complex process, but with careful planning and attention to detail, it can also be a rewarding one. The new SSIS platform offers many improvements over DTS, including better performance, more functionality, and a more modern and user-friendly interface. Happy migrating!