EOL: DTSxChange reached its end of life date on October 1, 2021. See the Solarwinds End of Life Policy for more information.
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:
| 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. |
Option Screen
The first screen that displays in DTS xChange is the Option Screen. The following options are available:
| Option | Description |
|---|---|
| Migrate | Opens 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. |
| Profile | Opens 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. |
| Reports | Opens the Report Viewer Application. Use the Report Viewer to view several predefined reports for SSIS package auditing. |
| Log | Displays the history of all the previous migrations that occurred on the machine where DTS xChange is currently installed. |
| Support/Bug | Select 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 update | DTS 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. |
| Registration | Displays the license information for your product. Use the Registration screen to activate or deactivate your license. |
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:
| Option | Description |
|---|---|
| Source SQL Server | Select 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:
|
| Source File System | Select 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:
| Option | Description |
|---|---|
| Target SQL Server | Select 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 System | Select Target File System if you want to save converted packages on file system (*.dtsx). |
| Target Platform | Target 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 Option | Select one of the three different options that specifies an action when a file already exists on the target location. |
| 64bit Option | Select 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
| Option | Description |
|---|---|
| Perform MetaData Validation | This 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 log | Enable/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 history | Generates 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
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.
| Option | Description |
|---|---|
| Display Associated Job Detail | Displays four additional columns related to associated job detail which includes the following: Job Name, last execution date, creation date, and last execution status. |
| Search for | Filter 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 clipboard | Save the content of the top grid into the clipboard in Tab delimited values (you can copy/paste content to excel). |
| Save report | Save 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 applies the following best practices:
| Best Practice | Description |
|---|---|
| Deployment framework | This 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 Handling | This 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 manager | This feature automatically finds any duplicate connections and removes them from new SSIS packages during the migration process. |
| Handle parameterized SQL Statement using variable with expression | DTS 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 managers | DTS 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 File | Description |
|---|---|
| XML configuration file | An XML file containing the configuration information. |
| Environment variable | An environment variable contains the configuration information. |
| Registry entry | A registry entry contains the configuration information. |
| Parent package variable | A variable in the package contains the configuration. This configuration type is typically used to update properties in child packages. |
| SQL Server | A 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.
 |
 |
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:
- Edit your config files using an XML editor or Notepad.
- Change your server name and other connection-specific information.
Success: All the packages using the config file start using new connection string.
| Option | Description |
|---|---|
| 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 package | Select 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 stored | Specifies 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.
| Option | Description |
|---|---|
| Store configuration in separate elements | Creates 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 element | Store the entire connection string including the password into a single XML element. This is the recommended option. |
| Create configurations for the following types | Checks 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.
Note: Your converted package(s) will still point to child DTS packages if you don't select this option.
 Convert Child Packages Inactive
|  Convert Child Packages Active
|
Specify one of the following three options for the target location of the converted child packages:
| Option | Description |
|---|---|
| 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 Server | Saves the converted package(s) to msbd database on SQL Server 2005/2008. |
| Store on file system | Saves 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.
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:
| Option | Description |
|---|---|
| Server | The Database server instance name where you want to store the auditing information. This can be SQL Server 2000/2005 or 2008. |
| Windows Authentication | Select this option if you are using windows authentication to connect to SQL Server. |
| User Name | Enter your SQL Server login name. This option is disabled if you select windows authentication. |
| Password | Enter your SQL Server password. This option is disabled if you select windows authentication. |
| Log Source Row Count | Captures the row count coming from source. Note: Sometimes this option is also referred as extracted row count. |
| Log Destination Row Count | Captures the row count coming into destination. Note: Sometimes this option is referred as loaded row count. |
| Log SQL Statement for Source/Destination | Logs 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 Information | Logs 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.
View a real time counter of the number of duplicate connections that are removed during the migration on the migration summary panel.
Select Show Detail view on the migration log page to check the connections that are consolidated.
Note: Selecting the consolidate connection setting may display fewer connections in SSIS compared to your original package.
 Consolidate Connection Inactive
|  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. 
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.
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.
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.
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 Level | Description |
|---|---|
| Unspecified | A 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. |
| Chaos | The pending changes from more highly isolated transactions cannot be overwritten. |
| ReadUncommitted | A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored. |
| ReadCommitted | Shared 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. |
| RepeatableRead | Locks 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. |
| Serializable | A range lock is placed on the DataSet, preventing other users from updating or inserting rows into the dataset until the transaction is complete. |
| Snapshot | Reduces 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:
| Setting | Description |
|---|---|
| NotSupported | The container does not enlist in a transaction, even if one is started by a parent container. |
| Supported | The 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. |
| Required | The 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
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:
| Setting | Description |
|---|---|
| Convert Blank [Numbers] to NULL | Creates 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 NULL | Creates 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 NULL | Creates an expression if the source is text (nvarchar, varchar, char...). This setting is OFF by default. |
| Convert Blank [Boolean] to NULL | Creates 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 NULL | Creates 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 NULL | Converts 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 Value | Creates 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 NULL | Checks 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.
| Example | Image |
|---|---|
| NULL Handling issue for Delimited Flat File |  |
| 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.
| Setting | Sample 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. |
Sample 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.
The following Codepage Settings are available:
| Setting | Description |
|---|---|
| Default CodePage | Enter 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 Sources | Forces 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 Targets | Forces 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 Task | Forces SSIS to use DefaultCodePage number for any converted ExecuteSQLTask. |
| Always use Default CodePage for Flat File Connections | Forces 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.
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:
| Option | Description |
|---|---|
| Check for 64bit support | Select this option if you are planning to run SSIS packages on a 64bit machine. |
| Automatically replace SQLOLEDB to SQL Native Client | Select 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.
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 Source | Released by | Data access API | Supported Database Versions | Vendor Support with SSIS? | 64-bit Support? |
|---|---|---|---|---|---|
| SQL Server | Microsoft | OLE DB | 2000 and later versions | Yes | x64, IA64 |
| SQL Server | Microsoft | ADO.NET | 2000 and later versions | Yes | x64, IA64 |
| SQL Server | Microsoft | ODBC | 2000 and later versions | Yes | x64, IA64 |
| SQL Server | Microsoft | OLE DB | 2000 and later versions | Yes | N/A |
| SQL Server | Microsoft | OLE DB | 2000 and later versions | Yes | N/A |
DB2
| Data Source | Released by | Data access API | Supported Database Versions | Vendor Support with SSIS? | 64-bit Support? |
|---|---|---|---|---|---|
| DB2 | Microsoft | OLE DB | All DRDA-compliant versions | Yes | x64, IA64 |
| DB2 | IBM | OLE DB | z/OS and UDB 7.0 and later, AIX 5.0 and later, AS400 4.5 and later versions | Yes | N/A |
| DB2 | IBM | ADO.NET | DB2 UDB 9.0 | No | N/A |
| DB2 | IBM | ODBC | z/OS and UDB 7.0 and later versions AIX 5.0 and later versions, AS400 4.5 and later versions | No | No |
| DB2 | Attunity | OLE DB | 6.1, 7.x, 8.0 on z/OS 7.x, 8.0 on UNIX 7.x, 8.0 on Windows | Yes | N/A |
| DB2/400 | Attunity | OLE DB | On AS/400 5.1 and later versions | Yes | N/A |
| DB2 | DataDirect | OLE DB | z/OS and UDB 7.0 and later versions AIX 5.x AS400 4.5 and later versions | No | N/A |
| DB2 | HIT | OLE DB0 | z/OS and UDB 8.0 and later versions AIX 5.x, AS400 4.5 and later versions | No | N/A |
| DB2 | DataDirect | ADO.NET | z/OS and UDB 7.0 and later AIX 5.x, AS400 4.5 and later versions | Yes | N/A |
| DB2 | ETI | Bulk Load | 8.0 and later versions | Yes | N/A |
| DB2¹ | Persistent | Data Flow Component Bulk Write, Bulk Read | N/A | No | N/A |
Oracle
| Data Source | Released by | Data access API | Supported database versions | Vendor Support with SSIS? | 64-bit Support? |
|---|---|---|---|---|---|
| Oracle | Microsoft | OLE DB | 7.3.4 and later versions² | Yes | No |
| Oracle | Microsoft | ADO.NET | 8.0 and later versions | Yes | x64, IA64 |
| Oracle | Oracle Corp | OLE DB | 8i and later versions | Yes | x64, IA64 |
| Oracle | Oracle Corp | ADO.NET | 8i and later versions | Yes | x64, IA64 |
| Oracle | Oracle Corp | ODBC | 8i and later versions | No | N/A |
| Oracle | Microsoft | ODBC | 8i and later versions | No | N/A |
| Oracle | Attunity | OLE DB | 9i and later versions | Yes | N/A |
| Oracle | DataDirect | OLE DB | 8i and later versions | Yes | N/A |
| Oracle | DataDirect | ADO.NET | 8i and later versions | Yes | N/A |
| Oracle | ETI | Bulk Load | 9.0 and later versions | Yes | N/A |
| Oracle | Persistent | Data Flow Component Bulk Write | 8i and later versions | No | N/A |
Sybase
| Data Source | Released by | Data access API | Supported database versions | Vendor Support with SSIS? | 64-bit Support? |
|---|---|---|---|---|---|
| Sybase | Sybase | OLE DB | 11.5 and later versions | No | N/A |
| Sybase | Sybase | ADO.NET | 11.5 and later versions | No | N/A |
| Sybase | Attunity | OLE DB | 12.0 and later versions | No | N/A |
| Sybase | DataDirect | OLE DB | 11.5 and later versions | Yes | N/A |
| Sybase | DataDirect | ADO.NET | 11.5 and later versions | Yes | N/A |
Informix
| Data Source | Released by | Data access API | Supported Database Version | Vendor Support with SSIS? | 64-bit Support? |
|---|---|---|---|---|---|
| Informix | IBM | OLE DB | 7.3 and later versions | No | N/A |
| Informix | Attunity | OLE DB | 7.31, 9.x, 10 | Yes | N/A |
| Informix¹ | Persistent | Data Flow Component Bulk Write & Bulk Read | N/A | No | N/A |
Teradata
| Data Source | Released by | Data access API | Supported Database Version | Vendor Support with SSIS? | 64-bit Support? |
|---|---|---|---|---|---|
| Teradata | Teradata | OLE DB | 2.6 and later versions | No | N/A |
| Teradata | Teradata | ADO.NET | 2.6 and later versions | No | N/A |
| Teradata | ETI | Bulk Load | 2.5 and later versions | Yes | N/A |
| Teradata | ETI | Bulk Extract | 2.5 and later versions | Yes | N/A |
Other
| Data Source | Released by | Data access API | Supported Database Version | Vendor Support with SSIS? | 64-bit Support? |
|---|---|---|---|---|---|
| SAP¹ | Microsoft | ADO.NET | R/3 4.6C and later versions | Yes | N/A |
| SAP | Theobald | OLE DB | R/3 | Yes | N/A |
| Office Access | Microsoft | OLE DB | 2003 and earlier versions | Yes | N/A |
| Office Excel | Microsoft | OLE DB | 2003 and earlier versions | Yes | N/A |
| Office 2007 | Microsoft | OLE DB | 2007 | No | N/A |
| FoxPro | Microsoft | OLE DB | 8.0 and later versions | No | N/A |
| File DBs | Microsoft | OLE DB | Any Jet 4.0– compatible version | No | N/A |
| Adabase | Attunity | OLE DB | 6.2.2 to 7.4.x on z/OS 3.3 to 5.1 on Open Systems (UNIX, Windows, OpenVMS) | Yes | N/A |
| CISAM | Attunity | OLE DB | On UNIX | Yes | N/A |
| DISAM | Attunity | OLE DB | On UNIX, Linux, and Windows | Yes | N/A |
| Ingres II | Attunity | OLE DB | 2 to 2.56 | Yes | N/A |
| Oracle Rdb | Attunity | OLE DB | 7.1.x, on OpenVMS Alpha and Integrity (Itanium) | Yes | Yes (HP Integrity) |
| RMS | Attunity | OLE DB | On OpenVMS Alpha and Integrity (Itanium) | Yes | Yes (HP Integrity) |
| Enscribe | Attunity | OLE DB | On HP NonStop G- Series and H-Series | Yes | Yes (HP Integrity) |
| SQL/MP | Attunity | OLE DB | On HP NonStop G- Series and H-Series | Yes | Yes (HP Integrity) |
| IMS/DB | Attunity | OLE DB | 6.1 and later versions | Yes | N/A |
| VSAM | Attunity | OLE DB | On z/OS 1.1 and later versions | Yes | N/A |
| LDAP | Persistent | ODBC | All LDAP-compliant servers | No | Yes |
Additional Information: For more information about the above lists, see the Connectivity and SQL Server 2005 Integration Services MSDN article.
DataFlow Options
The following options are available:
| Option | Description |
|---|---|
| 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:
|
| Command Timeout | Enter the number of seconds before source adapter query/command times out. Enter 0 if you don't want a timeout. |
| Delay Validation | Select this option if you are working in disconnected mode and you don't want to validate dataflow. |
Other Options
The following options are available:
| Option | Description |
|---|---|
| Package Protection Level | Use 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 Task | Select 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 Language | This 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 Order | DTS 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
The following options are available:
| Option | Description |
|---|---|
| Validate Packages After Migration | Select this option to fully validate your package for possible errors/warnings after migration. |
| View Packages | Select 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
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.
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 Type | Description |
|---|---|
| Validation errors | A red X indicates a validation error. Your package may not run until validation errors are fixed. |
| Validation warnings | A 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 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
Select Source Screen
Specify where DTS packages are stored on the Select Source screen. The following options are available:
| Option | Description |
|---|---|
| Source SQL Server | Select 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:
|
| Source File System | Select 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
The following options are available on the Enter Estimate screen:
| Option | Description | |||
|---|---|---|---|---|
| Estimate by task type | This 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:
| |||
| 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 Cost | Enter the Average Hourly development cost for the developer working on the migration project. | |||
| Additional Features | If 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
| Section | Image |
|---|---|
| Top Summary section |  |
| Task Distribution Summary |  |
| Connection Distribution Summary |  |
| Package/Task Summary |  |
Profiler Detail Report
Select Generate detailed report on the DTS Migration Estimate screen to generate a very detailed profiler report with all task attributes.
