DTS xChange Help - DTS to SSIS Packages Convert, ActiveX Migrate, SQL Server Upgrade FREE ...

170 Pages·2010·2.78 MB·English

by  Nayan Patel

DTS xChange Help

Table of contents

1. Welcome to DTS xChange
1.1 Product Overview
1.2 What's New
1.3 Installation Requirements
1.4 Licensing Options
1.5 Planning DTS to SSIS Migration
1.6 Running Packages on 64 Bit Machine
2. DTS xChange Reference
2.1 About DTS xChange
2.2 Option Screen
2.3 Specify Source and Target Screen
2.4 Select Packages Screen
2.5 Select Migration Options and Best Practices Screen
2.5.1 About Migration Options and Best Practices
2.5.2 Deployment Framework
2.5.3 Convert Child Packages
2.5.4 Auditing Framework
2.5.4.1 About Auditing and Monitoring Framework
2.5.5 Consolidate Connection Managers
2.5.6 Logging to SQL Server
2.5.7 Logging to Text File
2.5.8 Add CheckPoint
2.5.9 Wrap Package in Transaction
2.5.10 NULL Handling
2.5.11 Codepage Settings
2.5.12 Handle Unsupported Data Providers
2.5.13 DataFlow Options
2.5.14 Other Options
2.6 Verify and Migrate Packages Screen
2.7 Migration Summary Screen
2.8 View Migration and Validation Log Screen
2.9 View Migration History Screen
3. DTS Profiler Reference
3.1 About DTS Profiler
3.2 Option Screen
3.3 Select Source Screen
3.4 Select Packages Screen
3.5 Enter Estimate Screen
3.6 Profiler Summary Report
3.7 Profiler Detail Report
4. Auditing and Monitoring Framework Reference
4.1 About Auditing and Monitoring Framework
4.2 SSIS Execution Dashboard
4.3 Package Execution Trend Report
4.4 Errors/Warnings Report
4.5 Extract/Load Detail Report
4.6 Extract/Load Trend Report
4.7 Running Packages Report
4.8 Recent Executions Report
5. Migration Reference
5.1 Task Migration
5.1.1 About Task Migration
5.1.2 ActiveX Script Task
5.1.3 Data Pump Task
5.1.4 Data Driven Query Task
5.2 Connection Migration
5.2.1 About Connection Migration
5.3 Variable Migration
5.3.1 About Variable Migration
6. Common Errors/Warnings Reference
6.1 Errors
6.1.1 E0001 - Cannot Aquire Connection From Connection
Manager
6.1.2 E0002 - Invalid class string
6.2 Warnings
6.2.1 W0001- Truncation may occur
6.2.2 W0002- Removing unused columns may increse
performance
7. Frequently Asked Questions
7.1 Licensing and Product FAQs
7.1.1 FAQ - How can DTS xChange save time and money
7.1.2 FAQ - Is there any 3rd party tool available in the market
other than DTS xChange to help with DTS to SSIS migration
7.1.3 FAQ - Common product registration Issues
7.1.4 FAQ - How to activate the product
7.1.5 FAQ - Different licensing options with DTS xChange
7.1.6 FAQ - How to extend trial period
7.2 DTS and SSIS FAQs
7.2.1 FAQ - How to pass parameters to DTS or SSIS package
7.2.2 FAQ - What's new in SSIS 2008
7.2.3 FAQ - How to convert Scripting.FileSystem object of
ActiveX Script to native SSIS Task
7.2.4 FAQ - How to convert ADODB object of ActiveX Script to
native SSIS Task
7.2.5 FAQ - How to loop through files in a specified folder, load
one by one and move to archive folder using SSIS
7.2.6 FAQ - How to send HTML email in SSIS
7.2.7 FAQ - How to migrate OLAP Task from DTS to SSIS
7.2.8 FAQ - How to make my SSIS connections dynamic so I can
easily switch to different environment (e.g. Dev, QA, Prod ...)
7.2.9 FAQ - How to read/write variable value from child/parent
package in SSIS
7.2.10 FAQ - What are the advantages/disadvantages of storing
SSIS packages to MSDB vs File System
7.2.11 FAQ - Why should I migrate DTS packages to SSIS
(advantages of SSIS over DTS)
7.2.12 FAQ - Which data providers are supported in SSIS
7.2.13 FAQ - How to Enable/disable Task at runtime In SSIS
7.2.14 FAQ - How to run SSIS Packages using 32-bit drivers on
64-bit machine
7.2.15 FAQ - How to connect Integration Services running on a
remote machine
7.2.16 FAQ - Do I need multiple connections in SSIS for parallel
processing
7.2.17 FAQ - How to replace DTS Looping code to native SSIS
7.2.18 FAQ - How to modify task and connection properties in
SSIS at runtime
7.2.19 FAQ - How to migrate complex DataPump with ActiveX
Script Transformations
7.2.20 FAQ - How to migrate Data Driven Query Task to native
SSIS
7.2.21 FAQ - How to convert ActiveX Script to native SSIS
7.2.22 FAQ - How to call SSIS package from code and pass
variable value
8. Support
8.1 Contact Us
8.2 Other Reference
8.3 Copyrights
1. Welcome to DTS xChange
DTS xChange Help System v4.1.2

Last Updated on 10/16/2009
© 2009 Pragmatic Works, Inc. ALL RIGHTS
RESERVED.

[TOP]
1.1 Product Overview
DTS xChange is an enterprise ready tool to convert DTS packages to SSIS 2005/2008 with very little
manual effort. DTS xChange will automatically convert most of your tasks, variables, connections and
any other package level settings automatically. DTS xChange offers 3-Phase approach to convert your
DTS packages.

Profile – DTS xChange Profiler helps you estimate your migration project in hours and dollar cost
whether you choose to use an automation tool or not.

Convert – DTS xChange will migrate your packages with minimum efforts, applying rules to each DTS
package as it migrates them to enforce best practices.

Monitor – SSIS Report Viewer is a powerful and very easy to use Auditing Tool to Audit Packages
migrated (with Auditing Framework) using DTS xChange. Using SSIS Report Viewer you can do real
time package execution analysis in detail or trend view. It contains a series of reports to view errors,
warnings, extracted/loaded record count, run time info for package and tasks, Machine Name, User
Name and many more.

Note:
Convert : Quick Start video of DTS to SSIS Migration Profile : Quick Start video of DTS Profiler
Figure: 3-Components of DTS xChange
Note:
DTS xChange can apply auditing framework only during conversion process. To re-apply auditing
framework to converted SSIS packages or newly created SSIS packages you have to use BI xPress®
(Sold Separately). BI xPress® also comes with few additional features other than auditing framework
(e.g. Notification Framework, Package Validation and Package Deployment etc.).

Figure: High level overview of DTS xChange architecture
DTS xChange Features
 Converts hundreds of DTS packages to SQL Server Integration Services (SQL Server 2005
or 2008) in moments
 Validates packages after execution to ensure that they will work upon execution
 Deploys packages automatically to SQL Server 2005 or 2008
 Handles nearly all tasks except for MSMQ and Data Driven Query Tasks
 Applies a series of rules on DTS packages:
o Logging to text or SQL files
o Enable checkpoints
o Enable transactions
o Logging through event handlers into a robust event table
o Migrate children packages automatically
o Consolidate duplicate connections
o Create configuration files automatically
o Create sequence containers from parallel tasks
 Profiles packages for cost (man hours and hard cost) of migrations
 Converts SQL Native Client connections
 Can handle ODBC connections as a source
 Convert Complex Datapump and Dynamic Properties Task
 Convert UDL files to connection managers
 ActiveX Script Tasks are migrated to ActiveX Script Tasks in SSIS. Use the Profiler to
determine quickly which objects are created inside the tasks and determine action items
to migrate the task.
Comparison to the MS DTS Migration Wizard
The below table shows you a comparison between the built in wizard and DTS xChange. To see a
complete comparison list, please see our complete comparison documentation.

PDF Version (1089 KB) | Word Version (442 KB)
DTS xChange DTS Migration Wizard
Feature
Support Support
Conversion of Execute SQL Task

Conversion of Execute Process Task

Conversion of Data Pump Task (Sometimes)

Conversion of Dynamic Properties Task

ODBC Support as a Source

UDL File Support

Password protected Access Database

Flat File that doesn't map all columns

Data conversion between source and
destination

SQL Native Client support

Full package validation after migration
Detailed logs of conversion

Enterprise rules for migration to get the benefit
of SSIS

SSIS logging turned on

Checkpoint file support

Children package migration

Profiling capability

[TOP]
1.2 What's New

DTS xChange Version History

Version 4.1.2 [Oct 16, 2009]
 Added : Currency option for Profiler report
 Added : New checkbox on the "Other Options" screen to enable/disable auto correction of Flat
File column order
 Fixed : When Package Level Transaction is enabled then in the event of failure all auditing
information logged by Auditing Framework is also rolled back.
 Fixed : Certain system activity cause.
 Fixed : Sometime DTS xChange hangs while populating Flat File Destination Mappings.
 Fixed : If Package Name contains invalid characters package migration fails.
 Fixed : FlatFile Destination columns are generated in different order sometimes.

Version 4.1.1 [Apr 25, 2009]
 Added : All Script Tasks created by DTS xChange are now PreCompiled. With this version you
will see 10-20 % Faster response for auditing framework.
 Added : DTS Profiler report shows packages associated with Task Type in the Task Summary
section.
 Added : DTS Profiler report shows packages associated with Connection Type in the
Connection Summary section. conversion.
 Fixed : "Truncation may occur" warning is no longer generated for flat file source. DTS
xChange automatically adjust source column length metadata based on target column length
(Only when source is flat file)
 Fixed : When Execute Package Task is pointing to (local) or localhost then sometimes DTS
xChange fails to convert child package (this happens only when migration is performed on a
different machine).
 Fixed : Text File Logging and SQL Server Logging doesn't work for SQL Server 2008.
 Fixed : Profiler report export to excel format fails.
 Modified : Previous version didn't consider Nullable Flag and created NULL handling even
though target column doesn't allow NULL. This version provides an option on the NULL
handling screen to change default behavior. Now by default NULL Handling is only generated
when Target column is Nullable.

Version 4.1.0 [Jan 27, 2009]
NOTE : This version requires activation after installation. Please keep your S/N ready when prompted.
You can view your current serial number by launching DTS xChange and click Registration Information
at the top. Ignore this NOTE if you are running Trial version.
 Added : [Major Feature] Context Sensitive Help Integration.
 Added : New popup will be displayed after conversion. It will show error/warning summary
and Migration related help links.
 Added : Redesigned UI for clean look.
 Added : ActiveX Script replacement warnings are displayed with relevant help.
 Added : On the option screen "Latest from Pragmatic Works" section added. This will display
new product release, bug fixes, common question/answers and other useful DTS/SSIS
productivity news.
 Added : Migration report and log file will be added under "Miscellaneous" folder in the SSIS
Project when "View Packages" button is clicked.
 Added : "Migration Reading Material" links are automatically displayed after migration. These
links are suggested based on various patterns discovered during migration (e.g. How to
replace ActiveX using DTS Object Model Apis).
 Fixed : Remember Transaction Isolation Level.
 Fixed : Profiler doesn't validate missing Backward compatibility pack for SQL Server 2008
 Fixed : Folder path is not validated when you select SQL Server as target.
 Fixed : Package stored on SQL Server containing dot "." in the package name is saved with
incorrect file name (e.g. accouting.nighly.step1 will be saved as accouting.nighly.dtsx instead
of accouting.nighly.step1.dtsx).
 Fixed : Child package migration is failed in some cases when DTS package is modified to use
windows authentication from mixed mode in the child package connection.
 Fixed : If (local) or localhost is specified for child package server attribute then DTS xChange
fails to load child package when package is stored on a different machine..
 Fixed : Sometimes when you click Next button on "Select Source/Target" screen you may
receive an error regarding connection failure for Auditing Database.
 Modified : In the new version details for running packages is expanded by default in the
"Running Packages" and "Recent Executions" reports.
Version 4.0.6 [Jan 7, 2009]
 Added : 64bit OS support added for DTS xChange and DTS Profiler which means you can
install on 64bit OS (including XP, Vista).
 Added : Now you can profile multiple servers and folder location to generate one report for all
packages.
 Added : Now you can include/exclude packages which contain certain tasks selected in the
Task filter list.
 Added : Discover SQL Servers option added. This option will find SQL Servers running on the
local network.
 Added : Expand package detail option added. When this option is checked package detail will
be expanded when report is generated.
 Added : Remember recently entered/discovered server names.
 Added : Ability to stop Profile action by pressing "ESC" key added.
 Added : Checkbox option added to view all previously migrated packages.
 Fixed : Installation on Vista is corrupted if you don't run setup using "Run As Administrator"
option.
 Fixed : When you install new version all previous settings are wiped out (e.g. server name,
user name...).
 Fixed : Duplicate or unnecessary 64-bit warnings are displayed for Script Task.
 Fixed : If you enter spaces at the end of SMTP Server name during Send Mail Task migration
then you may receive error and package may not be converted.
 Fixed : Package with Dynamic Properties Task doesn't work some times.
 Fixed : DTS Profiler shows wrong numbers sometimes if machine has international settings
(e.g. German).
 Fixed : Job detail listing is not shown sometimes when DTS Package name in commandline is
different than actual name stored in MSDB.
 Fixed : Packages are counted twice in certain cases (this only happens when you apply
multiple S/N on the same machine and activate - deactivate Previous registration multiple
times)
 Modified : Show proper warning about Task Compatibility in SQL2005/2008 if
TransferSQLObject/Database task is used.
Version 4.0.5 [Nov 3, 2008]
 Fixed : When you profile DTS package(s) which have one or more Custom Tasks not
registered then you may receive confusing error message "Step 'DTSStep_XXXX_XXX' already
exists in the collection".
 Fixed : 64bit Warnings are displayed for event handlers even though there is no script task
added.
 Fixed : Sometimes you may get an error "Invalid file name" while profiling DTS packages.
 Fixed : Sometimes you may get an error "Multi version package requires GUID or Name"
while profiling DTS packages.
 Fixed : You may see target platform listed in the dropdown even though you don't meet
minimum requirements and when you migrate packages you get error without any useful
message.
 Fixed : For target platform "SQL Server 2008" If you already installed Backward Compatibility
Components you may still receive warning that you don't have Backward Compatibility
Components.
 Modified : Some non critical warnings are now marked as information.
 Modified : Migration report is automatically displayed once migration is completed.
Version 4.0.4 [Oct 27, 2008]
 Added : New option added (on the other options screen) to create direct SQL statements for
execute SQL task instead of using expression based variable approach. In the previous version
there was no setting to change this default behavior.
 Added : Two new reports (Execution dashboard and Execution Alerts) added.
 Added : Migration summary panel added on the migration status screen which displays Total
Migrated Variables, Tasks, Connections, Precedence, Packages, Child Packages and Total
Removed Duplicate Connections.
 Added : If DTS/SSIS Runtime is not installed then valid message will be prompted instead of
"Object reference not set" error.
 Added : ODBC Destination warning added for SQL Server 2005 and Full ODBC Destination
Support added for SQL Server 2008.
 Added : Remember password option added for encrypted packages so you don’t have to enter
password many times during migration or profile process.
 Fixed : Sometimes you may get an error "Exception from HRESULT: 0xC0204006"
 Fixed : Multiple connections are generated if Provider attribute is SQLNCLI or SQLNCLI10.
 Fixed : Every time product is updated Activation dialogbox appears and your are forced to
submit serial number and other information. This bug is fixed and any upgrade after
version 4.0.4 should not ask for activation unless its new registration or hardware profile is
changed.
 Fixed : User ODBC DSN is not recognized and "Missing DSN" error is reported in the migration
summary report.
 Fixed : You get an error after migration "Can not find Log file Migration - XXXXXX.dat" when
you enter some reserved characters in Migration Title.
 Modified : Dynamic Properties with Assignment from Query Result creates more secure code
without plain text password in the script task.
 Modified : By default only errors/warnings are visible instead of full detail log in the migration
summary.
 Modified : Connection consolidation improvement for ODBC connections so less number of
connections are created compared to previous version.
Version 4.0.3 [Sep 4, 2008]
 Added : Now you will be prompted to copy the serial number before any upgrade.
 Fixed : Unused columns for Flat File Source not removed from the pipeline which slows down
performance.
 Fixed : Profiler report doesn't display activex script lines in the task distribution summary.
 Fixed : Converting packages to SSIS 2008 throws error "Can not load
DTS.DTSPackagePriorityClass" .
Version 4.0.2 [Sep 2, 2008]
 Added : More Accurate Estimate for ActiveX script conversion based on Length of Source
code.
 Added : Color coding added in profiler report for Fully Migrated Tasks, Partially Migrated
Tasks and Tasks which can not be migrated.