| DTS xChange Help System | |
|
 Watch
Quick Start Video (10 Minutes) |
 View
DTS to SSIS Migrations FAQs |
|||
 View
Screenshots |
Compare
MS Wizard |
||||
Request Pricing |
 Download
Datasheet |
[TOP]
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 |
|
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 SSIS xPress® (Sold Separately). SSIS xPress also comes with few additional features other than auditing framework (e.g. Notification Framework, Package Validation and Package Deployment etc.). |
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)
| Feature | DTS xChange Support | DTS Migration Wizard 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]
[TOP]
|
Note: |
|---|
| Backward Compatibility Components are not installed by default during SQL Server 2008 Installation so make sure you have that selected during setup. You can click advanced options and select Backward Compatibility components. |
1. Check if BIDS (Business Intelligence Development Studio) installed ?
To check if BIDS is installed open Start -> All Programs -> Microsoft SQL Server 2005 or 2008. You should be able to see Business Intelligence Development Studio icon if it is installed.
2. Check
if SSIS Components Installed?
To check if necessary components to create and run SSIS packages installed, perform the following steps.
3. Check if Backward Compatibility Components installed?
To check if Backward Compatibility Components installed perform the following steps
|
Note: |
|---|
| If above test failed and you are getting components missing error then
please run the SQL Server 2005/2008 setup and install Backward
Compatibility components or download from the following URL (For 32 bit OS
use x86 link). Microsoft SQL Server 2005 Backward Compatibility Components The SQL Server Backward Compatibility package includes the latest versions of Data Transformation Services 2000 runtime (DTS), SQL Distributed Management Objects (SQL-DMO), Decision Support Objects (DSO), and SQL Virtual Device Interface (SQLVDI). These versions have been updated for compatibility with both SQL Server 2005 and SQL Server 2008 and include all fixes shipped through SQL Server 2000 SP4 and SQL Server 2005 SP2.  X86
Package (SQLServer2005_BC.msi) - 11273 KB X64
Package (SQLServer2005_BC.msi) - 18569 KB IA64
Package (SQLServer2005_BC.msi) - 23510
KB |
[TOP]
How to transfer license to different
machine?
DTS xChange is very flexible in terms of licensing. If
you ever decide to transfer your license to other machine then perform the
following steps. Transferring license requires Deactivate product from one
machine and activate on the other machine with same serial number.
Should I install DTS xChange on single machine or multiple machines?
You can install DTS xChange on single machine or
multiple machines depending on how many people need access to DTS xChange. Please review the following figures on both scenarios. If you are
installing DTS xChange on multiple machines then please make sure you split your total package migrations in multiple Serial Numbers instead of using
one Serial number for all packages (You can always ask customer service before your actual purchase. There is no additional charge to split you serial
number but it has to be requested before your purchase. Once serial number is issued it can not be cancelled).
[TOP]
Use DTS Package Profiler to plan your migration project
DTS to SSIS migration can be challenging so make sure you perform all necessary planning before you start actual conversion. You can use DTS Package Profiler to find out how many packages you have and which packages are duplicate. You can also Define priority to each package and assign developer who will be responsible for package migration. Perform the following actions to get started with you migration project.|
Note: |
|---|
|
|
Check security rights
Check data source connectivity
Most of DTS packages deal with one or more data sources to extract/load data or to execute DDL/DML statements. DTS xChange makes some smart adjustments in SSIS packages based on metadata (e.g. column name, size, precision etc.) provided by datasource. If metadata is not validated due to connectivity issue during migration then DTS xChange will use offline settings stored in DTS package but this can cause unexpected behavior some times (e.g. invalid data type is assigned to columns).Check drivers and ODBC DSN
If you have DTS package using multiple data sources other than SQL server than you need to check the following items|
Note: |
|---|
| Click here to get the full list of supported and unsupported drivers in SSIS. |
Standardize naming convention and folder structure
SSIS Packages can have many dependencies. Most common dependencies are Configuration files, Log files and Checkpoint Files. It is important that you try to keep same folder structure and path if possible across the same environment because all paths for listed dependencies are hard coded in Package and because of that if you move package from one environment to other and same path does not exist then package may fail to execute.|
Note: |
|---|
| Use SSIS xPress® to Deploy SSIS packages from one environment to another using Deployment feature. This feature allows to deploy multiple SSIS packages easily along with all dependencies. You can also change Config and CheckPoint path during deployment process. |
[TOP]
32-bit tools run by default: When the 32-bit tools are also installed, the path to the 32-bit tools appears BEFORE the path to the 64-bit tools in the PATH environment variable. Therefore, if you just type "dtexec" at the command prompt, for example, you are running the 32-bit version of the tool. Your options are to type the full 64-bit path, make the 64-bit directory the current directory first, or change the order in the PATH environment variable. Our testing has not shown any negative side-effects to changing the order of the PATH, but I make this suggestion cautiously.
Features that DON'T work on any 64-bit operating system (x64 or Itanium) DTS: You cannot run DTS packages in 64-bit mode on x64, or at all on Itanium. Therefore you also cannot use the Execute DTS 2000 Package task in these circumstances. Of course on x64 you can run DTS packages, or SSIS packages that run DTS packages, in 32-bit mode, after manually installing the optional DTS run-time support.Excel, Access, Jet: You have to run packages that use the Jet provider (Access and Excel) in 32-bit mode.
SQL Server Compact: You have to run packages that use the SQL Server Compact provider in 32-bit mode.
Logging to SQL Server Profiler: You can only use package logging to SQL Server Profiler in 32-bit mode.
[TOP]
|
Note: |
|---|
| Convert : Quick Start video of DTS to SSIS Migration
Profile : Quick Start video of DTS
Profiler |
[TOP]
Migrate:
This option will launch
wizard for DTS package migration. Using this option you can convert DTS packages
to SSIS 2005/2008. It will also apply series of best practices during migration
and at the end of the migration you will see full migration log and validation
report of converted packages for possible errors/warnings.
| Note: |
|---|
| Using Trial version you can convert maximum 3 packages. If you are converting DTS package which is calling other package(s) then parent package and child package(s) will be counted as separate migrations. |
Profile:
This option will launch DTS
Package Profiler Wizard. Profiler option is helpful to find out how many
packages you have and how complex they are to convert manually or using DTS
xChange. DTS Profiler will generate detailed report with package and task level
break down which you can export to PDF or Excel.
Reports:
This option will launch Report Viewer
Application. This is the place where you can view several predefined reports for
SSIS package auditing.
Log:
This
option will show history of all previous migration occurred on current machine
where you have installed DTS xChange.
Support/Bug:
You can click this URL if you have any
issue using DTS xChange. You can contact support by phone/email or by visiting
http://www.pragmaticworks.com/support
Check for update:
DTS xChange has auto update
feature which will check every couple of days or everytime you launch DTS
xChange depending option you have set. If product update is found then you will
see product update dialog box as below.
Registration:
Click this option to
view license information of your product. You can use same screen to
activate or deactivate license of your product.
[TOP]
On the source/target screen you can specify location of DTS packages and location for converted packages.
Specify where DTS package(s) are stored
Source SQL Server
If your DTS packages stored on SQL Server 2000 or SQL Server 2005 (under legacy mode) then select this option.
You can specify instance name or IP.
| Examples: |
|---|
|
|
Source File System
If DTS packages are stored as COM Structured Storage files (*.dts) then select this option. You have to browse folder location where dts files are
stored. On the next screen you can select packages found under selected folder.
Specify where migrated package(s) will be stored
Target SQL Server
If you want to store converted package on SQL Server 2005 under MSDB database then select SQL Server target. You can also browse specific
target folder of MSDB by clicking browse button.
| Reference: |
|---|
| FAQ - What are the advantages/disadvantages of storing SSIS packages to MSDB vs File System |
Target Platform
This drop down will list all installed versions of SQL Server which you can select as target platform. If you don't see any item in this dropdown
then make sure you install necessary components specified in the requirements section. Click here to
learn more about requirements.
[TOP]
Display Associated Job Detail:
This option will show four additional columns related associated job detail
which includes Job Name, last execution date, creation date and last execution status.
Search for:
This option will filter package list based on search criteria (e.g. to list all packages with word
"Test" enter Test in the filter box and click filter icon). This option is very handy when you have may packages and you don't know exact name of the
package.
Copy to clipboard:
Click this button to save content of top grid into clipboard in Tab delimited values (you
can copy/paste content to excel)
Save report:
Click this button to save content of top grid into Tab delimited values (you can copy/paste
content to excel)
[TOP]
DTS xChange adds value to your converted packages by applying series of options and SSIS best practices. If you are new to SSIS and you are not sure what are the best options for you then leave default selection. To set default values click on the button labeled as "Click here to set Default Settings"
Current version of DTS xChange applies the following best practices.
[TOP]
In SSIS there are several methods of making package portable which includes.
The most common and easy to use method is XML Configuration File. DTS xChange makes your package portable using Deployment framework feature which automatically creates reusable config files to stores connection strings. DTS xChange automatically creates minimum number of unique configuration files and reuses across many packages. This is a really useful feature to save time in deployment process. When you move your packages to different environment (e.g. DEV to PROD) where you have to point all your package connections to new datasources then no worries... Simply edit config files using your favorite XML editor or Notepad, change server name/other connection specific information and all packages using that config file will start using new connection string.
Create config file per connection (shared files):
This is the recommended option and when you select this
option, DTS xChange will try to create minimum number of config files by comparing server name, username and database name attributes of DTS package
connections and existing config files. If config file already created with matching attributes then it will be reused in the newly created SSIS
package. This option will store only one unique connection string per config file.
If "Consolidate connection manager" best practice is selected then connection manager and config file naming convention will be <connection
type>_<database or file name>_<number> (e.g. OLEDB_NORTHWIND_1)
Create config file per package:
Select this option if you have requirement to manage config file per package
and you don't want to share that connection information with any other package. This option will store all connection information used in SSIS
package.
Where config files will be stored:
Specify folder location where new config files will be stored. Try to keep
this location same for all packages you migrate using DTS xChange because during migration process DTS xChange constantly scan this directory to make
some smart adjustments and reuse existing config file whenever possible. During runtime SSIS package will read config file from this location so make
sure when you execute your SSIS package this path exists with all config files used by package.
| Note: |
|---|
| Config file path is hard coded in the package. So try to keep config folder path consistent across all environments otherwise when you
try to execute the package on a different machine it will fail because of different config file path. If you have different path in different environment then use SSIS xPress® to Deploy SSIS packages from one environment to another using Deployment feature. This feature allows to deploy multiple SSIS packages easily along with all dependencies. You can also change Config and CheckPoint path during deployment process. |
Store configuration in separate elements:
This option will create individual XML elements for ServerName,
UserName, Password and Catalog in the config file. If you want to get more control on connection string attributes (i.e. Define Time out or change
Authentication Method) then don't use this option.
Store configuration in a single element:
This is a recommended option. It will store entire ConnectionString
including password into single XML element.
Create configurations for the following types:
Check connection types for which you want to generate config
file. By default all connection types are selected.
| Additional Reference: |
|---|
Making SQL Server 2005 Integration Services Packages Portable |
[TOP]
Please check the Figure 2,3 to see the differences in migration output when "Convert child packages" option is unchecked and checked..
 Figure-2: With convert child package setting OFF |
 Figure-3: With convert child package setting ON |
Default (same as parent location):
Select this option if you want to save converted child package to the same location as parent packages (Target location specified on the "select
source and target location" screen).
Store on SQL Server:
Select this option if you want to save converted packages to MSDB database on SQL
Server 2005/2008.
Store on file system:
Select this option if you want to save converted packages to file system location other than parent package location (i.e. different directory).
[TOP]
Server:
Database server instance name
where you want to store auditing information. This can be SQL Server 2000/2005
or 2008.
Windows Authentication:
If you using
windows authentication to connect to SQL Server then check this option.
User Name:
Enter SQL Server login name.
This option will be disabled if you select windows authentication.
Password:
Enter SQL Server password. This
option will be disabled if you select windows authentication.
Log Source Row Count:
This option will
capture the row count coming from source. Some times this will be also referred
as extracted row count.
Log Destination Row Count:
This option
will capture the row count coming going into destination. Some times this will
be also referred as loaded row count.
Log SQL Statement for Source/Destination:
If this option turned on then sql query written to extract the data
will be logged. If you pulling data from flat file or some other relational
source then Table/View Name will be logged.
Log Connection Information:
This option
will log some useful attributes related to source or target connection which
includes entire connectionstring, filename, servername, catalogname and
username.
| Additional Reference: |
|---|
|
About Auditing and Monitoring Framework |
[TOP]
DTS xChange offers easy way to add rich auditing features in converted
packages using custom auditing framework developed by Pragmatic Works. This
Auditing Frameworks uses all Native SSIS features and it can track
packages in real-time. Auditing Framework comes with lots of predefined reports
which can give you some valuable information.
You can audit the following
information using reports provided with Auditing Framework (Note: Use Report
Viewer Application to view auditing data.)
How does Auditing Framework work?
The following changes will be made to SSIS package when you apply auditing framework. These changes are done based on options you select on the auditing screen.
Here is the basic architecture of Auditing framework.
To view reports you have to use "Reports" option found on the main screen.
There are seven inbuilt reports are shipped this version and more will be added in the future release.
| Report Name | Description |
| SSIS Execution Dashboard | This dashboard gives one place view of most common counters of SSIS package execution. |
| Package Execution Trend | This report provides many useful information about trend of package runtime, task runtime and errors/warnings. |
| Errors/Warnings | Provides detailed error/warning report including package level errors (package level errors not listed in any other report) |
| Recent Executions | Provides information of running packages/tasks and already completed executions. |
| Running Packages | Provides real time information of currently running packages and tasks. |
| Extract/Load Detail | Provides extract/load detail (e.g. row count, source/target information, query, file name ...) about each dataflow in each package execution. |
| Extract/Load Trend | Provides graphical view of extract and load trend over several years, weeks, months and days. |
[TOP]
In DTS in many cases you have to create connections multiple times with same attributes (e.g. Server, User Name and Database Name). This can create maintainance issues because when connection arribute(s) are change you have to update multiple connections. In SSIS you can create connection manager once and reuse in your package with multiple task(s) (e.g. DataFlow Task, ExecuteSQL Task etc.).
DTS xChange takes advantage of this new feature of SSIS and removes any duplicate connections from SSIS package. Any task referring to duplicate connections are adjusted automatically to use consolidated connection.
You can view real time counter of how many duplicate connections are removed on the migration summary panel during migration.
To check which connections have been consolidated you can check migration log in detail view (Check "Show Detail View" found on on the migration log page.).
When you select consolidate connection setting, you may see fewer connections in SSIS package compared to your Original package. Please see the below example.
 Figure-4: With consolidate connection setting OFF |
 Figure-5: With consolidate connection setting ON |
[TOP]
|
Note: |
|---|
| Logging to SQL Server is SSIS native logging provider and
"Auditing Framework" is a custom logging framework
created by pragmatic works using 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 which you don't get when you select "Logging to
text" or "Logging to SQL Server"
option. For more information on Auditing Framework Click Here |
|
Additional Reference: |
|---|
| Implementing Logging in
Packages |
[TOP]
|
Note: |
|---|
| Logging to Text file is SSIS native logging provider and
"Auditing Framework" is a custom logging framework
created by pragmatic works using 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 which you don't get when you select "Logging to
text" or "Logging to SQL Server"
option. For more information on Auditing Framework Click Here |
|
Additional Reference: |
|---|
| Implementing Logging in
Packages |
[TOP]
| Reference: |
|---|
|
Using Checkpoints in Packages |
Using DTS xChange you can easily configure Checkpoints for converted packages. To enable checkpoints make sure "Add CheckPoint" option is turned on as shown below.
[TOP]
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.
You can apply transaction setting by selecting "Wrap Package in Transaction" option as shown below.
|
Note: |
|---|
| Enabling this feature will require that the Microsoft Distributed Transaction Coordinator (MSDTC) service is enabled on each server participating in the package. Whenever possible try to use database native transactions (e.g. 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 requery. |
In
DTS 2000 you had ability to run transactions over our packages that enabled you to fail or succeed whole groups of atomic units of work much in the
same way that you can do using a traditional RDBMS.
| TransactionOption | Description |
| NotSupported | The container will not enlist in a transaction, even if one was started by a parent container. |
| Supported | The container will enlist in a transaction if one was started by a parent container. If a parent container did not start a transaction the container will not be enlisted in any transaction. |
| Required | The container will enlist in a transaction if one was started by a parent container. If a parent container did not start a transaction the container will start one itself. |
|
Additional Reference: |
|---|
| How to Configure
a Package to use Transactions Using Transactions in SSIS |
[TOP]
By default this setting is ON.
Convert Blank [DateTime] to NULL:By default this setting is ON.
Convert Blank [Text] to NULL:By default this setting is OFF.
Convert Blank [Boolean] to NULL:By default this setting is OFF.
Convert column values containing only spaces to NULL:
This setting is generally useful in case of Fixed width file where blank column is represented using several spaces (see Figure-3). If you select this
setting then any column containing only spaces will be converted to NULL.
By default this setting is OFF.
Trim extra spaces for Boolean Value:
What is NULL Handling and why do you need it?
You will rarely face NULL Handling issues in DTS 2000 Packagee 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 automatically which could be unwanted result in many cases. Automatic NULL Handling in DTS gives you ease of use but with the price of performance.
On the other hand SSIS uses Explicit Conversion which means SSIS will not change the value or 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 expressions to convert blank values or spaces to proper NULL.
MS DTS Migration Wizard does not support NULL handling and you will have to modify Data Flow to take care 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 to save significant amount of development/testing time by automatically implementing NULL handling.
Check the following figures which show some sample data which requires proper NULL Handling.
Expressions
The following table shows various expressions generated by DTS xChange. Expression is only generated if column needed NULL Handling.| Setting | Sample Expression and explanation |
| Convert Blank [Numbers] to NULL Or Convert Blank [Boolean] to NULL Or Convert Blank [DateTime] to NULL Or Convert Blank [Text] to NULL Or Convert Blank [Other] to NULL Or Convert column values containing only spaces to NULL |
TRIM(CategoryID) == "" ? (DT_STR,255,1252)NULL(DT_STR,255,1252) : CategoryID Explanation: If source column value is blank then convert 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. (See Figure-3 : 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. |
Below is the screenshot of sample Dataflow converted using NULL Handling.
|
Additional Reference: |
|---|
| Derived
Column Transformation Integration Services Expression Reference |
[TOP]
Default CodePage:
Enter the codepage number which will be used to translate source/target data when "Always Use DefaultCodePage for Non-SQL Server Source or Target"
option is checked (see next two options).
Always use Default CodePage for Non-SQL Server Sources:
This setting will force SSIS to use DefaultCodePage number to translate source characters for Non-SQL Server (i.e. ORACLE, DB2 ...). This setting
insures that when CodePage information is not available from source, DefaultCodePage will be used instead of throwing error.
Always use Default CodePage for Non-SQL Server Targets:
This setting will force SSIS to use DefaultCodePage number to translate target characters for Non-SQL Server (i.e. ORACLE, DB2 ...). This setting
insures that when CodePage information is not available from target, DefaultCodePage will be used instead of throwing error.
Always use Default CodePage for ExecuteSQL Task:
This setting will force SSIS to use DefaultCodePage number for any converted ExecuteSQLTask.
Always use Default CodePage for Flat File Connections:
This setting will force SSIS to use DefaultCodePage number for any Flat File Connections.
|
Additional Reference: |
|---|
| Code Page Identifiers CodePage Supported by Windows |
[TOP]
SSIS is completely re-written and significantly different in many ways. Some Data Providers which used to work fine with DTS may not work as
expected or may not work at all in SSIS. DTS xChange gives you intelligent warning message if Data Provider you are using is not in the Supported
list. If your Driver is not in the supported list doesn't mean it will not work but its not officially tested by SSIS Team at Microsoft.
When you perform migration and if your connection is using Data Provider which is not in the compatibility list then you will be prompted to select new driver or continue with old driver (See Figure-2).
| 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 |
Y | x64, IA64 |
| SQL Server | Microsoft | ADO.NET | 2000 and later versions |
Y | x64, IA64 |
| SQL Server | Microsoft | ODBC | 2000 and later versions |
Y | x64, IA64 |
| SQL Server | Attunity | OLE DB | 2000 and later versions |
Y | |
| SQL Server | DataDirect | OLE DB | 2000 and later versions |
Y | |
| DB2 | Microsoft | OLE DB | All DRDA-compliant versions | Y | 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 |
Y | |
| DB2 | IBM | ADO.NET | DB2 UDB 9.0 | N | |
| DB2 | IBM | ODBC | z/OS and UDB 7.0 and later versions AIX 5.0 and later |
N | N |
| 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 |
Y | |
| DB2/400 | Attunity | OLE DB | On AS/400 5.1 and later versions |
Y | |
| DB2 | DataDirect | OLE DB | z/OS and UDB 7.0 and later versions AIX 5.x AS400 4.5 and later |
N | |
| DB2 | HIT | OLE DB | z/OS and UDB 8.0 and later versions AIX 5.x, AS400 4.5 |
N | |
| DB2 | DataDirect | ADO.NET | z/OS and UDB 7.0 and later AIX 5.x, AS400 4.5 |
Y | |
| DB2 | ETI | Bulk Load | 8.0 and later versions |
Y | |
| DB2¹ | Persistent | Data Flow Component
Bulk Write & Bulk Read |
- | N | |
| Oracle | Microsoft | OLE DB | 7.3.4 and later versions² |
Y | N |
| Oracle | Microsoft | ADO.NET | 8.0 and later versions |
Y | x64, IA64 |
| Oracle | Oracle Corp | OLE DB | 8i and later versions | Y | x64, IA64 |
| Oracle | Oracle Corp | ADO.NET | 8i and later versions | Y | x64, IA64 |
| Oracle | Oracle Corp | ODBC | 8i and later versions | N | |
| Oracle | Microsoft | ODBC | 8i and later versions | N | |
| Oracle | Attunity | OLE DB | 9i and later versions | Y | |
| Oracle | DataDirect | OLE DB | 8i and later versions | Y | |
| Oracle | DataDirect | ADO.NET | 8i and later versions | Y | |
| Oracle | ETI | Bulk Load | 9.0 and later versions |
Y | |
| Oracle | Persistent | Data Flow Component
Bulk Write |
8i and later versions | N | |
| SAP¹ | Microsoft | ADO.NET | R/3 4.6C and later versions |
Y | |
| SAP | Theobald | OLE DB | R/3 | Y | |
| Office Access | Microsoft | OLE DB | 2003 and earlier versions |
Y | |
| Office Excel | Microsoft | OLE DB | 2003 and earlier versions |
Y | |
| Office 2007 | Microsoft | OLE DB | 2007 | N | |
| Sybase | Sybase | OLE DB | 11.5 and later versions |
N | |
| Sybase | Sybase | ADO.NET | 11.5 and later versions |
N | |
| Sybase | Attunity | OLE DB | 12.0 and later versions |
Y | |
| Sybase | DataDirect | OLE DB | 11.5 and later versions |
Y | |
| Sybase | DataDirect | ADO.NET | 11.5 and later versions |
Y | |
| Informix | IBM | OLE DB | 7.3 and later versions |
N | |
| Informix | Attunity | OLE DB | 7.31, 9.x, 10 | Y | |
| Informix¹ | Persistent | Data Flow Component
Bulk Write & Bulk Read |
- | N | |
| Teradata | Teradata | OLE DB | 2.6 and later versions |
N | |
| Teradata | Teradata | ADO.NET | 2.6 and later versions |
N | |
| Teradata | ETI | Bulk Load | 2.5 and later versions |
Y | |
| Teradata | ETI | Bulk Extract | 2.5 and later versions |
Y | |
| FoxPro | Microsoft | OLE DB | 8.0 and later versions |
N | |
| File DBs | Microsoft | OLE DB | Any Jet 4.0– compatible version | N | |
| Adabas | Attunity | OLE DB | 6.2.2 to 7.4.x on z/OS 3.3 to 5.1 on Open |
Y | |
| CISAM | Attunity | OLE DB | On UNIX | Y | |
| DISAM | Attunity | OLE DB | On UNIX, Linux, and Windows |
Y | |
| Ingres II | Attunity | OLE DB | 2 to 2.56 | Y | |
| Oracle Rdb | Attunity | OLE DB | 7.1.x, on OpenVMS Alpha and Integrity (Itanium) |
Y | Y (HP Integrity) |
| RMS | Attunity | OLE DB | On OpenVMS Alpha and Integrity (Itanium) |
Y | Y (HP Integrity) |
| Enscribe | Attunity | OLE DB | On HP NonStop G- Series and H-Series |
Y | Y (HP Integrity) |
| SQL/MP | Attunity | OLE DB | On HP NonStop G- Series and H-Series |
Y | Y (HP Integrity) |
| IMS/DB | Attunity | OLE DB | 6.1 and later versions |
Y | |
| VSAM | Attunity | OLE DB | On z/OS 1.1 and later versions |
Y | |
| LDAP | Persistent | ODBC | All LDAP-compliant servers |
N | Y |
| Note: The following is not a definitive list of all data providers, and not all data providers on this
list have been tested by Microsoft with SSIS. Only current Beta or shipping products are listed. Information about third-party
products was provided by the product vendors and could not be independently verified. Soure : Microsoft.com [http://technet.microsoft.com/en-us/library/bb332055.aspx] Last Updated : 4/1/2008 ¹This product is in beta. ²Although this provider can connect to and use versions of Oracle up to and including Oracle 10gR2, it does not support database constructs (such as BLOB/CLOB data types) introduced after Oracle 8.0). |
[TOP]
Truncation Row Disposition Option:
You can select any of the following option to change the row truncation disposition option. This option determines what action should be performed
when data is truncated. Default action is Fail Component.
Available options are :
1. RD_FailComponent
2. RD_IgnoreFailure
3. RD_RedirectRow
Command Timeout:
You Enter number of seconds before source adapter query/command times out. Enter 0 if you don't want timeout.
Delay Validation:
If you are working in disconnected mode and you don't want to validate dataflow check this option.
[TOP]
Package Protection Level:
You can use this option to change protection level of converted package. Please refer MSDN help to learn more about each protection level and
pros/cons.
Reference URL : http://technet.microsoft.com/en-us/library/ms141747.aspx
Always Use Direct SQL for ExecuteSQL Task:
By default DTS xChange takes some smart decision and use expression based variables for certain type of Parameterized SQL Statements which is
recommended best practice to avoid any Provider specific parameters handling. In some case if you don't want to create expression based variables and
only use direct SQL Statements then check this option.
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 will be
grayed out. In SQL 2008 You can write script task in C# to VB.net. Depending on what language is selected in this dropdown DTS xChange will create
code in the selected language for Script Task during migration.
[TOP]
[TOP]
After migration is completed you can review Migration Log and Validation Log.
What is Migration Log?
Migration Log includes
Informational messages, Migration related errors and warnings reported by DTS xChange.
What is Validation Log?
Validation Log includes any errors or warnings reported by SSIS Runtime engine.
Make sure you correct any validation errors after migration in order to run your package successfully. Validation errors are not necessarily problems
with conversion but most of the time its problem with connection string, permission issues, file not found etc.
Validation errors (red x sign) are critical to fix. Until you fix all validation errors your package may not run.
Validation warnings (yellow exclamation sign) are non-critical and mostly fixing warnings may increase performance of SSIS package. If you do
not fix warnings your package will still continue to run but you may not get full performance.
[TOP]
To view any log for previous migration click "Log" option on the Welcome screen of DTS xChange and it should open the following screen. You can select the item and click next or double click to view the detailed log.
[TOP]
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.
[TOP]
Migrate:
This option will launch
wizard for DTS package migration. Using this option you can convert DTS packages
to SSIS 2005/2008. It will also apply series of best practices during migration
and at the end of the migration you will see full migration log and validation
report of converted packages for possible errors/warnings.
| Note: |
|---|
| Using Trial version you can convert maximum 3 packages. If you are converting DTS package which is calling other package(s) then parent package and child package(s) will be counted as separate migrations. |
Profile:
This option will launch DTS
Package Profiler Wizard. Profiler option is helpful to find out how many
packages you have and how complex they are to convert manually or using DTS
xChange. DTS Profiler will generate detailed report with package and task level
break down which you can export to PDF or Excel.
Reports:
This option will launch Report Viewer
Application. This is the place where you can view several predefined reports for
SSIS package auditing.
Log:
This
option will show history of all previous migration occurred on current machine
where you have installed DTS xChange.
Support/Bug:
You can click this URL if you have any
issue using DTS xChange. You can contact support by phone/email or by visiting
http://www.pragmaticworks.com/support
Check for update:
DTS xChange has auto update
feature which will check every couple of days or everytime you launch DTS
xChange depending option you have set. If product update is found then you will
see product update dialog box as below.
Registration:
Click this option to
view license information of your product. You can use same screen to
activate or deactivate license of your product.
[TOP]
Specify where DTS package(s) are stored
Source SQL Server
If your DTS packages stored on SQL Server 2000 or SQL Server 2005 (under legacy mode) then select this option. You can specify instance name or IP.
| Examples: |
|---|
|
|
Source File System
If DTS packages are stored as COM Structured Storage files (*.dts) then select this option. You have to browse folder location where dts files are
stored. On the next screen you can select packages found under selected folder.
[TOP]
Display Associated Job Detail:
This option will show four additional columns related associated job detail
which includes Job Name, last execution date, creation date and last execution status.
Search for:
This option will filter package list based on search criteria (e.g. to list all packages with word
"Test" enter Test in the filter box and click filter icon). This option is very handy when you have may packages and you don't know exact name of the
package.
Copy to clipboard:
Click this button to save content of top grid into clipboard in Tab delimited values (you
can copy/paste content to excel)
Save report:
Click this button to save content of top grid into Tab delimited values (you can copy/paste
content to excel)
[TOP]
Estimate by task type
This matrix has all possible DTS task type listed in the grid view. This table contains two estimate columns with suggested numbers but you can change
it if you want. This table has three columns as below.
Task Name : This column has common DTS task types. Any thing in the Green column will be converted 100% by DTS xChange. Anything in the yellow
will be partially converted by DTS xChange which means it may or may not work properly after conversion and some manual tweaks may require to make it
working. Anything in the red means there is no equivalent task in SSIS or DTS xChange can not convert that task so you have manually re-write that
task to native SSIS using your own approach.
Est. Hrs (Manual) : This column lists estimated time required to convert associated task type using manual approach.
Est. Hrs (DTS xChange) : This column lists estimated time required to convert associated task type using DTS xChange. Most of the tasks will be
automatically converted. Green cells are read only because DTS xChange converts 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)
This field allows you to enter estimated overhead time per package when you do migration manually without using DTS xChange. Usually this overhead
time includes Testing and Troubleshooting time, variable migration and connection migration time.
Other Time - Manual approach (Per Package)
This field allows you to enter estimated overhead time per package when you do migration using DTS xChange. This time will be usually less than
previous field because less troubleshooting/testing required compared to manual approach when you convert packages using DTS xChange.
Development Cost
Enter Average Hourly development cost for developer working on migration project.
Additional Features
If you want to add estimate for applying certain best practices when you go though manual approach select select one or more options here and enter
time to implement each best practice feature in your converted package.
[TOP]
[TOP]
If you want to generate very detailed profiler report with all task attributes then make sure you select "Generate detailed report" option on the DTS Migration Estimate screen.
[TOP]
DTS xChange offers easy way to add rich auditing features in converted
packages using custom auditing framework developed by Pragmatic Works. This
Auditing Frameworks uses all Native SSIS features and it can track
packages in real-time. Auditing Framework comes with lots of predefined reports
which can give you some valuable information.
You can audit the following
information using reports provided with Auditing Framework (Note: Use Report
Viewer Application to view auditing data.)
How does Auditing Framework work?
The following changes will be made to SSIS package when you apply auditing framework. These changes are done based on options you select on the auditing screen.
Here is the basic architecture of Auditing framework.
To view reports you have to use "Reports" option found on the main screen.
There are seven inbuilt reports are shipped this version and more will be added in the future release.
| Report Name | Description |
| SSIS Execution Dashboard | This dashboard gives one place view of most common counters of SSIS package execution. |
| Package Execution Trend | This report provides many useful information about trend of package runtime, task runtime and errors/warnings. |
| Errors/Warnings | Provides detailed error/warning report including package level errors (package level errors not listed in any other report) |
| Recent Executions | Provides information of running packages/tasks and already completed executions. |
| Running Packages | Provides real time information of currently running packages and tasks. |
| Extract/Load Detail | Provides extract/load detail (e.g. row count, source/target information, query, file name ...) about each dataflow in each package execution. |
| Extract/Load Trend | Provides graphical view of extract and load trend over several years, weeks, months and days. |
[TOP]
Where Last "N" days/weeks/months can be changed by specifying interval parameter.
[TOP]
[TOP]
[TOP]
[TOP]
[TOP]
[TOP]
[TOP]
After
adding column in the output column collection edit the script.
Following table explains how to convert various patterns found in ActiveX Script to SSIS
|
DataPump ActiveX Script Conversion Examples |
|
|
DTS |
SSIS |
| Example 1 | |
| DTSDestination("ProductID") = DTSSource("ProductID") | No need to convert because there is no transformation involved in this example. Just map ProductID <-> ProductID in the Destination Adapter. If you have simple pattern like this then just remove from the script and also make sure you remove from Script Component Input Columns to improve performance. |
| Example 2 | |
If DTSSource("TransCode") = "ACT" Then
DTSDestination("TransDescription") = "Acct Trans"
Else
DTSDestination("TransDescription") = "UNKNOWN"
End If |
In this example TransDescription column is derived from value of TransCode. Pattern like this when you don't
have exact mapping of source column to target column or Target column value is derived based on one or more source column
values then the best way is use derived column. You can also use script component but give first preference to Derived Column
if possible because its faster compared to script component. Some times you have to use Script component because of complexity
of code. Derived column is good for simple expressions. Using Derived Column Step 1: Add Derived Column Component in the pipeline Step 2: Add new column (e.g. MyTransDescription) Step 3: Type expression as below [TransCode]="ACT" ? "Acct Transaction" : "UNKNOWN" Step 4: On the Destination Adapter Mappings screen map MyTransDescription to Target column TransDescription Using Script Component You can do same thing with Script component but it will be slower than Derived column so if possible give first preference to Derived Column. Step 1: Select Input column TransCode (ReadOnly) Step 2: Add New Output Column "MyTransDescription" under Input Output Columns tab and specify data type and length of the column. Step 3: Type the following script If Row."TransCode" = "ACT" Then Row.MyTransDescription = "Acct Trans" Else Row.MyTransDescription = "UNKNOWN" End If Step 4: On the Destination Adapter Mappings screen map MyTransDescription to Target column TransDescription |
| Example 3 | |
If DTSSource("Discontinued") = 0 Or _
DTSSource("Discontinued") Is Null Then
DTSDestination("UnitPrice") = DTSSource("UnitPrice")+50
Else
DTSDestination("UnitPrice") = Null
End If |
If Row.Discontinued = 0 Or _
Row.Discontinued_IsNull Then
Row.UnitPrice = Row.UnitPrice + 50
Else
Row.UnitPrice_IsNull = True
End If
|
| Example 4 | |
DTSDestination("ProductName") = UCase(DTSSource("ProductName")) |
Row.ProductName = UCase(Row.ProductName) |
| Example 5 | |
| DTSDestination("RecordTimeStamp") = Now() | In this example source table doesn't have
RecordLoadTimeStamp column and value for target field is generated at
run time. For pattern like this you have to either use "Derived Column" component or use Script Component. Using Derived Column If you use derived column to add or modify column in the pipeline then Using Script Component If you decide to use script component then you have to add new column in the Output Columns (See below). After
adding column in the output column collection edit the script as below. |
[TOP]
Purpose
The Data Driven Query task allows you to perform flexible, Transact-SQL based operations on data, including stored procedures and INSERT, UPDATE or
DELETE statements. For each row in a source rowset, the Data Driven Query task selects, customizes, and executes one of several SQL statements. You
select which statement to execute via a constant return value set in a Microsoft® ActiveX® script transformation. Based on the return constant you
use in the script, one of four different parameterized SQL statements that you create may be executed for each source row.
Summary
DTS xChange does not convert Data Driven Query to native SSIS but it will be converted to embedded DTS object. Data Driven Query Task has been discontinued
in SSIS and you may want to rewrite it using native SSIS tasks and components. This section will cover some detail on how to use native SSIS components
to convert Data Driven Query Task manually.
Step-By-Step Example
The following example will guide you step-by-step how to convert DDQ task to Data Flow and apply various transformations. Following items will be
covered in this example
Challenging part of converting DDQ Task is ActiveX Script Transformation (see below code). If you observe the source code you will notice that
Main function is returning 3 different status based on certain condition
In our example
'**********************************************************************
' Visual Basic Transformation Script
'**********************************************************************
' Copy each source column to the destination column
Function Main()
If DTSSource("CategoryID") =1 or DTSSource("CategoryID") =2 Then '//For Category 1,2 : Insert
'//Special mapping
DTSDestination("ProductName") = UCase(DTSSource("ProductName"))
'//Copy column mappings
DTSDestination("ProductID") = DTSSource("ProductID")
DTSDestination("SupplierID") = DTSSource("SupplierID")
DTSDestination("CategoryID") = DTSSource("CategoryID")
DTSDestination("QuantityPerUnit") = DTSSource("QuantityPerUnit")
DTSDestination("UnitPrice") = DTSSource("UnitPrice")
DTSDestination("UnitsInStock") = DTSSource("UnitsInStock")
DTSDestination("UnitsOnOrder") = DTSSource("UnitsOnOrder")
DTSDestination("ReorderLevel") = DTSSource("ReorderLevel")
DTSDestination("Discontinued") = DTSSource("Discontinued")
'//Get CategoryName from Lookup
DTSDestination("CategoryName") = DTSLookups("lookupCategory").Execute(DTSSource("CategoryID"))
Main = DTSTransformstat_InsertQuery
ElseIf DTSSource("CategoryID") =3 Then '//For Category 3 : Update Price by 2%
Main = DTSTransformStat_UpdateQuery
Else '//For other category dont do anything
Main = DTSTransformstat_SkipRow
End If
End Function
DTS xChange can convert everything except DDQ Task. Perform the following actions to replace DDQ task with DataFlow
| Step-1 : Setup Test Database |
| This sample uses northwind database. Please download the northwind database script and run in a new Sql Server Management Studio query window to your local instance (or select different server). This script will create northwind database and populates sample data. |
| Step-2 : Migrate DTS package using DTS xChange |
| Migrate sample dts package using DTS xChange. It will migrate all tasks except DDQ. |
| Step-3 : Delete DDQ and add DataFlow task |
| Delete DDQ Task container and drop new DataFlow task in the package surface. Rename DataFlow to "Process Products". Attach "Create Table" task with Data Flow task. |
| Step-4 : Add OLEDB source |
| - Click on the DataFlow and drop OLEDB Source Adapter from the toolbar as displayed in the Figure-2. - Double click on the source and select CONN_SRC in the connection dropdown. - Select "Products" table in the Table or View Dropdown. |
| Step-5 : Add derived column |
Add derived column component and name it "Special Transformation". Add new "Replace 'ProductName'" expression as below. This expression
will change ProductName to upper case. Connect OLEDB Source with derived column (See final figure). Figure-7: Replace special transformation with Derived column task |
| Step-6 : Add conditional split |
Add conditional split component. Attach Derived column with conditional split as shown in the final figure. Double click on the conditional
split to configure various outputs based on conditions. If you observe the ActiveX script above you will notice that we have total 2 IF conditions
and one default (i.e. ELSE block). We will do the same thing here in conditional split and give it meaningful name for each output.
Figure-8: Replace special transformation with Derived column task |
| Step-7 : Add Lookup |
DDQ Task ActiveX Script Transformation is using Lookup to find CategoryName using source CategoryID. We can accomplish similar behavior
using Lookup Component found in SSIS but you will notice that in SSIS lookup you don't have to pass parameter.
 Figure-9: Replace special transformation with Derived column task  Figure-10: Replace special transformation with Derived column task  Figure-11: Replace special transformation with Derived column task
|
| Step-8 : Add OLDEB destination |
|
| Step-9 : Add OLEDB Command |
OLEDB command can be used if you want to execute SQL Statements for each row in the input pipeline.
 Figure-11: Replace special transformation with Derived column task |
| Step-10 : Final Control Flow |
 Figure-12: Converted sample SSIS package Click on Debug -> Start Debugging (or F5) to run the package |
[TOP]
Severity:
Low
Description:
This is the most common
error you may see after migration. Here is the sample error you may see in the
validation log
Description: SSIS Error Code
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method
call to the connection manager "OLEDB_NORTHWIND_1" fail.
This
error can occur for many reasons. Here are some possible reasons
- One or
more attribute of your connection string is invalid.
- Server name/file
path specified in the connection string is invalid/offline or not
accessible due to network or firewall issue.
Recommended Action:
- In the dataflow right click on the source adapter and select "Edit..."
- Select Columns tab
- Uncheck unnecessary columns from the "Available
External Columns" list.
- Click OK to save changes
- Repeat above steps
for all columns with similar warning. Check "Error List" for any possible
Warnings/Errors
[TOP]
Severity: High
Description:
This error occurs when one or more DTS components not registered on the machine where you running DTS xChange/DTS
Profiler. This error will prevent package loading and you will not be able to profile or migrate package until you install required DLLs.
Recommended Action:
- Open the DTS package causing the issue and try to find any Custom Task. OLAP Task is the most common Custom task which is not installed by
default in that case you may receive this error.
- Make sure you have DTS Runtime installed properly. If you dont have DTS Runtime then please use the following Links to download appropriate version
of DTS Runtime.
- Click here to learn more about installation requirements
| Note: |
|---|
| If above test failed and you are getting components missing error then please run the SQL Server 2005/2008 setup and install Backward
Compatibility components or download from the following URL (For 32 bit OS use x86 link). Microsoft SQL Server 2005 Backward Compatibility Components The SQL Server Backward Compatibility package includes the latest versions of Data Transformation Services 2000 runtime (DTS), SQL Distributed Management Objects (SQL-DMO), Decision Support Objects (DSO), and SQL Virtual Device Interface (SQLVDI). These versions have been updated for compatibility with both SQL Server 2005 and SQL Server 2008 and include all fixes shipped through SQL Server 2000 SP4 and SQL Server 2005 SP2.
X86 Package (SQLServer2005_BC.msi) - 11273 KB
X64 Package (SQLServer2005_BC.msi) - 18569 KB
IA64 Package (SQLServer2005_BC.msi) - 23510 KB |
[TOP]
Severity: Low
Description:
This warning occurs when you have different column size in the source and target.
Recommended Action:
- In the dataflow right click on the source adapter and select "Show Advanced Editor"
- Select "Input and Output Properties" tab
- Expand "<source> output"
- Expand "Output columns" node
- Change Length to match with Target column
- Click OK to save changes
- Repeat above steps for all columns with similar warning. Check "Error List" for any possible Warnings/Errors
[TOP]
Severity: Low
Description:
This warning occurs when you have columns
selected in the source adapter which are not used anywhere in the transformation
or mappings.
Such unwanted columns increase SSIS buffer size and
slow down DataFlow processing.
Recommended Action:
- In the dataflow right click on the source adapter and select "Edit..."
- Select Columns tab
- Uncheck unnecessary
columns from the "Available External Columns" list.
- Click OK to save changes
- Repeat above steps for all columns with similar warning. Check "Error List" for any possible Warnings/Errors
[TOP]
Enhanced Performance and Caching for the Lookup Transformation:
Performance enhancements to the Lookup transformation include faster cache loading and more efficient lookup operations. These enhancements
are possible because of the following features:
New ADO.NET Components:
Integration Services now includes the following ADO.NET components:
New Date and Time Data Types:
The following new date and time data types are available in Integration Services:
New Debug Dump Files:
You can create debug dump files (.mdmp and .tmp) that provide information about what happens when a package runs. This information can
help you in troubleshooting issues that occur when you run the package.
For more information visit the following URLs
Source: simple-talk.com - New feature in
SSIS 2008
Source: technet.microsoft.com - What's New (Integration Services 2008)
Source: blogs.msdn.com/mattm - What's new in SQL Server 2008 for SSIS - Part one
Source: blogs.msdn.com/mattm - What's new in SQL Server 2008 for SSIS - Part two
[TOP]
| Parameterized DTS Package - Passing parameters to DTS package from command line |
DTSRun /S "(local)" /N "PkgTest" /A "varFile":"8"="File_001.xls" /E Note: "8" is DataType ID |
| Parameterized SSIS Package - Passing parameters to SSIS package from command line |
DTSExec /SQL "\PkgTest" /SERVER "(local)" /SET "\Package.Variables[User::varFile].Value";"File_001.xls" |
Please refer the following URL to get more information on DTExec command line options
http://msdn.microsoft.com/en-us/library/ms162810.aspx
[TOP]
| Path related functions |
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Dim sMyVar As String
'//Get only file name from a specified path => Returns mydatafile_001.txt
sMyVar = System.IO.Path.GetFileName("c:\temp\mydatafile_001.txt")
'//Get only directory path from a specified path => Returns c:\temp
sMyVar = System.IO.Path.GetDirectoryName("c:\temp\mydatafile_001.txt")
'//Combine two paths into one path => Returns c:\temp\mydatafile_001.txt
sMyVar = System.IO.Path.Combine("c:\temp", "mydatafile_001.txt")
'//Get filename without extension => Returns mydatafile_001
sMyVar = System.IO.Path.GetFileNameWithoutExtension("c:\temp\mydatafile_001.txt")
'//Get extension of the file => Returns txt
sMyVar = System.IO.Path.GetExtension("c:\temp\mydatafile_001.txt")
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
| Check if file/folder exists |
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Dim sMyVar As String
If File.Exists("c:\temp\file_001.txt") = True Then
'//Debug.Print "File Exists"
End If
If Directory.Exists("c:\temp") = True Then
'//Debug.Print "Folder Exists"
End If
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
| Read from text file |
Imports System.IO
Public Class ScriptMain
Public Sub Main()
'//Read file content to string variable
Dim sMyfileData As String
Dim sReader As StreamReader = New StreamReader("c:\temp\file_001.txt")
sMyfileData = sReader.ReadToEnd
sReader.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
| Read from text file (line by line) |
Imports System.IO
Public Class ScriptMain
Public Sub Main()
'//Read file line by line
Dim sReader As New StreamReader("c:\autoexec.bat")
' Display all the text lines in the file.
Do Until sReader.Peek = -1
' The ReadLine methods reads whole lines.
Console.WriteLine(sReader.ReadLine)
Loop
' Always close a StreamReader when you've done with it.
sReader.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
| Write to text file |
Imports System.IO
Public Class ScriptMain
Public Sub Main()
'//Open existing file for append. If file doesn't exist then new file will be created
Dim writer As StreamWriter = New StreamWriter("c:\write_test.txt", True)
writer.WriteLine("Hello world - line1")
writer.WriteLine("Hello world - line2")
writer.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
| Get file information |
Imports System.IO
Public Class ScriptMain
Public Sub Main()
'//Get file properties
Dim sInfo As String
Dim FileProps As FileInfo = New FileInfo("c:\windows\notepad.exe")
sInfo = sInfo & " File Name = " & FileProps.FullName
sInfo = sInfo & " Creation Time = " & FileProps.CreationTime
sInfo = sInfo & " Last Access Time = " & FileProps.LastAccessTime
sInfo = sInfo & " Last Write Time = " & FileProps.LastWriteTime
sInfo = sInfo & " Size = " & FileProps.Length
System.Diagnostics.Debug.Write(sInfo)
FileProps = Nothing
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
| List files (with wild card search pattern and recursive option) |
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Dim file As String
'//Recursive listing (search for *.txt)
'//Dim files() As String = Directory.GetFiles("c:\windows", "*.txt", SearchOption.AllDirectories)
'//Top level listing (search for *.txt)
Dim files() As String = Directory.GetFiles("c:\windows", "*.txt", SearchOption.TopDirectoryOnly)
For Each file In files
System.Diagnostics.Debug.WriteLine(file & "...found")
Next
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
| List sub directories |
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Dim dir As String
'Dim dirlist() As String = Directory.GetDirectories("c:\windows","*",SearchOption.AllDirectories)
Dim dirlist() As String = Directory.GetDirectories("c:\windows")
For Each dir In dirlist
System.Diagnostics.Debug.WriteLine(dir)
Next
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
| List disk drives |
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Dim dirInfo As Directory
Dim drive As String
Dim drives() As String = dirInfo.GetLogicalDrives()
For Each drive In drives
System.Diagnostics.Debug.WriteLine(drive)
Next
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
| File/Folder delete, copy, move |
Imports System.IO
Public Class ScriptMain
Public Sub Main()
If File.Exists("c:\dest\datafile.txt") Then
File.Delete("c:\dest\datafile.txt")
End If
File.Copy("c:\src\datafile.txt", "c:\dest\datafile.txt")
File.Move("c:\src\datafile.txt", "c:\dest\datafile.txt")
If Directory.Exists("c:\dest") Then
Directory.Delete("c:\dest")
End If
Directory.CreateDirectory("c:\dest")
Directory.Delete("c:\dest")
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
[TOP]
Using ADO objects inside ActiveX Script task is very common in DTS. People use ADO objects (i.e. ADODB.Connection, ADODB.RecordSet etc)
for many reasons but the most common use are
- Creating connections at runtime
- Executing Adhoc Queries at runtime
- Processing certain data row by row
Check the following example on how to convert ActiveX Script. It performs couple of things
- Establish Connection to SQL Server using ADODB.Connection object
- Execute SQL Query and Load the recordset using ADODB.Recordset object
- Loop through each record to build HTML formatted string and email it.
| Sample ActiveX Script using ADODB.Connection and ADODB.Recordset |
'**********************************************************************
' Visual Basic ActiveX Script
'**********************************************************************
Function Main()
Dim objRs
Dim objConn
Dim strSql
Dim strHtml
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Provider=SQLOLEDB;Data Source=(local);Trusted_Connection=yes;Initial Catalog=Northwind"
strSql = "Select top 10 b.ProductName,Sum(a.UnitPrice*a.Quantity) as Total from [Order Details] a "
strSql = strSql & "join Products b on a.ProductID =b.ProductID "
strSql = strSql & "group by b.ProductName order by Sum(a.UnitPrice*a.Quantity) desc"
'--- //Method 1 : Using Execute Method of Connection Object
'--- Set objRs=objConn.Execute(strSql)
'--- //
'*** /// OR ///****
'//Method 2 : Using Execute Method of Connection Object
Set objRs = CreateObject("ADODB.Recordset")
objRs.Open strSql, objConn
'//
strHtml = "<h2>Top 10 Products<h2><table>"
strHtml = strHtml & "<tr><td>Product Name</td><td>Total</td></tr>"
Do While Not objRs.EOF
strHtml = strHtml & "<tr><td>" & objRs(0) & "</td><td>" & objRs(1) & "</td></tr>"
objRs.MoveNext
Loop
strHtml = strHtml & "</table>"
'MsgBox strHtml
SendMail "dataservices@mycompany.com", "ceo@mycompany.com", "Top 10 Products", strHtml, True
Main = DTSTaskExecResult_Success
End Function
Sub SendMail(FromAddress, ToAddress, Subject, Body, IsHtml)
'//Write Code to send email
End Sub
|
Above script is DTS way to perform some common operations but when you migrate to SSIS you can take different approach without writing
too much code. The best practice is whenever possible try to avoid hard coded connection string inside your script.
The following table enlists how to migrate various patterns you generally encounter in the DTS world.
| DTS | SSIS |
| ADO Connection | 1. Replace with Connection Manager |
| ADO Command (no result set returned ... i.e. INSERT/UPDATE/DELETE) | 1. Add Execute SQL Task. Set Connection, Set SQL Statement 2. Set resultset property to "None" 3. Set Parameter mapping if required |
| ADO Recordset (which returns resultset) | 1. Add SSIS variable of Object data type to hold Resultset 2. Add Execute SQL Task. Set Connection, Set SQL Statement 3. Set resultset property to "Full Resultset" 4. Set resultset mapping to variable of Object data type to hold Recordset. Make sure to rename Resultset Name="0" |
| ADO Recordset Looping | 1. Add Foreach Loop Container 2. 2. Use Foreach ADO Enumerator and specify variable which holds resultset (SSIS variable of Object Datatype) 3. In the Variable Mappings add recordset column index to SSIS variable map. Index starts from 0. |
Lets walk through Step-By-Step how to convert above sample DTS ActiveX script to SSIS equivalent control flow.
Fig-1 (Converted SSIS Package)
Sample Package Installation
Perform the following steps to setup and test the above sample SSIS Package
1. Extract the sample zip file
2. Open FAQTest.sln and analyze or run the Sample Package to test
Step-By-Step
Define Variables : The sample package uses four variables with the following settings.
"Is Expression" column refers to the EvaluateAsExpression property of SSIS variable.
|
|||||||||||||||||||||||||
| Define Connections : The sample package requires one connection. - Create an oledb connection ((local).Northwind) to use Northwind database. |
|||||||||||||||||||||||||
Execute SQL Task (Get Recordset) : Next step is to create an execute sql task to execute
the following SQL Statement also make sure you select Northwind connection for this task.
|
|||||||||||||||||||||||||
| Foreach Loop Container (Loop Recordset) : Place Foreach Loop Container and double click to open properties dialog box. Apply the following settings displayed in the screenshots. | |||||||||||||||||||||||||
|
Fig-2 (Select Enumerator, ADO object Source Variable and Enum mode) |
|||||||||||||||||||||||||
| Script Task (Update HTML String) : Place Script Task inside the Foreach Loop Container.
- Double click the Script Task - Specify ReadOnlyVariables as displayed in the following screenshot - Specify ReadWriteVariables as displayed in the following screenshot. - Click on the Design Script Button - Enter the script displayed in the Fig-5 |
|||||||||||||||||||||||||
|
Fig-4 (ReadOnly and ReadWrite variables)
|
|||||||||||||||||||||||||
| Script Task (Send HTML Email) : Last step is to place Script Task to execute SendHTMLEmail
routine. - Place a Script Task - Double click the Script Task - Specify strHTML in the ReadOnlyVariables - Click on the Design Script Button - Enter the script displayed in the Fig-6 |
|||||||||||||||||||||||||
|
Fig-6 (Send HTML Email)
|
|||||||||||||||||||||||||
| Connect all tasks and testing : Connect all tasks as shown in the Fig-1 and execute the package to test. | |||||||||||||||||||||||||
[TOP]
Sample Package Installation
Perform the following steps to setup and test the above sample SSIS Package
1. Extract the sample zip file
2. Create a folder C:\SSIS
3. Create a Folder C:\SSIS\NightlyData
4. Create a Folder C:\SSIS\NightlyData\Archived
5. Copy Provided sample files to C:\SSIS\NightlyData
6. Open FAQTest.sln and analyze or run the Sample Package to test
Step-By-Step
Define Variables : The sample package uses five variables with the following settings.
"Is Expression" column refers to the EvaluateAsExpression property of SSIS variable.
|
||||||||||||||||||||||||||||||
| Define Connections : The sample package requires two connections. - Create an oledb connection ((local).tempdb) to use tempdb database. - Create a Flat file connection (NightlyDataFile) for any available file in the SourceFolder (in this example use C:\SSIS\NightlyData\Nightly_01.txt). We have to make FlatFile connection Dynamic so we can use one connection to load several files from the source folder. In order to make FlatFile connection dynamic we have to configure ConnectionString Property as an Expression (See below screenshot). varFilePath will be updated on each iteration of file in Foreach Loop Container (Check the Foreach Loop Container description below in this article). |
||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||
Execute SQL Task (Create or Truncate Table) : Next step is to create an execute sql
task to execute the following SQL Statement also make sure you select tempdb connection for this task.
|
||||||||||||||||||||||||||||||
| Foreach Loop Container (Load Nighly Data Files) : Place Foreach Loop Container and double click to open properties dialog box. Apply the following settings displayed in the screenshots. | ||||||||||||||||||||||||||||||
|
Fig-1 (Select Enumerator, Folder, Files and Retrieve filename) |
||||||||||||||||||||||||||||||
| Data flow (Load Data File) : Place Data flow inside the Foreach Loop Container.
- Double click the dataflow - Place FlatFile Source - Place OLEDB Destination. - Connect FlatFile Source and OLEDB Destination - Double click on the OLEDB Destination to Select tempdb connection. Then click on mapping and configu |
||||||||||||||||||||||||||||||
|
Source->Destination |
||||||||||||||||||||||||||||||
| File System Task (Move File to Archive) : Last step is to place File System Task to move processed file to archive folder. Please specify the properties displayed as below. | ||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||
| Connect all tasks and testing : Connect all tasks as shown in the very first screen and run the package. If everything is configured correctly then files should load into staging table and then moved to Archived folder. | ||||||||||||||||||||||||||||||
[TOP]
| Send HTML email |
'// Example :
'// SendMail("someone@mycompany.com, _
'// "support@dtsxchange.com", _
'// "HTML Test Email!!!", _
'// "<B>Hello</B> How are you?", _
'// True)
Private Sub SendMail( _
ByVal SendTo As String, _
ByVal From As String, _
ByVal Subject As String, _
ByVal Body As String, _
Optional ByVal IsBodyHtml As Boolean = True, _
Optional ByVal SMTPServer As String = "localhost", _
Optional ByVal UserName As String = "", _
Optional ByVal Password As String = "", _
Optional ByVal Domain As String = "", _
Optional ByVal Attachments As String = "")
Dim oMessage As System.Net.Mail.MailMessage
Dim mySmtpClient As System.Net.Mail.SmtpClient
oMessage = New System.Net.Mail.MailMessage(From, SendTo, Subject, Body)
oMessage.IsBodyHtml = IsBodyHtml
'//Attachments
If Not String.IsNullOrEmpty(Attachments) Then
Dim sFiles() As String
Dim sFile As String
sFiles = Split(Attachments, ";")
For Each sFile In sFiles
If Not String.IsNullOrEmpty(sFile) Then
oMessage.Attachments.Add(New Net.Mail.Attachment(sFile))
End If
Next
End If
mySmtpClient = New System.Net.Mail.SmtpClient(SMTPServer, 25)
If UserName = "" Then
mySmtpClient.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials
Else
mySmtpClient.Credentials = New System.Net.NetworkCredential(UserName, Password, Domain)
End If
mySmtpClient.Send(oMessage)
End Sub
|
[TOP]
[TOP]
[TOP]
| Load Package, Set Variable and Execute Package at runtime |
Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim oApp As Microsoft.SqlServer.Dts.Runtime.Application = New _
Microsoft.SqlServer.Dts.Runtime.Application()
Dim oPkg As Microsoft.SqlServer.Dts.Runtime.Package
'//Load Child Package from SQL Server
oPkg = oApp.LoadFromSqlServer("\DW_Packages\Task_ExecSQL", "(local)", "", "", Nothing)
Dim vars As Variables
'//If Child Package Variable Doesn't exist at runtime
'//then create a new variable and pass the value from Parent package variable.
If oPkg.Variables.Contains("gvChild1") Then
oPkg.VariableDispenser.LockOneForWrite("gvChild1", vars)
Try
vars("gvChild1").Value = "Hello!!! Set From Parent..."
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Else
oPkg.Variables.Add("gvChild1", False, "", "Hello!!! Set From Parent...")
End If
oPkg.Execute() '//Execute the Child Package
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
[TOP]
Visit the following URL to see full list of feature matrix.
DTS xChange Features :
http://pragmaticworks.com/DTSxChange-vs-MSWizard.asp
[TOP]
| Load Package, Set Variable and Execute Package at runtime |
Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim oApp As Microsoft.SqlServer.Dts.Runtime.Application = New _
Microsoft.SqlServer.Dts.Runtime.Application()
Dim oPkg As Microsoft.SqlServer.Dts.Runtime.Package
'//Load Child Package from SQL Server
oPkg = oApp.LoadFromSqlServer("\DW_Packages\Task_ExecSQL", "(local)", "", "", Nothing)
Dim vars As Variables
'//If Child Package Variable Doesn't exist at runtime
'//then create a new variable and pass the value from Parent package variable.
If oPkg.Variables.Contains("gvChild1") Then
oPkg.VariableDispenser.LockOneForWrite("gvChild1", vars)
Try
vars("gvChild1").Value = "Hello!!! Set From Parent..."
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Else
oPkg.Variables.Add("gvChild1", False, "", "Hello!!! Set From Parent...")
End If
oPkg.Execute() '//Execute the Child Package
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
[TOP]
[TOP]
| Functionality | Best in File System | Best in MSDB |
| Security | X | |
| Backup and Recovery | X | |
| Deployment | X | |
| Troubleshooting | X | |
| Execution Speed | X | X |
| Availability | X |
If security concerns you greatly, you may want to consider placing your packages in the msdb database. To secure your packages on the
file system, you could have multiple layers of security by using the Windows Active Directory security on the folder on the file system
where the packages are at. You could also then place a password on the packages to keep users that may have administrator rights to your
machine from executing the package. This does add extra complexity to your package deployments in some cases. If you store your packages
in the msdb database, you can assign package roles to each package to designate who can see or execute the package. The packages can also
be encrypted in the msdb database, which strengthens your security even more.
Backup and recovery is simpler with storing your packages in the msdb database. If you were to store your packages in the msdb database,
then you must only wrap the msdb database into your regular maintenance plan to backup all the packages. As packages are added, they are
wrapped into the maintenance plan. The problem with this is that you can restore a single package using this mechanism. You’d have to
restore all the packages to a point in time, and that would also restore the jobs and history. The other option is a file system backup,
which would just use your favorite backup software to backup the folders and files. If you did this, you must rely on your Backup Operator
to do this for you, which makes some uneasy. You could though at that point, restore individual packages to a point in time. In reality,
you may just go ahead and redeploy the packages from Source Safe if you couldn’t retrieve a backup file.
File system deployments are much simpler but less sophisticated. To deploy packages onto the file system, you must only copy them into
the directory for the package store. You can create subdirectories under the parent directory to subdivide it easily. You can also copy
a single package over easily as well in case you need to make a package change. To import a package into the package store using the msdb
database, you must use Management Studio (or a command-line tool called dtutil.exe) and import them package by package. To do a
bulk migration, you could use the deployment utility.
Along the same lines as deployment is troubleshooting. If something were to go bump in the night and you wanted to see if the packages
in production were the same release as the packages you thought you had deployed, you must only copy the files down to your machine and
perform a comparison using Source Safe or another similar tool. If the files were stored in the msdb database, you would have to right-click
on each package in Management Studio and select Export. If the same packages were stored in the file system, you must only copy the files
to your machine.
Availability of your packages is always on the top of the list for DBAs. If you were to store the packages in the msdb database and the
database engine were to go down, the packages are unavailable. If they were stored in the file system, then your packages would be available
for execution. Of course, if the database engine is down, then probably one of your key data sources would also be down at the time.
The good news is no matter what storage option you choose the performance will be the same. As you can see there are many pros and cons
to each storage option and neither overwhelmingly wins. The main reason that we choose to use the file system generally is for simplicity
of deployment.
[TOP]
and ... many more....
For more information please download the following white paper
Whats New in SSIS.doc
[TOP]
| 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 |
Y | x64, IA64 |
| SQL Server | Microsoft | ADO.NET | 2000 and later versions |
Y | x64, IA64 |
| SQL Server | Microsoft | ODBC | 2000 and later versions |
Y | x64, IA64 |
| SQL Server | Attunity | OLE DB | 2000 and later versions |
Y | |
| SQL Server | DataDirect | OLE DB | 2000 and later versions |
Y | |
| DB2 | Microsoft | OLE DB | All DRDA-compliant versions | Y | 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 |
Y | |
| DB2 | IBM | ADO.NET | DB2 UDB 9.0 | N | |
| DB2 | IBM | ODBC | z/OS and UDB 7.0 and later versions AIX 5.0 and later |
N | N |
| 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 |
Y | |
| DB2/400 | Attunity | OLE DB | On AS/400 5.1 and later versions |
Y | |
| DB2 | DataDirect | OLE DB | z/OS and UDB 7.0 and later versions AIX 5.x AS400 4.5 and later |
N | |
| DB2 | HIT | OLE DB | z/OS and UDB 8.0 and later versions AIX 5.x, AS400 4.5 |
N | |
| DB2 | DataDirect | ADO.NET | z/OS and UDB 7.0 and later AIX 5.x, AS400 4.5 |
Y | |
| DB2 | ETI | Bulk Load | 8.0 and later versions |
Y | |
| DB2¹ | Persistent | Data Flow Component
Bulk Write & Bulk Read |
- | N | |
| Oracle | Microsoft | OLE DB | 7.3.4 and later versions² |
Y | N |
| Oracle | Microsoft | ADO.NET | 8.0 and later versions |
Y | x64, IA64 |
| Oracle | Oracle Corp | OLE DB | 8i and later versions | Y | x64, IA64 |
| Oracle | Oracle Corp | ADO.NET | 8i and later versions | Y | x64, IA64 |
| Oracle | Oracle Corp | ODBC | 8i and later versions | N | |
| Oracle | Microsoft | ODBC | 8i and later versions | N | |
| Oracle | Attunity | OLE DB | 9i and later versions | Y | |
| Oracle | DataDirect | OLE DB | 8i and later versions | Y | |
| Oracle | DataDirect | ADO.NET | 8i and later versions | Y | |
| Oracle | ETI | Bulk Load | 9.0 and later versions |
Y | |
| Oracle | Persistent | Data Flow Component
Bulk Write |
8i and later versions | N | |
| SAP¹ | Microsoft | ADO.NET | R/3 4.6C and later versions |
Y | |
| SAP | Theobald | OLE DB | R/3 | Y | |
| Office Access | Microsoft | OLE DB | 2003 and earlier versions |
Y | |
| Office Excel | Microsoft | OLE DB | 2003 and earlier versions |
Y | |
| Office 2007 | Microsoft | OLE DB | 2007 | N | |
| Sybase | Sybase | OLE DB | 11.5 and later versions |
N | |
| Sybase | Sybase | ADO.NET | 11.5 and later versions |
N | |
| Sybase | Attunity | OLE DB | 12.0 and later versions |
Y | |
| Sybase | DataDirect | OLE DB | 11.5 and later versions |
Y | |
| Sybase | DataDirect | ADO.NET | 11.5 and later versions |
Y | |
| Informix | IBM | OLE DB | 7.3 and later versions |
N | |
| Informix | Attunity | OLE DB | 7.31, 9.x, 10 | Y | |
| Informix¹ | Persistent | Data Flow Component
Bulk Write & Bulk Read |
- | N | |
| Teradata | Teradata | OLE DB | 2.6 and later versions |
N | |
| Teradata | Teradata | ADO.NET | 2.6 and later versions |
N | |
| Teradata | ETI | Bulk Load | 2.5 and later versions |
Y | |
| Teradata | ETI | Bulk Extract | 2.5 and later versions |
Y | |
| FoxPro | Microsoft | OLE DB | 8.0 and later versions |
N | |
| File DBs | Microsoft | OLE DB | Any Jet 4.0– compatible version | N | |
| Adabas | Attunity | OLE DB | 6.2.2 to 7.4.x on z/OS 3.3 to 5.1 on Open |
Y | |
| CISAM | Attunity | OLE DB | On UNIX | Y | |
| DISAM | Attunity | OLE DB | On UNIX, Linux, and Windows |
Y | |
| Ingres II | Attunity | OLE DB | 2 to 2.56 | Y | |
| Oracle Rdb | Attunity | OLE DB | 7.1.x, on OpenVMS Alpha and Integrity (Itanium) |
Y | Y (HP Integrity) |
| RMS | Attunity | OLE DB | On OpenVMS Alpha and Integrity (Itanium) |
Y | Y (HP Integrity) |
| Enscribe | Attunity | OLE DB | On HP NonStop G- Series and H-Series |
Y | Y (HP Integrity) |
| SQL/MP | Attunity | OLE DB | On HP NonStop G- Series and H-Series |
Y | Y (HP Integrity) |
| IMS/DB | Attunity | OLE DB | 6.1 and later versions |
Y | |
| VSAM | Attunity | OLE DB | On z/OS 1.1 and later versions |
Y | |
| LDAP | Persistent | ODBC | All LDAP-compliant servers |
N | Y |
¹This product is in beta.
²Although this provider can connect to and use versions of Oracle up to and including Oracle 10gR2, it does not support database constructs (such as BLOB/CLOB data types) introduced after Oracle 8.0).
[TOP]
You can enable/disable certain tasks in SSIS using two different methods.
1. Use expression on Disable Property of Task
2. Use expression on precedence constraint.
Please use second option whenevr possible.
[TOP]
DTExec - 32Bit can be found under : C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn
DTExec - 64Bit can be found under : C:\Program Files\Microsoft SQL Server\90\DTS\Binn
For more information click on the following URL
http://msdn.microsoft.com/en-us/library/ms162810.aspx
If your SSIS package is referencing any 32Bit DLL or 32Bit drivers from your package then you must use 32Bit version of DTExec.
Common example of 32Bit driver is : Microsoft Jet Driver (MS Access or Excel). There is no 64Bit version of Jet Driver so any packages using Jet
driver (i.e. Excel or MS Access Database Connections) must be called using 32 bit DTExec.
When you are designing such SSIS packages in Visual Studio and if you have reference to any 32Bit driver/dll then make sure you change Project Property Run64BitRuntime to False.
- Right click on Project Node
- Under Debugging option change Run64BitRuntime to False
[TOP]
You may need to perform additional security configurations if you are connection to SSIS Server running on remote machine.
Please check the following link for more information
http://msdn.microsoft.com/en-us/library/aa337083.aspx
[TOP]
It was commonly recommended in DTS that a package should contain multiple connection objects pointing to the same database if you had multiple tasks using that database. It was considered more efficient for each task to have a dedicated connection to the server.
In SSIS, DTS Connection objects have been replaced with Connection Managers. There is a good reason for the name change. A SSIS Connection Manager maintains a pool of connections to the database rather than just a single transaction therefore you only need one Connection Manager whereas in DTS you would need multiple Connection objects. If you need to replicate the same behavior as DTS within a SSIS package then look into using the RetainSameConnection property.
[TOP]
Pragmatic Works, Inc
91 Branscomb Rd. Suite 16
Green
Cove Springs, FL 32043
Sales Hotline: 1-(888)-471-1946
Support
Hotline: 1-(877)-468-6404
Fax: 760-462-3959
Sales@pragmaticworks.com
Support@pragmaticworks.com
About us:
With decades of experience in business
intelligence, Pragmatic Works specializes in implementing business intelligence
solutions on the Microsoft BI stack (Analysis Services, Integration Services and
Reporting Services) for small and large-scale customers.
[TOP]
Introductory Videos from JumpstartTV
http://www.jumpstarttv.com/
Guided Tours
http://www.microsoft.com/sql/technologies/integration/tours.mspx
Guided Tours
http://www.microsoft.com/sql/technologies/integration/tours.mspx
TechNet Webcast: Introduction to SQL Server 2005
Integration Services (Level 200)
http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032289998&EventCategory=5&culture=en-US&CountryCode=US
SQL Server™ 2005: Integration Services Virtual Lab
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032286838&EventCategory=3&culture=en-US&CountryCode=US
[TOP]
This guide contains proprietary information protected by copyright. The software described in this guide is furnished under a software license or nondisclosure agreement. This software may be used or copied only in accordance with the terms of the applicable agreement. No part of this guide may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying and recording for any purpose other than the purchaser’s personal use without the written permission of Pragmatic Works Inc.
If you have any questions regarding your potential use of this material,
please contact:
Pragmatic Works, Inc
91
Branscomb Rd. Suite 16
Green Cove Springs, FL 32043
Website : www.pragmaticworks.com
Email : info@pragmaticworks.com
Phone
: 1-888-471-1946
Disclaimer: The information in this document is
provided in connection with Pragmatic Works products. No license, express or
implied, by estoppel or otherwise, to any intellectual property right is granted
by this document or in connection with the sale of Pragmatic Works products.
EXCEPT AS SET FORTH IN PRAGMATIC WORKS' TERMS AND CONDITIONS AS SPECIFIED IN THE
LICENSE AGREEMENT FOR THIS PRODUCT, PRAGMATIC WORKS ASSUMES NO LIABILITY
WHATSOEVER AND DISCLAIMS ANY EXPRESS, IMPLIED OR STATUTORY WARRANTY RELATING TO
ITS PRODUCTS INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTY OF
MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT. IN NO
EVENT SHALL PRAGMATIC WORKS BE LIABLE FOR ANY DIRECT, INDIRECT, CONSEQUENTIAL,
PUNITIVE, SPECIAL OR INCIDENTAL DAMAGES (INCLUDING, WITHOUT LIMITATION, DAMAGES
FOR LOSS OF PROFITS, BUSINESS INTERRUPTION OR LOSS OF INFORMATION) ARISING OUT
OF THE USE OR INABILITY TO USE THIS DOCUMENT, EVEN IF PRAGMATIC WORKS HAS BEEN
ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. Pragmatic Works makes no
representations or warranties with respect to the accuracy or completeness of
the contents of this document and reserves the right to make changes to
specifications and product descriptions at any time without notice. Pragmatic
Works does not make any commitment to update the information contained in this
document.
[TOP]
Click here to Download sample package
Click here to Download sample package
