Dts Packages Using Dts Designer and Wizards

EOL: DTSxChange reached its end of life date on October 1, 2021. See the Solarwinds End of Life Policy for more information.

Overview

DTS xChange is an enterprise-ready tool that converts DTS packages to SSIS 2005/2008 with little manual effort. DTS xChange automatically converts most of your tasks, variables, connections, and any other package level settings. DTS xChange offers a three phase approach to convert your DTS packages:

Phase	Description
Profile	DTS xChange Profiler helps you estimate your migration project in hours and dollar costs whether you choose to use an automation tool or not.
Convert	DTS xChange migrates your packages with minimal efforts, applying rules to each DTS package as it migrates them to enforce best practices.
Monitor	SSIS Report Viewer is a powerful and easy to use Auditing Tool to Audit Packages that you migrated (with Auditing Framework) using DTS xChange. With SSIS Report Viewer you can do real time package execution analysis in detail or trend view. SSIS Report Viewer contains a series of reports to view errors, warnings, extracted/loaded record count, run time info for package and tasks, Machine Name, User Name, and many more.

Note: DTS xChange can only apply auditing framework during the conversion process. To re-apply auditing framework to converted SSIS packages or newly created SSIS packages you have to use SentryOne BI xPress. For more information about SentryOne BI xPress, see the BI xPress product page.

DTS xChange Features

The following features are available in DTS xChange:

• Convert hundreds of DTS packages to SQL Server Integration Services (SQL Server 2005 or 2008) in moments
• Validate packages after execution to ensure that they will work upon execution
• Deploy packages automatically to SQL Server 2005 or 2008
• Handle nearly all tasks except for MSMQ and Data Driven Query Tasks
• Apply a series of rules on DTS packages:
  • Logging to text or SQL files
  • Enable checkpoints
  • Enable transactions
  • Logging through event handlers into a robust event table
  • Migrate children packages automatically
  • Consolidate duplicate connections
  • Create configuration files automatically
  • Create sequence containers from parallel tasks
• Profile packages for cost (man hours and hard cost) of migrations
• Convert SQL Native Client connections
• Can handle ODBC connections as a source
• Convert Complex Datapump and  Dynamic Properties Task
• Convert UDL files to connection managers
• ActiveX Script Tasks are migrated to ActiveX Script Tasks in SSIS. Use the Profiler to determine quickly which objects are created inside the tasks and determine action items to migrate the task.

Comparison to the MS DTS Migration Wizard

The following table compares the built in wizard to DTS xChange:

Feature	DTS xChange Support Compatibility	DTS Migration Wizard Support Compatibility
Conversion of Execute SQL Task	Compatible	Compatible
Conversion of Execute Process Task	Compatible	Compatible
Conversion of Data Pump Task	Compatible	Compatible sometimes
Conversion of Dynamic Properties Task	Compatible	Incompatible
ODBC Support	Compatible as a source	Incompatible
UDL File Support	Compatible	Incompatible
Password protected Access Database	Compatible	Incompatible
Flat File that doesn't map all columns	Compatible	Incompatible
Data conversion between source and destination	Compatible	Incompatible
SQL Native Client support	Compatible	Incompatible
Full package validation after migration	Compatible	Incompatible
Detailed logs of conversion	Compatible	Incompatible
Enterprise rules for migration to get the benefit of SSIS	Compatible	Incompatible
SSIS logging turned on	Compatible	Incompatible
Checkpoint file support	Compatible	Incompatible
Children package migration	Compatible	Incompatible
Profiling capability	Compatible	Incompatible

Release Notes

A PDF copy of the DTS xChange release notes may be downloaded here

System Requirements

Your machine must meet the following requirements before installing DTS xChange:

• SQL Server Integration Services 2005 or 2008
• BIDS 2005 or 2008 (Business Intelligence Development Studio)
• Windows XP, Vista, Windows 7, Windows 2000/2003/2008
• Backward Compatibility Components

Note: Backward Compatibility Components are not installed by default during SQL Server 2008 installation. Make sure you select to install Backward Compatibility Components during the setup. You can select advanced options and then select Backward Compatibility components.

1. Check if BIDS (Business Intelligence Development Studio) install

Verify that Business Intelligence Development Studio (BIDS) is installed onto your machine. Select Start> All Programs > Microsoft SQL Server 2005 or Microsoft SQL Server 2008. The BIDS icon displays if it's installed.

2. Check if SSIS Components Installed

Verify that SSIS Components are installed onto your machine. Complete the following steps:

1. Select Start> All Programs > Microsoft SQL Server 2005 or Microsoft SQL Server 2008 >SQL Server Business Intelligence Development Studio.
2. Select File> New Project.
3. Select Business Intelligence Projects in the Project types pane, and then make sure you can see Integration Services Project in the Templates pane.

3. Check if Backward Compatibility Components installed

Verify the Backward Compatibility Components are installed onto your machine. Complete the following steps:

1. Select Start> All Programs > Microsoft SQL Server 2005 or Microsoft SQL Server 2008 > SQL Server Management Studio. Connect to any SQL 2005 Instance.
2. Select and expand Management> Legacy> Data Transformation Services.
3. Right click the node and select Import Package. Browse the existing *.dts file and select Okto add the file. Note: Only DTS Structured Storage Files can be imported.
4. Once the Package displays under the tree node, right click the package and select Open in DTS Designer to see if you can view the package.

Note: If the Backward Compatibility Components check failed and you are getting a components missing error then please run the SQL Server 2005/2008 setup and install Backward Compatibility components or download from the following URLs (For 32 bit OS use x86 link).

Microsoft SQL Server 2005 Backward Compatibility Components The SQL Server Backward Compatibility package includes the latest versions of Data Transformation Services 2000 runtime (DTS), SQL Distributed Management Objects (SQL-DMO), Decision Support Objects (DSO), and SQL Virtual Device Interface (SQLVDI). These versions have been updated for compatibility with both SQL Server 2005 and SQL Server 2008 and include all fixes shipped through SQL Server 2000 SP4 and SQL Server 2005 SP2.

Licensing

How is DTS xChange licensed and how does it keep track of migrations?

DTS xChange is licensed per package which means you have to purchase a license based on number of packages you want to migrate. You can use DTS Profiler to determine how many packages you have and how long they may take to migrate manually vs using DTS xChange.

As you migrate packages using DTS xChange the "Total Available Migrations" counter reduces on your license.

You can migrate the same package (with the same internal Package ID) multiple times. This will not reduce your remaining migration count.

Your counter decreases by one when you migrate a new package using DTS xChange. DTS xChange keeps track of package migrations based on the package's internal identifier. This ensures that renaming a package will not count your package twice because the internal identifier remains same.

How to add new migrations (i.e. apply new serial number to get more migrations)

If your migration limit is exceeded and you can not migrate new packages, you have to purchase a new serial number to allow additional migrations. The first three letters or number of your serial number determine how many migrations are allowed. If your serial number starts with DEV, it gives you 50 Migrations. If your serial number has a first three Numeric prefix, the number indicates the migration count (for example 080-XXXXX-XXXX-XXXXX gives you 80 migrations).

Add more migrations by deactivating your current license or by installing DTS xChange on a new machine and applying a new serial number. To de-activate the current license, complete the following steps:

1. Open DTS xChange and select the Registration Information link at the top.
2. Select Deactivate Software on the registration dialog box. Note: Once product is de-activated you will be forced to close the application.
3. Open DTS xChange again. Note: DTS xChange will launch as Trial version.
4. Select Register, and enter your name, company, and serial number. You can select Activate Online if you are connected to internet.
5. Select Registerto complete the registration.

Once the new serial number is applied, check your migration counter on the first screen to see the new migrations added to your license. When you apply a new serial number you can still migrate old packages migrated with all previous serial numbers.

How to transfer license to a different machine

Licensing in DTS xChange is very flexible. Transferring your license requires you to deactivate DTS xChange from your current machine and activate it on the new machine with the same serial number. Transfer your license to another machine by completing the following steps:

1. Open DTS xChange and select the Registration Information link at the top.
2. Select Deactivate Software on the registration dialog box. Note: Once product is deactivated you will be forced to close the application.
3. Install DTS xChange on the new machine where you want to transfer the license. Open DTS xChange after the installation completes.
4. Select Register, and enter your name, company, and serial number. You can select Activate Online if you are connected to internet.
5. Select Registerto complete the registration.

Should I install DTS xChange on single machine or multiple machines?

You can install DTS xChange on single machine or multiple machines depending on the number of users that need access to DTS xChange. Please review the following figures on both scenarios. If you are installing DTS xChange on multiple machines make sure you split your total package migrations in multiple Serial Numbers instead of using one Serial number for all packages.

Important: Once serial number is issued it can not be cancelled. You can always ask customer service before your actual purchase. There is no additional charge to split your serial number but it has to be requested before your purchase.

How to activate the product

Activate DTS xChange online by completing the following steps:

1. Download and Install the trial version of DTSxChange from the SentryOne DTS xChange product page.
2. Open DTSxChange by selecting Start> Programs> DTSxChange.
3. SelectRegisteron the trial screen and enter your name, company and serial number. Note: Generally you get the serial number in the email upon purchase. Select Activate automatically when I am online to speed up registration process.
4. Select Registerto complete the registration process.

The DTS xChange screen displays if your registration is successful. If your registration is not successful,  you are prompted with an error message or the option to activate in Offline mode.

Note: Firewall/Proxy issues can stop you from registering your product automatically.

If you can't register the product by selecting Register, then you must complete the following Offline activation steps:

Select Generate Email to send a message to SentryOne Support, or select Copy to Clipboard to copy your data and then forward that information to SentryOne Support via support.sentryone.com. The copied information includes the machine Hash Code and the Serial number you are trying to activate. Once SentryOne Support receives an email, they will contact you about an offline activation code.

1. Open DTS xChange and select Register.
2. Complete the registration information.
3. Select Activate Manually by entering Activation code. Enter the serial number and activation code provided to you by SentryOne.

How to extend the trial period

Extending the DTS xChange trial is two step process:

• Step-1 : Send the Limit Code to SentryOne support via support.sentryone.com to receive a valid extension.
• Step-2 : Apply the extension code you receive through email to your trial version.

To extend your trial period, complete the following steps:

1. Open the DTS xChange Trial version and select Extendon the bottom left of the screen.
2. Email the Limit Code to SentryOne Support. Note: Do not close Trial Dialog box until you receive extension code because Limit code expires when you close the application. SentryOne Support will email you the extension code to extend your trial period.
3. Enter the extension code in the Extension Code field, and then select Extendto extend your trial.

Planning DTS to SSIS Migration

DTS to SSIS Migration can be time consuming if you don't spend the time needed to create a proper environment for Migration. Review the following check list items to get the best migration output, reduce troubleshooting time, and improve ROI.

Use DTS Package Profiler to plan your migration project

DTS to SSIS migration can be challenging. Perform all of the necessary planning before you start your actual conversion. You can use DTS Package Profiler to find out how many packages you have and which packages are duplicates. You can also Define priority to each package and assign developers who will be responsible for package migration. Get started with your migration project by completing the following steps:

1. Profile all servers with DTS packages to get a package count. Export the profiler report to PDF or Excel.
2. Find out an accurate count of packages you need to migrate and how many individuals will be working on the project .
3. Purchase the necessary license(s) based on number of packages and number of individuals.

Note:

• Select Copy to Clipboard on the package listing screen during package profile to get a list of packages listed on the screen and paste them into excel.
• Use filter functionality on the select package screen to quickly find packages with certain prefix or sub string.  Select a column header to sort by name or selection.
• Export to PDF if you are getting an error while exporting to excel. Errors may occur if your report exceeds 65000 lines (This is known limitation in Excel).
• Export to Excel if you want to retain Expand/Collapse functionality of the report.

Check security rights

1. Make sure you have administrative privileges on the machine where you installed DTS xChange to avoid any possible permission issues during conversion.
2. If you are migrating packages stored on SQL Server, make sure the SQL login you are using to connect to DTS Server has at least read-only access to MSDB database. If you can see all packages in the list you can successfully access packages.
3. If you have any encrypted packages you will be prompted for the package password during migration.

Check data source connectivity

Most of DTS packages deal with one or more data sources to extract/load data or to execute DDL/DML statements. DTS xChange makes some smart adjustments in SSIS packages based on metadata (such as column name, size, precision etc.) provided by datasource. If the metadata is not validated due to connectivity issues during migration, then DTS xChange uses offline settings stored in DTS packages. This can cause unexpected behavior sometimes such as invalid data type is assigned to columns.

Get the best output with DTS xChange by checking the following items:

• You can access all source and target systems from the machine where you have installed DTS xChange.
• You have access to all network shares, flat files, UDL files and any other files used by DTS packages.
• If have any DTS package(s) using connections with windows authentication make sure you can access that data source under your login.

Check drivers and ODBC DSN

If you have DTS packages using multiple data sources other than SQL server, you need to check the following items:

• Ensure all ODBC or OLEDB drivers used by DTS packages are properly installed on the same machine running DTS xChange.
• If you are using the ODBC driver you have to configure the exact same DSN used by DTS package.
• Some data providers are not supported in SSIS. Make sure you install alternate drivers recommended by DTS xChange. Recommendations are made during Package migration so you will not be able to find which driver is best suited for you until you migrate the package.

Standardize naming convention and folder structure

SSIS Packages can have many dependencies. Most common dependencies are Configuration files, Log files, and Checkpoint Files. It's important that you try to keep same folder structure and path if possible across the same environment because all paths for listed dependencies are hard coded in the Package. Moving a package from one environment to another without the same path will cause the package to fail to execute.

Sometimes in large organizations it's not possible to keep the same path in different environments because of hardware and security constraints. For example, on a Development machine you may store under C:\SSIS but on the Production machine everything must go under M:\SSIS. In this scenario, make sure to change Paths of any dependency file referred inside your SSIS package when you deploy packages.

Running Packages on 64 Bit Machine

Things to remember when you working in a 64bit environment

Consider the following when working in a 64bit environment:

• There is no 64 bit Jet driver, meaning that there is no Excel or Access support on 64bit operating systems. You may get the following error: The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered. To resolve this issue, run the SSIS package using the 32bit version of DTSExec.
• There is no JIT for VSA scripts on 64 bit. Scripts must be compiled when developing the package (through the 32-bit designer tools). The PreCompile should be set to true, which is the default. The script is then compiled when the package is saved.
• You cannot remotely debug on IA64. There is no Visual Studio support for Itanium (IA64) and no BIDS. Packages need to be developed on another machine, but can then be deployed to and executed on IA64 machines. There is full support for BIDS on x64 platforms.
• When designing packages on a 64-bit machine, the default behavior is to run in 64-bit mode. You may want to change this so that you can access 32-bit only resources such as Jet. Within the Project Properties, select the Debugging page, and change the Run64BitRuntime property to false.

• The default t DTExec that's used is the 64-bit version, but if you have elected to install the 32-bit runtime as well, you can use the 32-bit version of DTExec. This allows you to access 32-bit only resources such as Jet again. See C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe
• On a 64-bit version of SQL Server 2005, if you want to schedule a package to execute a package under 32-bit mode, you have to use the Operation System (CmdExec) job step type. The SSIS Package Execution step type always uses the 64-bit runtime, but by using an Operating system step you can explicitly specify that the 32-bit version of DTExec should be used. As above the 32-bit version can be found in C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe.
• Executing packages through SQL Server Management Studio (SSMS) on x64 or I64 machines uses a 32-bit execution host. Executing packages from within the object explorer may not be ideal.
• DTExecUI.exe, the nice GUI sibling of DTExec is 32-bit only. To ensure you run as 64-bit just use DTExec.exe.

Potential problems in a 64-bit setup

Potential Problem	Description
32-bit tools not installed	The 32-bit runtime and tools are not automatically included when you install 64-bit Integration Services. If you have packages that need to run in 32-bit mode, then you have to select an additional option on the Feature Selection page of Setup. On x64, this can be BIDS or Management Tools - Complete. You must select Management Tools- Complete on itanium because BIDS is not available on Itanium.
32-bit tools run by default	When the 32-bit tools are also installed, the path to the 32-bit tools appears BEFORE the path to the 64-bit tools in the PATH environment variable. Therefore, if you just type dtexec at the command prompt, for example, you are running the 32-bit version of the tool. Your options are to type the full 64-bit path, make the 64-bit directory the current directory first, or change the order in the PATH environment variable. Our testing has not shown any negative side-effects to changing the order of the PATH, but I make this suggestion cautiously.

Features that don't work on any 64-bit operating system (x64 or Itanium)

Incompatible Feature	Description
DTS	You cannot run DTS packages in 64-bit mode on x64, or at all on Itanium. You also cannot use the Execute DTS 2000 Package task in these circumstances. Of course on x64 you can run DTS packages, or SSIS packages that run DTS packages, in 32-bit mode, after manually installing the optional DTS run-time support.
Excel, Access, Jet	You have to run packages that use the Jet provider (Access and Excel) in 32-bit mode.
SQL Server Compact	You have to run packages that use the SQL Server Compact provider in 32-bit mode.
Logging to SQL Server Profiler	You can only use package logging to SQL Server Profiler in 32-bit mode.

Dts Packages Using Dts Designer and Wizards

Source: https://docs.sentryone.com/help/dts-xchange-getting-started

Share this post