Making DTS and SSIS Packages Portable

1
Making DTS and SSIS Packages Portable

• David Lundell, MBA
• MCDBA MCT MCSE MCSD
• Mutually Beneficial Inc
• David_at_MutuallyBeneficial.com

2
The Business Goal

• Be the hero
• Help the Sales People sell
• By fixing the CrossSell Pubs Products to
  Northwind Customers Process

3
The Setup

• Just fixed three issues in a crucial DTS package
• Great Job!
• Change Tested Approved
• No issues found in Testing!
• Now move it into Production!

4
The Technical Goal

• Move freshly fixed, tested and approved DTS
  Package to Production
• Without Changing It!
• Have it work in Production
• Have a reproducible process
• Able to rollback

5
The Challenge (Demo)

• Connection Information is hardcoded in the
  package
• Server and Database names in Connection Objects
• Database Names in the DataPump Task
• SourceObjectName
• DestinationObjectName
• Server Names in Package Logging
• Path Names hardcoded in various steps and Logging
  for the package and steps

6
The Process

• DTSBackup from sqldts.com
• Copies to new server
• Keeps Package GUID
• Save DTS Package as COM Structured file
• Check in to Source Control
• Save DTS Package as VB file
• Check in to Source Control
• Use RedGate DTSCompare to verify that your
  package is identical
• Make your package Portable

7
The Environments

• Separate Servers
• Easiest to work with
• as long as Database Names are the same
• Separate Instances
• Cant use local server for connections and
  logging (not to named instances)
• Separate Databases on same Instance
• Hardest as the database names get stuck into the
  Data Pump Tasks

8
The Solution (Demo)

• Disconnected Edit (if db names differ)
• Remove Database name from SourceObjectName
  DestinationObjectName on the Data Pump Task
• Dynamic Properties
• Modify Connection properties based on Global
  Variables (recommended option)
• Global Variables
• Active Script tasks
• If needed this is the best way to tweak variables
• DTSrun.exe Parameters
• Can Set Global variables at run time
• Can determine Path for Error File

9
Gotchas

• Separate instances
• log to file not SQL server
• Set as a command line parameter to dtsrun.exe
• Cant change logging after package starts
• Global variables are cAsE sENsItIvE
• Always use String type
• Have had no success passing in other data types
• Dynamic Properties
• Use ini files
• Or Global Variables (recommended)
• Other methods tend to be unreliable or not
  valuable
• Highly recommend careful logging

10
Logging DTS 2000 (Demo)

• Use a VBScript Task
• DTSPackageLog.WriteStringToLog
• Neater in the text file
• Does not preface it with "Error 1 (00000001),
  Description "
• DTSPackageLog.WriteTaskRecord
• Neater in the DTS Log
• Shows with a green check instead of a red X

11
Pizza Break
12
The Solution (SQL 2005 SSIS) (d)

• Data Sources
• Package Configurations (our focus)
• XML file
• Can be specified at runtime as a dtExec param
• SQL (stored in a table)
• Environmental Variable
• Registry Entry
• Parent Package Variable
• Specify Connection Strings at run time
• Job or
• dtExec command line parameter

13
What happened to Dynamic Properties? (Demo)

• Package Configurations
• Expressions

14
Logging SQL 2005 SSIS

• Can use multiple providers in same package
• Can specify any combination of providers for
  different containers
• Define level of detail at Container level
• All providers at that level get same information

15
Logging Providers

• Text File (default is csv)
• XML File
• SQL sysdtsLog90 in whatever database you
  designate
• Very different from in DTS all in one table
• SQL Profiler file
• Overwrites file every time
• Event Log
• Depending on level of detail and events it can
  fill up your log!

16
DTS 2000 running on 2005

• Can still log to a 2000 server
• If log to the 2005 server cant access the Logs
  like on 2000
• Logs to the tables in msdb
• No more Right Click the package and schedule
  package Presto Job
• With Encrypted Command line
• Use DTSRun.exe with command line parameters
• Use dtsrunui to generate command lines
• Even if your packages are on 2005

17
More Information

• How to interpret data that is logged by using a
  SQL Server 2005 Integration Services log provider
  
• http//support.microsoft.com/default.aspx?scidkb
  en-us906563
• Logging Providers
• http//www.databasejournal.com/features/mssql/arti
  cle.php/3562406
• Configurations http//msdn2.microsoft.com/ms141682
  .aspx