| Sr. No. |
Scope |
Issue/Feature
Description |
DTS xChange |
MS DTS Migration Wizard |
| 1. |
Connection |
ODBC Support |
DTS xChange will migrate ODBC Connection (With DSN or
DSN
less) created in you DTS 2000 Package. MS DTS Migration wizard
does not handle ODBC Connection properly and any DataPump Using
ODBC connection will be converted to Embedded DTS Package (see below)
which you will have to convert to DataFlow manually after Migration
is done.
|
MS DTS
Migration Wizard
|
DTS
xChange
|
|
|
|
DTS xChange uses ADO.net Connection Type to support
any ODBC Source inside DataFlow. If you using ODBC Source inside DataPump then DTS xChange will automatically create ADO.net Connection
for you. Since there is no ODBC Source available for DataFlow Task
DTS xChange creates DataReader Source to support ODBC Source.
|
|
 |
 |
| 2. |
Connection |
|
UDL File
Support |
DTS xChange will migrate any UDL connection created inside your
DTS 2000 Package. MS DTS Migration
wizard does not migrate UDL connection and
any task(s) using UDL connection needs to be re-configured manually
after migration is done. |
|
|
|
| 3. |
Connection |
|
Password Protected MS Access
Database Support |
If you have Password Protected Access Database connection in DTS
2000 Package then password property will not be migrated by MS DTS
Migration Wizard and after migration you have to go through manual
editing of the SSIS package. DTS xChange Successfully migrates any
password protected MS Access database connection. |
|
|
|
| 4. |
Connection |
|
Flat File Connection (When
not all flat file columns mapped) |
From our experience we have found that most of organizations use flat
files heavily and that's why migrating Flat File Connection from
DTS to SSIS is very important piece of Migration Process. If you
have a flat file and you are not using all columns of the file in
the mapping then MS DTS Migration wizard will not migrate the connection
successfully. Please refer the following comparison for more detail.
|
Test Case : Mapping
in DTS Package [Flat File => SQL Server]
|
|
MS DTS
Migration Wizard
|
DTS
xChange
|
|
|
|
| Here is an example
of data when Columns not created properly during migration
[Delimited File]
[Fixed Width File]
|
|
|
|
|
|
| 5. |
Connection |
|
Flat File
Connection (Columns mapped in different order) |
If you have Flat File connection in DTS Package and Columns of the
Flat File connection are not mapped in Sequential order then your
connection will not migrate successfully when you use MS DTS Migration
Wizard.
Look at the following very simple DTS mapping example (Flat
File => SQL Server)
There is nothing special about this mappings except the order they
were created (i.e. 3rd Col Mapped, 2nd Col Mapped, 1st Col Mapped,
4th.. 5th... so on). Since 3rd column mapped
first it was assigned DTSTransform_1. If you have the following
case then using MS Migration Wizard will require you to re-create
all the flat file columns and Mapping inside the DataFlow.
DTS xChange will handle cases like below by going through several
checks to determine validity of Flat File Column order.
|
Test Case : Mapping
in DTS Package [Flat File => SQL Server]
|
|
MS DTS
Migration Wizard
|
DTS
xChange
|
|
|
|
|
|
 |
|
| 6. |
Connection |
|
Flat File Connection (Complex
Mappings) |
If flat file columns are mapped using one-one and many-many mapping
in a single DataPump then most probably your Flat File Connection
Migration will fail if you using MS DTS Migration Wizard. DTS xChange
has complex algorithm which goes though several level of checks
to create most accurate information about your flat file connection.
Please check the couple of test cases which will not work with MS
DTS Migration wizard but it will work fine if you use DTS xChange.
|
|
|
|
| 7. |
Connection |
|
Flat File (When Header rows
are skipped) |
You can use "Skip Row" setting in DTS when you don't want to load
couple of staring lines from the flat file. These lines could be
data rows or some unstructured information which you don't want
to process to avoid any possible errors. When you migrate Flat File
connection with MS DTS Migration Wizard then your connection will
only migrate successfully if you skipping data rows with exact same
number of columns. If you trying to skip some unstructured lines
from flat file then you are in trouble, your connection will not
Migrate.The following example shows comparison
between MS DTS Migration Wizard and DTS xChange.
|
Sample File used for
test case
DTS Flat File Connection Properties
|
|
MS DTS
Migration Wizard
|
DTS
xChange
|
|
Notice that First Record has been skipped which really should
not.
|
All Rows are Parsed Correctly
|
|
|
|
|
| 8. |
Connection |
|
Flat File/Other Connections
- NULL Handling (Only when Column of string datatypes mapped to
Numeric, DateTime, Boolean or any other non string columns)
|
You will rarely face NULL Handling issues in DTS 2000 Package because
everything in DTS is implicit, which means DTS will always go ahead
and convert data type of your data to match with Target Column Data
Type regardless you really need it or don't need it. This gives
you ease of use because you don't have to deal with type conversion
but with the price of performance.On the
other hand SSIS uses Explicit Conversion which means SSIS will not
do type casting of 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 is highly possible that you may or may not have data in all columns
or rows when loading data from source. Consider the following scenario
if you have empty value or spaces only (generally Fixed width File
has spaces when no data found in the column)
With SSIS when any source column of string data
type (i.e. varchar, nvarchar, text, ntext ... etc.) mapped to non-string
data type and data coming from source has blank values or Spaces
then most likely DataFlow will through an error. DTS always convert
blank string to NULL but SSIS does not do that automatically. In
SSIS you have to write complex expression to convert blank values
or spaces to NULL.
MS DTS Migration Wizard does not support NULL handling and you will
have to modify Data Flow to take care of various issues related
to NULL. Good news is all tedious and time consuming work will be
done for you if you use DTS xChange to Migrate DTS Packages. Please
review the following examples how DTS xChange helps you save significant
amount of development/testing time.
|
|
|
|
| 9. |
Connection |
|
SQL Native Client
Support |
MS DTS Migration Wizard does
not support SQL Native Client. Which means if you have used
SQL
Native Client Provider in DTS Package then connection will not migrate
at all and all task(s) using that connection must be re-configured.
DTS xChange supports SQL Native Client so any connection using SQL
Native Client will work without any issue when it's migrated using
DTS xChange. |
|
|
|
| 10. |
Connection |
|
Fixed Width Flat File Destination
(Last Column Marked as Quotable using Text Qualifier) |
In DTS When you dealing with Fixed width Flat File you don't have
to worry about TextQualifier for columns because they are only used
for Delimited file. But by default all string datatype columns of
flat file are created with "Quotable" setting and Numeric/DateTime
with "Not Quotable". If you have last column with any of string
datatypes then MS DTS Migration Wizard will generated in proper
data in the fixed width file. Please check the following comparison
between MS DTS Migration Wizard and DTS xChange output.
|
Sample Properties of
DTS Fixed Width Flat File Destination
|
|
MS DTS
Migration Wizard
|
DTS
xChange
|
|
Not Correct: Quoted last column
in Fixed Width File
|
|
|
|
|
|
| 11. |
Connection |
|
Consolidate Duplicate Connections
(i.e. Connections with Same Attributes) |
If you have worked with DTS then you probably know that there is
no way in DTS where you can create connection once and used multiple
times in many data flow tasks. This limitation is gone in SSIS because
you can create connection once and reuse many times in several DataFlow
tasks. But when you use MS DTS Migration Wizard you can not leverage
this new powerful SSIS feature.
Well if you using DTS xChange then you don't have to worry about
this anymore. DTS xChange has a rule called "Consolidate Connections".
This rule compares connection attributes and creates a single connection
for all connection which have same attributes (i.e. ServerName,
UserName, Password, Database etc.)
Check the following example
where all SQL Connections are consolidated to one connection because
they all point to same server and same database with all other
attributes matching.
|
Sample DTS Package
|
|
MS DTS
Migration Wizard
|
DTS
xChange
|
|
|
|
|
|
|
|
| 12. |
Variable |
|
Variable with special characters
(i.e. @, $, % etc.) |
In DTS there is no restriction on how you can name a variable. Which
means you can use any special characters like @, $, % etc. along
with Alpha Numeric characters. On the other hand SSIS is very restrictive
about variable naming and you can only use AlphaNumeric characters
and underscore("_").
MS DTS Migration wizard will fail if you have used any special character
in variable name (i.e. @MyFilePath). DTS xChange solves this issue
by cleaning any invalid characters and creating a valid SSIS Variable
Name. DTS xChange also replaces invalid variable name from the following
tasks.
- ActiveX Script
- Execute DTS Package Task (inner/outer variable)
- Execute SQL Task (Variables used in Parameter Mappings)
- Dynamic Properties Task
|
|
|
|
| 13. |
Task |
|
Dynamic Properties Task |
In DTS Dynamic Property Task is used to configure Variables, Connections
or Tasks at run time.
There is no equivalent task in SSIS but there are other ways to
change properties at runtime in SSIS using expressions. But still
many features of Dynamic Properties is not available in
SSIS
(e.g. reading from Data File, Reading From INI). DTS xChange generates
all necessary code for you to Migrate DTS Dynamic Properties Task.
DTS xChange Creates Script Task and Set of SSIS Variables in order
to Migrate same functionality of Dynamic Properties Task from DTS.
MS DTS Migration Wizard does not support Dynamic Property Task but
if you use DTS xChange then all the functionalities of Dynamic Properties
Task will be migrated successfully.
Reading Values From
------------------------
- INI
- Environment Variable
- Data File
- Constant
- Global Variable
- Query
Setting Value To
------------------------
- Global Variable
- Task Property
- Connection Property
MS DTS Migration Wizard does not migration
Dynamic Properties Task.
|
|
|
|
| 14. |
Task |
|
FTP Task (Overwrite = True) |
MS DTS Migration
Wizard does not migrate "Overwrite" property when it's migrated to SSIS so you have manually change the setting after migration is
completed. |
|
|
|
| 15. |
Task |
|
|
|
| 16. |
Task |
|
Bulk Insert Task (When row
delimiter is specified) |
In DTS when you use Bulk Insert Task you have two options to specify
the layout of the data file.
1. Specify Format File
2. Use Row Delimiter/Column Delimiter
First option works fine with MS DTS Migration Wizard but when you
select 2nd Option Bulk Insert Task in SSIS will not work and you
have to manually configure the task after spending time for troubleshooting.
DTS xChange Successfully Migrate Bulk Insert Task and it will work
fine with any option you choose in DTS. |
|
|
|
| 17. |
SSIS Features |
|
Auditing Framework Support |
DTS xChange comes with out of the box full auditing of SSIS Package execution. Ao get support for auditing all you
have to do is select "Add Auditing Framework" rule and all hard work will be done for all migrated packages. DTS xChange comes
with SSIS Report Viewer application to analyze Trend and Detail about every single package execution which has Auditing
Framework Support.
You can audit the following information using reports provided with SSIS Report Viewer Application
- Which packages are currently running and which task is running inside the package.
- Historical package execution detail for selected date range (i.e. Run time, Errors, Warnings etc.)
- Error and Warning by Task and Package
- Run time by Task and Package
- Extracted and Loaded Records along with their source and target information (e.g. Table/View, Sql Query, File Name, Component Name,
Data Flow Name, Connection String etc.).
- Run time Trend for several days/weeks/years by Package and Task
- Error/Warning/ Trend by Package and Task
- Extract/Load Trend by Package, DataFlow
- Extract/Load Trend by Data Object (e.g. File, Table/View or Sql Query)
The following screen shows various options you can specify to configure Auditing Framework.
|
|
|
|
| 18. |
SSIS Features |
|
SQL Server Logging (SSIS Built-In
Provider) |
Logging to SQL Server using built in Logging Provider is another
way to capture various package/task events with some useful information.
Here are the key differences between "Logging Through Event Handler"
and "Logging to SQL using Built-In Provider" are as below.
- If you choose to use "Logging
Through Event Handler" Rule then you can capture some really important
Audit data e.g. PackageName, IsInteractiveMode
(i.e. Package called from Job or SSIS Designer) and another huge
advantage is you can utilize Operational Data Warehouse shipped
with DTS xChange to perform Trending Analysis.
- If you choose
to use "Logging Through Event Handler" Rule
then you can use additional components provided
with DTS xChange to build Operational Data Warehouse,
which includes Operational Data
Warehouse, Excel Reports, Reporting Services Reports,
Cubes etc.
|
Configuration screen
for Logging to SQL Server Rule (Built-In Logging Provider)
|
|
|
|
|
| 19. |
SSIS Features |
|
Text File Logging (SSIS Build-In
Provider) |
If you want to log various event information of package or task into Text file then
you can turn on "Logging to Text file" rule.
|
Configuration screen
for Logging to Text File Rule (Built-In Logging Provider)
|
|
|
|
|
| 20. |
SSIS Features |
|
Deployment Framework (Easily Deploy Packages to different environment without many changes) |
This is another powerful feature of DTS xCHange. When you turn on
this rule DTS xChange will generate XML Configuration file for Connection
so you can easily modify connection information when without modifying
SSIS package itself. Config files are very helpful when you have
Dev/QA/Prod environment and each environment needs different connection
settings.
There are two ways you can create XML Config files using Config
File Rule in DTS xChange.
1. One Config File for each unique Connection (Shared Config file
across many packages)
2. One Config file for each Package
By default first option is selected. This option will create config
file for selected Connection type and it is used across the packages
using that same connection. For example if you have 50 Packages
and you are using OLEDB connection for Northwind then instead of
50 Config files only one Config file for Northwind connection will
be created and it will be used by all 50 Packages. This option requires
consolidate connection rule turned on and all connections will be
renamed to enforced standard naming convention.
NOTE : When this option is selected the naming
convention for Config file and connection manager is <CONNECTION
TYPE>_<DATASOURCE>_<Counter>
Second option will create one Config file for each Package and they
are not shared with any other package. So if you decide to change
connection information for certain connection then you have to change
it in all Config files having that connection information. This
rule doesn't enforce any naming convention for connection manager
like first option and it also doesn't require Consolidate Connection
Rule Turned On.
|
Configuration screen
for Dynamic Connection Rule
|
|
|
|
|
| 21.. |
SSIS Features |
|
Wrap Package in a Transaction |
DTS xChange allows you to utilize Transaction Support in
SSIS by
using Transaction Rule. When Transaction Rule is enabled in DTS xChange and if any failure occurs during package execution then
rollback operation will be performed to restore original state of
the data before package was executed.
|
Configuration screen
for Wrap Package in a Transaction Rule
|
|
|
|
|
| 22. |
SSIS Features |
|
Full Validation of Package
After Migration |
DTS xChange Perform Full Validation of the Migrated Package if "Validate
Package After Migration" option is selected on the "Verify and Process"
screen. Validation process will find out any potential errors in
the package (e.g. Column Mapped is Missing in the Database, Truncation
Warnings, Performance Enhancement Warnings, Invalid File Path Warning
etc.).
|
Validation Summary after
Migration
|
|
|
|
|
| 23. |
Other Features |
|
Package Profiling and Searching
Capability |
DTS xChange offers a feature called Package Profiler. Here is the
few things you can do using this tool.
1. Estimate the effort required to Migrate selected Package(s) (Time & $$$)
2. Search for Packages containing specific Task(s) (e.g. Search all Packages
with Send Mail task)
3. Find out the % distribution and Total count of various Tasks in the selected
Package(s)
4. Find out the % distribution
and Total count of various Connection Types in the selected Package(s)
5. Save Report in PDF/Excel
|
|
|
|
| 24. |
Other Features |
|
View/Save/Delete Migration
History |
In the past with MS DTS Migration
Wizard there was no way to see detailed log of Migration/Validation.
DTS xChange takes care of this problem. Every time Migration is
completed DTS xChange creates log files for Migration summary and
Validation Summary so you can review it any time you want by choosing
View Log option.
|
View Migration History
|
|
|
|
|
| 25. |
Usability |
|
Remember Application Settings |
MS DTS Migration Wizard does not save any settings after you close
the application which means you have to re-enter everything including
source and target information, list of packages. This become frustrating
if you are using Wizard frequently.
DTS xChange takes care of old problem and it saves most of setting
you enter or select including
- Source Connection Information
- Target Connection Information
- Package list selected for Migration
- Rules selected for Migration
- Window size and location |
|
|
|
| 26. |
Usability |
|
Multiple DTS Files selection
(i.e. COM Structured Files) for Migration |
If you using MS DTS Migration Wizard and you are migrating dts files
to SSIS Packages then you will be little disappointed to know that
MS Wizard does not allow you to choose more than one file for migration.
Using DTS xChange you can select and Migrate as many dts files as
you want.
|
MS DTS
Migration Wizard
|
DTS
xChange
|
|
|
|
|
|
|
|
| 27. |
Usability |
|
|
|
| 28. |
Usability |
|
Support for Re-Process Packages
without Re-Selecting same options again |
In DTS xChange it's also possible to re-migrate all selected packages
by just one click. If you using MS DTS Migration Wizard then you
have to close the Wizard completely and select same source/target
again.
|
|
|
|
| 29. |
Usability |
|
Support for going Back from
any step of the wizard |
In the MS DTS Migration Wizard once you are done with Migration
it is not possible to go to the previous screen to select new packages
for migration which means every time you want to migrate new package
you have to completely close the wizard before you can select new
package(s).
DTS xChange is very flexible and it allows you to go back and forth
from any step of the wizard and you can also go to the first screen
from any step by just clicking on the reload button
 |
|
|
|
| 30. |
Usability |
|
|
|
| 31. |
Usability |
|
Better progress feedback using
Migration Counters, Labels and Progressbar |
DTS xChange Provides rich information during migration so user can
see exactly what is going on behind the scene.
|
Migration Progress Screen
|
|
|
|
|
| 32. |
Usability |
|
|
|
| 33. |
Other Features |
|
Data Conversion Support |
You will rarely face Data Conversion issues when you use DTS because
DTS uses implicit conversion, which means DTS will always go ahead
and convert data type of your data to match with Target Column data
type regardless you really need it or don't need it. This gives
you ease of use because you don't have to deal with type conversion
but with the price of performance.On the
other hand SSIS uses Explicit Conversion which means SSIS will not
do type casting of 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.
During the DataPump migration DTS xChange check
DataTypes of Source and Target Columns and it automatically adds
Data Conversion Transformation if Type Casting is required to handle
explicit conversion. MS DTS Migration Wizard also has similar process
of adding data conversion support but many cases of Data conversion
not supported by MS DTS Migration Wizard
which includes any of the following cases
DT_TEXT => DT_STR
DT_TEXT => DT_WSTR
DT_NTEXT => DT_STR
DT_NTEXT => DT_WSTR
DT_STR => DT_TEXT
DT_STR => DT_NTEXT
DT_WSTR => DT_TEXT
DT_WSTR => DT_NTEXT
NOTE: All above data types are SSIS Specific
data types. DT_STR is equivalent to varchar or char data type in
SQL Server and
DT_WSTR (Unicode) is
equivalent to nvarchar or nchar.
All above cases are supported by DTS xChange.
Below is the screenshot of sample Data Conversion Transformation
added by DTS xChange to handle conversion of Non-Unicode data to
Unicode data.
|
Data Conversion in Data
Flow
|
|
|
|
|
PDF
Version (1089 KB) |
Word
Version (442 KB)
