Using DTS xChange

About DTS xChange

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

DTS xChange Conversion Diagram

PhaseDescription
ProfileDTS xChange Profiler helps you estimate your migration project in hours and dollar costs whether you choose to use an automation tool or not.
ConvertDTS xChange migrates your packages with minimal efforts, applying rules to each DTS package as it migrates them to enforce best practices.
MonitorSSIS 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.

Option Screen

DTS xChange Welcome Screen options

The first screen that displays in DTS xChange is the Option Screen. The following options are available:

OptionDescription
MigrateOpens the  DTS package migration wizard  and allows you to convert DTS packages to SSIS 2005/2008. The Migrate option applies a series of best practices during migration, and displays a full migration log and validation report of the converted packages for possible errors/warnings at the end of the migration.

Note:  The Trial version allows you to convert a maximum of three packages. If you are converting DTS packages that call other package(s), the parent package and child package(s) are counted as separate migrations.
ProfileOpens the DTS Package Profiler Wizard. Profiler is helpful to find out how many packages you have and how complex they are to convert manually or using DTS xChange. DTS Profiler generates detailed reports with package and task level break downs that can be exported as a PDF or Excel file.
ReportsOpens the Report Viewer Application. Use the Report Viewer to view several predefined reports for SSIS package auditing.
LogDisplays the history of all the previous migrations that occurred on the machine where DTS xChange is currently installed.
Support/BugSelect this URL if you have any issue using DTS xChange. You can contact support by phone/email or by visiting https://www.sentryone.com/support.
Check for updateDTS xChange has an auto update feature that checks every couple of days or everytime you launch DTS xChange depending on your selection. A product update dialog displays when a product update is found.
RegistrationDisplays the license information for your product. Use the Registration screen to activate or deactivate your license.

DTS xChange Product Updates Screen

Specify Source and Target Screen

DTS xChange Specify Source and Target screen

The Specify Source/Target Location screen allows you to specify the location of DTS packages and the location for converted packages. The following options are available:

Specify where DTS package(s) are stored:

OptionDescription
Source SQL ServerSelect this option if your DTS packages are stored on SQL Server 2000 or SQL Server 2005 (under legacy mode). You can specify the instance name or IP. For example:
  • MYSERVER\SQLINST1
  • 192.168.2.3
  • (local)
  • MYSERVER\DEVINST1,1433
Source File SystemSelect this option if DTS packages are stored as COM Structured Storage files (*.dts). Browse the folder location where the dts files are stored, and select the packages found in the desired folder.

Specify where migrated package(s) are stored:

DTS xChange MSDB folder example

OptionDescription
Target SQL ServerSelect Target SQL Server if you want to store converted package on SQL Server 2005 under msdb database. Select Browse to browse a specific target folder of the msdb.
Target File SystemSelect Target File System if you want to save converted packages on file system (*.dtsx).
Target PlatformTarget Platform lists all of the installed versions of SQL Server that you can select as the target platform. If you don't see any items in this dropdown then make sure you install necessary components specified in the requirements section.
Overwrite OptionSelect one of the three different options that specifies an action when a file already exists on the target location.
64bit OptionSelect Yes if you are going to run SSIS packages on a 64bit machine. This option does not perform any additional actions during the migration process. Selecting Yes, or Don't know displays some warnings describing the known issues on a 64bit machine

Advanced Options

OptionDescription
Perform MetaData ValidationThis option is only for internal testing purposes. Do not deselect this option when you are converting packages that will used for production. Deselecting this option causes no metadata validation to be performed which gives you faster migrations, but may cause unexpected behaviors in some cases.
Log application logEnable/disable detailed logging of migration and application events. The log is stored in AppLog.txt found under <program files>\pragmatic works\DTS xChange.
Log migration historyGenerates a migration history file after each migration. Use the Log option found on the very first screen to view historical data of migration log.

Select Packages Screen

DTS xChange Select Packages screen

The Select Packages screen lists all of the packages for the selected source location. Select the first column to sort packages by selection status and select the second column to sort by package name.

OptionDescription
Display Associated Job DetailDisplays four additional columns related to associated job detail which includes the following: Job Name, last execution date, creation date, and last execution status.
Search forFilter the package list based on your search criteria (for example, to list all packages with word Test enter Test in the filter box and click filter icon). This option is useful when you have a lot of packages and you don't know exact name of the package.
Copy to clipboardSave the content of the top grid into the clipboard in Tab delimited values (you can copy/paste content to excel).
Save reportSave the content on the top grid into Tab delimited values (you can copy/paste content to excel).

Select Migration Options and Best Practices Screen

About Migration Options and Best Practices

DTS xChange adds value to your converted packages by applying a series of options and SSIS best practices. If you are new to SSIS and you aren't sure about the best options for you, leave the default selection. To set default values select Click here to set Default Settings.

DTS xChange Best Practices and Migration Settings

DTS xChange applies the following best practices:

Best PracticeDescription
Deployment frameworkThis feature automatically creates a reusable config file that stores the connection string. DTS xChange automatically creates a minimum number of unique configuration files and reuses them across multiple packages. This feature saves time in the deployment process (for example, from DEV to PROD).
Auditing framework (Custom Auditing Framework for extended logging)This powerful logging mechanism tracks the execution history of a package when the package is executed  manually or through a job. Auditing data includes the Start/End time of the Package, and individual Tasks, Errors and Warnings, Records Count in/out, Data source information (for example, filename, table name, database name) and many other useful performance matrix.
NULL HandlingThis feature helps you to avoid any possible failures due to NULL data for empty values (especially needed when you are using flatfile source).
Consolidate connection managerThis feature automatically finds any duplicate connections and removes them from new SSIS packages during the migration process.
Handle parameterized SQL Statement using variable with expressionDTS xChange creates variables to store SQL statements based on the provider type and parameter usage in the statement. It's recommended practice that you use expression based variables to avoid data type issues when you have parameters in the SQL Statement.
Proper naming convention for connection managersDTS xChange uses consistent naming conventions for connections across all converted packages.

You can apply the following new native features of SSIS during the migration process:

  • Logging to Text file using
  • Logging to SQL Server
  • CheckPoint
    • Can help you to restart your package from the point where it was failed last time.
  • Transaction
  • DataFlow validation options
  • Package Protection Level

Note:  DTS xChange gives you options to apply the features listed above in bulk during the migration process.

Deployment Framework

Configuration files are similar to UDL files in DTS. Unlike UDL files, SSIS Configuration files can also store Variable values, Task Properties and Connection Attributes. In SSIS there are several methods of making packages portable:

Configuration FileDescription
XML configuration fileAn XML file containing the configuration information.
Environment variableAn environment variable contains the configuration information.
Registry entryA registry entry contains the configuration information.
Parent package variableA variable in the package contains the configuration. This configuration type is typically used to update properties in child packages.
SQL ServerA table in a SQL Server database that contains the configuration.

The XML Configuration file is the easiest and most common method to make your package portable. DTS xChange makes your package portable using the Deployment framework feature that automatically creates reusable config files to store connection strings. DTS xChange automatically creates the minimum number of unique configuration files and reuses them across multiple packages which helps to save time in the deployment process. 

DTS xChange Deployment Framework tab

DTS xChange Package Configurations Organizer

When you move your packages to a different environment (such as from DEV to PROD) where you have to point all your package connections to new data sources, you can do the following:

  1. Edit your config files using an XML editor or Notepad.
  2. Change your server name and other connection-specific information.

Success: All the packages using the config file start using new connection string.

OptionDescription
Create config file per connection (shared files)This is the recommended option. DTS xChange creates the minimum number of config files by comparing the server name, username and database name attributes of the DTS package connections and existing config files. If there is a config file that's already created with matching attributes it will be reused in the new SSIS package.

Note:  This option only stores one unique connection string per config file. If the Consolidate connection manager best practice is selected then the connection manager and config file naming convention is <connection type>_<database or file name>_<number> (for example, OLEDB_NORTHWIND_1).
Create config file per packageSelect this option if you have the requirement to manage each config file per package, and you don't want to share that connection information with any other package. This option stores all connection information used in the SSIS package.
Where config files will be storedSpecifies the folder location where new config files are stored. Keep this location the same for all of the packages you migrate using DTS xChange because the migration process constantly scans this directory to make adjustments and reuse the existing config file whenever possible.

Note:  The SSIS package reads the config file from this location during runtime. Make sure this path exists within all config files used by the package when you execute your SSIS package.

Note:  The Config file path is hard coded in the package. Keep the config folder path consistent across all environments. Executing the package on a different machine may fail if you have a different config file path.

OptionDescription
Store configuration in separate elementsCreates individual XML elements for ServerName, UserName, Password, and Catalog in the config file. Don't use this option if you want to have more control over connection string attributes (for example, defining Timeout or changing Authentication Method).
Store configuration in a single elementStore the entire connection string including the password into a single XML element. This is the recommended option.
Create configurations for the following typesChecks the connection types where you want to generate a config file. All connection types are selected by default

Additional Information: For more information, see the Making SSIS Packages Portable article.

Convert Child Packages

Select Convert child DTS package to SSIS package to automatically detect and convert any Execute Package tasks to Execute SSIS Package tasks and migrate all child packages to SSIS. 

DTS xChange Convert Child Packages tab

Note:  Your converted package(s) will still point to child DTS packages if you don't select this option.

DTS xChange sample package with Convert Child Packages Off
Convert Child Packages Inactive

DTS xChange sample package with Convert Child Packages On
Convert Child Packages Active

Specify one of the following three options for the target location of the converted child packages:

OptionDescription
Default (same as parent location)Saves the converted child package(s) to the same location as the parent package(s). The Target location is specified on the select source and target location screen.
Store on SQL ServerSaves the converted package(s) to msbd database on SQL Server 2005/2008.
Store on file systemSaves the converted package(s) to a file system location other than parent package location.

Auditing Framework

DTS xChange offers an easy way to add rich auditing features to converted packages using the custom auditing framework developed by SentryOne. The Auditing Framework uses all Native SSIS features and it tracks packages in real-time. The Auditing Framework comes with predefined reports that give you valuable information about your migrations. DTS xChange Auditing Framework tab

For information about the SSIS Logging Auditing and Monitoring Framework, see the Auditing and Monitoring article. 

The following options are available on the Auditing Framework tab:

OptionDescription
ServerThe Database server instance name where you want to store the auditing information. This can be SQL Server 2000/2005 or 2008.
Windows AuthenticationSelect this option if you are using windows authentication to connect to SQL Server.
User NameEnter your SQL Server login name. This option is disabled if you select windows authentication.
PasswordEnter your SQL Server password. This option is disabled if you select windows authentication.
Log Source Row CountCaptures the row count coming from source.

Note:  Sometimes this option is also referred as extracted row count.
Log Destination Row CountCaptures the row count coming into destination.

Note:  Sometimes this option is referred as loaded row count.
Log SQL Statement for Source/DestinationLogs SQL queries that written to extract the data. If you are pulling data from a flat file or some other relational source then Table/View Name is logged.
Log Connection InformationLogs useful attributes related to the source or target connection: the entire connectionstring, filename, servername, catalogname and username.

Consolidate Connection Managers

DTS in many cases requires you to create connections multiple times with same attributes (for example Server, User Name and Database Name). This creates maintenance issues because when connection attribute(s) are changed, you have to update multiple connections. In SSIS you can create a connection manager once and reuse it in your package with multiple task(s)(for example DataFlow Task, ExecuteSQL Task etc).

DTS xChange takes advantage of this new feature of SSIS and removes any duplicate connections from the SSIS package. Any task referring to duplicate connections are adjusted automatically to use the consolidated connection.

DTS xChange Consolidate Connections tab

View a real time counter of the number of duplicate connections that are removed during the migration on the migration summary panel.

DTS xChange Migration Summary example

Select Show Detail view on the migration log page to check the connections that are consolidated.

DTS xChange View Migration Report screen

Note:  Selecting the consolidate connection setting may display fewer connections in SSIS compared to your original package.

DTS xChange Consolidate Connection Off
Consolidate Connection Inactive

DTS xChange Consolidate Connection On
Consolidate Connection Active

Logging to SQL Server

Select Logging to SQL Server to enable the native SQL Server Logging provider for converted packages. Select from several events that you want to add to the log. DTS xChange Logging to SQL Server tab

Note:  If you have selected the Auditing Framework, deselect this option to avoid the extra overhead of logging during the package execution.

Note:  Logging to SQL Server is an SSIS native logging provider and Auditing Framework is a custom logging framework created by SentryOne using the native SSIS feature called Event Handler. Auditing Framework tracks performance, errors/warnings, row count and many more. Auditing Framework also gives you great reporting capability that you don't get when you select Logging to text or Logging to SQL Server.

Additional Information: For more information about Logging to Packages see the Implementing Logging in Packages MSDN article.

Logging to Text File

Select Logging to Text to enable the native Text File Logging provider for the converted packages. Select several events that you want to log. 

DTS xChange Logging to Text tab

Note:  If you have already selected the Auditing Framework, deselect this option to avoid the extra overhead of logging during package execution.

Note:  Logging to Text file is the SSIS native logging provider and Auditing Framework is a custom logging framework created by SentryOne using the native SSIS feature called Event Handler. Auditing Framework tracks performance, errors/warnings, row count and many more. Auditing Framework also gives you great reporting capability that you don't get when you select Logging to text or Logging to SQL Server.

Additional Information: For more information about Logging to Packages see the Implementing Logging in Packages MSDN article.

Add Checkpoint

Integration Services can restart failed packages from the point of failure, instead of rerunning the package. If a package is configured to use checkpoints, information about package execution is written to a checkpoint file. When the failed package is rerun, the checkpoint file is used to restart the package from the point of failure. If the package runs successfully, the checkpoint file is deleted, and then re-created the next time the package is run.

DTS xChange Checkpoints tab

Select Add Checkpoints to enable checkpoints for DTS xChange converted packages.

Additional Information: For more information about checkpoints, see the Restart Packages by Using Checkpoints MSDN article.

Wrap Package in Transaction

Wrapping a package in a transaction ensures that data events are protected by a transaction. If a data problem occurs, all actions that have occurred in the data are rolled back.

Select Wrap Package in Transaction to apply the transaction setting to your package.DTS xChange Transaction tab

Note:  Enabling this feature requires the Microsoft Distributed Transaction Coordinator (MSDTC) service to be  enabled on each server participating in the package. Use database native transactions (such as BEGIN TRAN... COMMIT) because MSDTS transactions are slower than native transactions.

Isolation Level

There are seven different types of transaction isolation levels you can select from the dropdown.

Isolation LevelDescription
UnspecifiedA different isolation level than the one specified is being used, but the level cannot be determined. When using OdbcTransaction, if you do not set IsolationLevel or you set IsolationLevel to Unspecified, the transaction executes according to the default isolation level of the underlying ODBC driver.
ChaosThe pending changes from more highly isolated transactions cannot be overwritten.
ReadUncommittedA dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored.
ReadCommittedShared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data.
RepeatableReadLocks are placed on all data that is used in a query, preventing other users from updating the data. Prevents non-repeatable reads but phantom rows are still possible.
SerializableA range lock is placed on the DataSet, preventing other users from updating or inserting rows into the dataset until the transaction is complete.
SnapshotReduces blocking by storing a version of data that one application can read while another is modifying the same data. Indicates that from one transaction you cannot see changes made in other transactions, even if you re-query.

TransactionOption

In DTS 2000, you could run transactions over packages that enabled you to fail or succeed groups of atomic units of work similar to using a traditional RDBMS.

SQL Server Integration Services includes similar functionality that is easier to configure. It also enables you to have multiple transactions in a package. Each container has a property called TransactionOption that configures that container's participation in a transaction. If a container starts a transaction, all child containers of that transaction have the option to enlist in that transaction.

TransactionOption has three possible settings:

SettingDescription
NotSupportedThe container does not enlist in a transaction, even if one is started by a parent container.
SupportedThe container enlists in a transaction if one is started by a parent container. If a parent container did not start a transaction, the container does not enlist in any transaction.
RequiredThe container enlists in a transaction if one is started by a parent container. If a parent container does not start a transaction the container starts one itself.

Additional Information: For more information about Wrapping a Package in a transaction, see the Integration Services Transactions MSDN article.

NULL Handling

DTS xChange Add NULL Handling option

There are several options on NULL Handling screen. NULL Handling is a recommended setting and should not be deselected unless you have specific reason. The following options are configured on the NULL Handling screen by default:

SettingDescription
Convert Blank [Numbers] to NULLCreates an expression if the target column is NUMERIC (int, smallint, money) and the source column is text (nvarchar, varchar, char). This setting is ON by default.
Convert Blank [DateTime] to NULLCreates an expression if the target column is DateTime (smalldatetime, datetime) and the source column is text (nvarchar, varchar, char...). This setting is ON by default.
Convert Blank [Text] to NULLCreates an expression if the source is text (nvarchar, varchar, char...). This setting is OFF by default.
Convert Blank [Boolean] to NULLCreates an expression if the target column is BOOLEAN (bit) and the source column is TEXT (nvarchar, varchar, char...). This setting is OFF by default.
Convert any other blank [ GUID, varbinary...] to NULLCreates an expression if the source column is TEXT (nvarchar, varchar, char...) and the target column is any data types other than TEXT , BOOLEAN, DATETIME or NUMERIC. This setting is ON by default.
Convert column values containing only spaces to NULLConverts any column containing only spaces to NULL. This setting is OFF by default. This setting can be useful if a Fixed width file contains a blank column that represents several spaces.
Trim extra spaces for Boolean ValueCreates an expression if the target column is BOOLEAN (bit) and the source column is TEXT (nvarchar, varchar, char...). This setting is useful when you have for Fixed width file and you store TRUE.
Generate NULL Handling only if destination column allows NULLChecks the Nullable flag from the DTS Package column metadata (transformations-> destination columns -> {{column name}} -> Nullable Property). Sometimes metadata from DTS is not up to date and you may see some unexpected NULL handling expressions or expressions may not be created at all for certain columns. If you don't see expressions when it should be then deselect this option to ignore Nullable flag from DTS and create NULL handling based on source-target datatype comparison. This setting is ON by default.

What is NULL Handling?

NULL Handling issues in DTS 2000 Packages are rare because everything in DTS is implicit, meaning that DTS automatically converts the data type of your data to match the Target Column Data Type (which could be an unwanted result in many cases). Automatic NULL Handling in DTS gives you ease of use but with the price of performance.

SSIS uses Explicit Conversion meaning that SSIS does not change the value or type cast the data unless you explicitly specify (Data Conversion Task is used for this purpose). This gives better performance compared to DTS but you have to do additional work to setup Data Conversion.

It's possible that you may have data in all columns or rows when loading data from the source. 

Note:  Generally a Fixed Width File has spaces when no data is found in the column.

Consider the following scenario if you only have empty values or spaces only: 

With SSIS any source column of a string data type (varchar, nvarchar, text, ntext ... etc) mapped to a non-string data type, and data coming from a source has blank values or spaces, then most likely DataFlow outputs an error. DTS always convert blank strings to NULL but SSIS does not do that automatically. In SSIS you have to write expressions to convert blank values or spaces to proper NULL.

MS DTS Migration Wizard does not support NULL handling and you have to modify Data Flow to fix various issues related to NULL. DTS xChange can be used to complete all of the tedious work for you during the migration of your DTS Packages. Review the following examples how DTS xChange helps you to save significant amounts of development/testing time by automatically implementing NULL handling. Check the following figures that display sample data requiring proper NULL Handling.

ExampleImage
NULL Handling issue for Delimited Flat FileDTS xChange NULL Handling issue for Delimited Flat File
NULL Handling issue for Fixed-Width Flat FileDTS xChange NULL Handling issue for Fixed Width Flat File

Expressions

The following table displays various expressions generated by DTS xChange. Expressions only generate if the column needs NULL Handling.

SettingSample Expression and Explanation
Convert Blank [Numbers] to NULL Convert Blank [Boolean] to NULL Convert Blank [DateTime] to NULL Convert Blank [Text] to NULL Convert Blank [Other] to NULL Convert column values containing only spaces to NULL

TRIM(CategoryID) == "" ? (DT_STR,255,1252)NULL(DT_STR,255,1252) : CategoryID

Explanation: If the source column value is blank then it converts to NULL otherwise use same source value.

Trim extra spaces for Boolean Value

TRIM(Discontinued) == "" ? (DT_STR,255,1252)NULL(DT_STR,255,1252) : TRIM(Discontinued)

Explanation: Trim value if Target is BOOLEAN and source column value is TEXT.  If you store True/False value in Fixed width file then Trim it so you don't carry over extra spaces. 

Last column is defined as 5 characters but some times when you have "True" value it only uses 4 character and 5th character is <space>. This creates problem in SSIS unless you trim additional spaces.

DTS xChange Sample DataFlow using NULL HandlingSample DataFlow using NULL Handling

Additional Information: For more information about NULL Handling see the Derived Column Transformation and Integration Services Expression Reference MSDN articles.

Codepage Settings

CodePage Setting options can be used if your source system has data stored in a different language (other than ANSI Latin Settings - Codepage 1252). SSIS uses 1252 CodePage to translate source data by default. 

Do not modify codepage settings if you are not sure about source system settings. The Default option works fine for most cases. If your source system has data stored in Japanese for example, then you have to find the appropriate code page number to translate Japanese characters (e.g. 932) and enter in the DefaultCodePage field.

DTS xChange CodePage Settings tab

The following Codepage Settings are available:

SettingDescription
Default CodePageEnter the codepage number used to translate source/target data when Always Use DefaultCodePage for Non-SQL Server Source or Target option is selected.
Always use Default CodePage for Non-SQL Server SourcesForces SSIS to use DefaultCodePage number to translate source characters for Non-SQL Server (ORACLE, DB2, etc). This setting ensures that when CodePage information is not available from source, DefaultCodePage is used instead of throwing error.
Always use Default CodePage for Non-SQL Server TargetsForces SSIS to use DefaultCodePage number to translate target characters for Non-SQL Server (ORACLE, DB2, etc). This setting ensures that when CodePage information is not available from target, DefaultCodePage is used instead of throwing error.
Always use Default CodePage for ExecuteSQL TaskForces SSIS to use DefaultCodePage number for any converted ExecuteSQLTask.
Always use Default CodePage for Flat File ConnectionsForces SSIS to use DefaultCodePage number for any Flat File Connections.

Additional Information: For more information about Codepage settings, see the Codepage Identifiers MSDN article.

Handle Unsupported Data Providers

SSIS is completely re-written and significantly different in many ways. Some Data Providers that work fine with DTS may not work as expected or may not work at all in SSIS. DTS xChange gives you intelligent warning messages if the Data Provider you're using isn't on the Supported list.

DTS xChange Handle Data Providers tab

Note:  Your drivers may not be on the supported list which indicates that they have not been officially tested by the SSIS Team at Microsoft.

The following options are available:

OptionDescription
Check for 64bit supportSelect this option if you are planning to run SSIS packages on a 64bit machine.
Automatically replace SQLOLEDB to SQL Native ClientSelect this option to replace all your SQL Server connections from DTS Packages with new SQL Native Client driver.

If you're using a data provider that isn't on the compatibility list during migration, you are prompted to select a new driver or continue using the old driver.DTS xChange Suggested New Data Provider screen

Compatibility List of Data Providers

The following is a list of known data providers:

Note:  This is not a definitive list of all data providers. Not all of the providers listed have been tested by Microsoft with SSIS. Information about third-party products was provided by the product vendors and could not be independently verified.

SQL Server

Data SourceReleased byData access APISupported Database VersionsVendor Support with SSIS?64-bit Support?
SQL ServerMicrosoftOLE DB2000 and later versionsYesx64, IA64
SQL ServerMicrosoftADO.NET2000 and later versionsYesx64, IA64
SQL ServerMicrosoftODBC2000 and later versionsYesx64, IA64
SQL ServerMicrosoftOLE DB2000 and later versionsYesN/A
SQL ServerMicrosoftOLE DB2000 and later versionsYesN/A

DB2

Data SourceReleased byData access APISupported Database VersionsVendor Support with SSIS?64-bit Support?
DB2MicrosoftOLE DBAll DRDA-compliant versionsYesx64, IA64
DB2IBMOLE DBz/OS and UDB 7.0 and later, AIX 5.0 and later, AS400 4.5 and later versionsYesN/A
DB2IBMADO.NETDB2 UDB 9.0NoN/A
DB2IBMODBCz/OS and UDB 7.0 and later versions AIX 5.0 and later versions, AS400 4.5 and later versionsNoNo
DB2AttunityOLE DB6.1, 7.x, 8.0 on z/OS 7.x, 8.0 on UNIX 7.x, 8.0 on WindowsYesN/A
DB2/400AttunityOLE DBOn AS/400 5.1 and later versionsYesN/A
DB2DataDirectOLE DBz/OS and UDB 7.0 and later versions AIX 5.x AS400 4.5 and later versionsNoN/A
DB2HITOLE DB0z/OS and UDB 8.0 and later versions AIX 5.x, AS400 4.5 and later versionsNoN/A
DB2DataDirectADO.NETz/OS and UDB 7.0 and later AIX 5.x, AS400 4.5 and later versionsYesN/A
DB2ETIBulk Load8.0 and later versionsYesN/A
DB2¹PersistentData Flow Component Bulk Write, Bulk ReadN/ANoN/A

Oracle

Data SourceReleased byData access APISupported database versionsVendor Support with SSIS?64-bit Support?
OracleMicrosoftOLE DB7.3.4 and later versions²YesNo
OracleMicrosoftADO.NET8.0 and later versionsYesx64, IA64
OracleOracle CorpOLE DB8i and later versionsYesx64, IA64
OracleOracle CorpADO.NET8i and later versionsYesx64, IA64
OracleOracle CorpODBC8i and later versionsNoN/A
OracleMicrosoftODBC8i and later versionsNoN/A
OracleAttunityOLE DB9i and later versionsYesN/A
OracleDataDirectOLE DB8i and later versionsYesN/A
OracleDataDirectADO.NET8i and later versionsYesN/A
OracleETIBulk Load9.0 and later versionsYesN/A
OraclePersistentData Flow Component Bulk Write8i and later versionsNoN/A

Sybase

Data SourceReleased byData access APISupported database versionsVendor Support with SSIS?64-bit Support?
SybaseSybaseOLE DB11.5 and later versionsNoN/A
SybaseSybaseADO.NET11.5 and later versionsNoN/A
SybaseAttunityOLE DB12.0 and later versionsNoN/A
SybaseDataDirectOLE DB11.5 and later versionsYesN/A
SybaseDataDirectADO.NET11.5 and later versionsYesN/A

Informix

Data SourceReleased byData access APISupported Database VersionVendor Support with SSIS?64-bit Support?
InformixIBMOLE DB7.3 and later versionsNoN/A
InformixAttunityOLE DB7.31, 9.x, 10YesN/A
Informix¹PersistentData Flow Component Bulk Write & Bulk ReadN/ANoN/A

Teradata

Data SourceReleased byData access APISupported Database VersionVendor Support with SSIS?64-bit Support?
TeradataTeradataOLE DB2.6 and later versionsNoN/A
TeradataTeradataADO.NET2.6 and later versionsNoN/A
TeradataETIBulk Load2.5 and later versionsYesN/A
TeradataETIBulk Extract2.5 and later versionsYesN/A

Other

Data SourceReleased byData access APISupported Database VersionVendor Support with SSIS?64-bit Support?
SAP¹MicrosoftADO.NETR/3 4.6C and later versionsYesN/A
SAPTheobaldOLE DBR/3YesN/A
Office AccessMicrosoftOLE DB2003 and earlier versionsYesN/A
Office ExcelMicrosoftOLE DB2003 and earlier versionsYesN/A
Office 2007MicrosoftOLE DB2007NoN/A
FoxProMicrosoftOLE DB8.0 and later versionsNoN/A
File DBsMicrosoftOLE DBAny Jet 4.0– compatible versionNoN/A
AdabaseAttunityOLE DB6.2.2 to 7.4.x on z/OS 3.3 to 5.1 on Open Systems (UNIX, Windows, OpenVMS)YesN/A
CISAMAttunityOLE DBOn UNIXYesN/A
DISAMAttunityOLE DBOn UNIX, Linux, and WindowsYesN/A
Ingres IIAttunityOLE DB2 to 2.56YesN/A
Oracle RdbAttunityOLE DB7.1.x, on OpenVMS Alpha and Integrity (Itanium)YesYes (HP Integrity)
RMSAttunityOLE DBOn OpenVMS Alpha and Integrity (Itanium)YesYes (HP Integrity)
EnscribeAttunityOLE DBOn HP NonStop G- Series and H-SeriesYesYes (HP Integrity)
SQL/MPAttunityOLE DBOn HP NonStop G- Series and H-SeriesYesYes (HP Integrity)
IMS/DBAttunityOLE DB6.1 and later versionsYesN/A
VSAMAttunityOLE DBOn z/OS 1.1 and later versionsYesN/A
LDAPPersistentODBCAll LDAP-compliant serversNoYes

Additional Information: For more information about the above lists, see the Connectivity and SQL Server 2005 Integration Services MSDN article.

DataFlow Options

DTS xChange Dataflow Options tab

The following options are available:

OptionDescription
Truncation Row Disposition Option

Select any of the following options to change the row truncation disposition option. This option determines what action should be performed when data is truncated. The Default action is Fail Component. The available options are:

  • RD_FailComponent
  • RD_IgnoreFailure
  • RD_RedirectRow
Command TimeoutEnter the number of seconds before source adapter query/command times out. Enter 0 if you don't want a timeout.
Delay ValidationSelect this option if you are working in disconnected mode and you don't want to validate dataflow.

Other Options

DTS xChange Other Options tab

The following options are available:

OptionDescription
Package Protection LevelUse this option to change the protection level of the converted package.

Additional Information: For more information see the Access Control for Sensitive Data in Packages MSDN article.
Always Use Direct SQL for ExecuteSQL TaskSelect this option if you want to use direct SQL Statements and you don't want to create expression based variables.

Note:  DTS xChange takes some smart decisions and uses expression based variables for certain types of Parameterized SQL Statements by default, which is the recommended best practice to avoid any Provider specific parameters handling.
ScriptTask LanguageThis option is only valid if you select Target Platform SQL Server 2008. For SQL Server 2005 You have only one option VB.net and this option is  grayed out. In SQL 2008 You can write script tasks in C# to VB.net. Depending on what language is selected in this dropdown DTS xChange creates code in the selected language for Script Task during migration.
Auto Correct Flat File Column OrderDTS xChange creates Flat File connection manager columns based on the metadata of the original DTS Flat File connection and DataPump transformation using the flat file connection. Select this option to generate the flat file column order based on the DTS package metadata.

Note:  In some cases DTS metadata is incorrect. If your metadata is incorrect, disable this option to migrate the Flat File connection properly.

Verify and Migrate Packages Screen

DTS xChange Verify and Migrate Packages screen

The following options are available:

OptionDescription
Validate Packages After MigrationSelect this option to fully validate your package for possible errors/warnings after migration.
View PackagesSelect this option after your migration has completed to automatically create an Integration Services project and open Visual Studio with all migrated packages included in the project.

Migration Summary Screen

DTS xChange Migration Summary screen

The Migration Summary Screen displays the following information:

  • Migration Errors/Warning and Attention Count
  • Validation Errors/Warning Count
  • Migration related reading material

View Migration and Validation Log Screen

After your migration has completed, you can view the Migration and Validation Logs.

The Migration Log displays informational messages and migration related errors and warnings that are reported by DTS xChange.

DTS xChange View Migration and Validation Log screen

The Validation Log displays any errors or warnings that are reported by the SSIS Runtime engine. Correct any validation errors after your migration to run your package(s) successfully. 

Note:  Generally validation errors are problems with the connection string, permissions, or file locations and are not migration issues.

Error TypeDescription
Validation errorsA red X indicates a validation error. Your package may not run until validation errors are fixed.
Validation warningsA yellow ! indicates a validation warning. Validation warnings are mostly non-critical, and fixing these issues helps with the performance of the SSIS package. Your package will continue to run, but may suffer from performance issues until validation warnings are fixed.

View Migration History Screen

Select log on the welcome screen of DTS xChange to open any log for a previous migration. Select the item you want to view, and then select next to view the log.

DTS xChange View Migration History screen

DTS Profiler

Before your migration project starts, find out quickly what you're about to jump into. DTS xChange offers a detailed profiling tool that allows you to scope out your DTS migration project. It's perfect for project managers, DBA managers, consultants or anyone interested in determining how much of an effort the migration is going to be.

Option Screen Profiler

See Option Screen section.

Select Source Screen

DTS xChange Specify Source Location screen

Specify where DTS packages are stored on the Select Source screen. The following options are available:

OptionDescription
Source SQL ServerSelect this option if your DTS packages are stored on SQL Server 2000 or SQL Server 2005 (under legacy mode). You can specify the instance name or IP. See the following examples:
  • MYSERVER\SQLINST1
  • 192.168.2.3
  • (local)
  • MYSERVER\DEVINST1,1433
Source File SystemSelect this option if your DTS packages are stored as COM Structured files (*.dts). Browse for the folder location where the DTS files are stored. Select your packages from the desired folder.

Select Packages Screen Profiler

See Select Packages Screen section.

Enter Estimate Screen

DTS xChange Migration Estimate

The following options are available on the Enter Estimate screen:

OptionDescription
Estimate by task typeThis matrix has all the possible DTS task types listed in the grid view. This table contains two estimate columns with suggested numbers that can be changed. The following three columns are displayed:
Task Name - This column lists common DTS task types. Any item in the Green column is converted 100% by DTS xChange. Any item in the yellow column is partially converted by DTS xChange, meaning that it may or may not work properly after the conversion and may require some manual tweaking to make it work. Any item in the red column means there is no equivalent task in SSIS, or DTS xChange can not convert that task and you will have to manually re-write the task to native SSIS using your own approach.
Est. Hrs (Manual) - This column lists the estimated time required to convert the associated task type using the manual approach.
Est. Hrs (DTS xChange) - This column lists the estimated time required to convert the associated task type using DTS xChange. Most of the tasks are automatically converted. Green cells are read only because DTS xChange converts the Green Task Type 100%. Anything in the yellow or red is editable and you can enter your own estimate.
Other Time - Manual approach (Per Package)Enter the estimated overhead time per package when you do migration manually without using DTS xChange. Usually overhead time includes Testing and Troubleshooting time, variable migration and connection migration time.
Other Time - Manual approach (Per Package)Enter the estimated overhead time per package when you do migration using DTS xChange. This time is usually less than previous field because less troubleshooting/testing is required compared to the manual approach when you convert packages using DTS xChange.
Development CostEnter the Average Hourly development cost for the developer working on the migration project.
Additional FeaturesIf you want to add an estimate for applying certain best practices when you go though the manual approach, select one or more options here and enter the time to implement each best practice feature in your converted package.

Profiler Summary Report

SectionImage
Top Summary sectionDTS xChange Package Profile Report Summary Version
Task Distribution SummaryDTS xChange Profile Report Task Distribution Summary
Connection Distribution SummaryDTS xChange Profile Report Connection Distribution Summary
Package/Task SummaryDTS xChange Profile Report Package/Task Summary

Profiler Detail Report

DTS xChange Profiler Detail Report

Select Generate detailed report on the DTS Migration Estimate screen to generate a very detailed profiler report with all task attributes.