DTS Notes

Ø  Simple DTS package

§  Source connection

§  Destination connection

§  Datapump task

§  Note: Different types of connection managers

·         ODBC for AS400

·         ODBC for Oracle

·         Native client tends to have better performance than OLEDB – however, in SSIS destination OLEDB allows the developer to define commit batches

Ø  Dynamic Properties task

§  Modifies properties of tasks at runtime with resources outside the task

§  Outside resources:

·         Global variable

·         Constant

·         INI File

·         Data file

·         Query that uses the first row returned

·         Environment variable from the System or User variables

§  INI example to manage server connection between development & production environments

·         Create INI file

·         Add a dynamics property task to the package

·         Add property definition

¨       Double-click dynamic properties task to edit

¨       Click on Add

¨       Navigate through tree & properties to find which property to define

¨       Click on Set

 

    

Ø  INI file is default source

Ø  Browse to the location of the file (needs to have the same relative location on both servers)

Ø  Select section defined in INI file

Ø  Select key from INI file

Ø  Verify value in Preview section

·         Make sure dynamics property task is added to the beginning of the workflow so properties are configured correctly for runtime

Ø  Using Global Variables

§  How to create global variables

·         Right click in empty area of package

·         Select package properties in the context menu

·         Select global variables tab

·         Make sure Explicit Global Variables is checked

·         Add/configure global variables

·         Note: we are using GV1, GV2 naming convention

§  How to use global variables/parameters in Execute SQL task

·         Input parameters

¨       Add execute SQL task

¨       Write SQL query and put in “?” in where clause

¨       Click on “Parameters”

¨       Make sure “Input Parameters” tab is selected

¨       Map parameters to global variables to make SQL statement dynamic

 

·         Output parameters

¨       Add execute SQL task

¨       Write SQL query

¨       Click on “Parameters”

¨       Select “Output parameters” tab

¨       Select output parameter type

¨       Map parameter to global variable

§  Active X script for additional functionality (get around parameter limitations)

·         Add Active X script

·         Declare Active X variables (package, connection, SQL string)

·         Build SQL string concatenating strings & variables

·         Assign SQL string to task SQL source (can assign to data pump tasks or Execute SQL tasks – browse in dynamic properties task to find out name of property)

Ø  Active X script to dynamically change email message content based upon output parameter

Ø  Active X script to manage changing text file destinations