1 / 35

Making DTS and SSIS Packages Portable

The Business Goal. Be the heroHelp the Sales People sellBy fixing the CrossSell Pubs Products to Northwind Customers Process. The Setup. Just fixed three issues in a crucial DTS packageGreat Job!Change Tested

lucien
Download Presentation

Making DTS and SSIS Packages Portable

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    1. Making DTS and SSIS Packages Portable David Lundell, MBA MCDBA MCT MCSE MCSD Mutually Beneficial Inc David@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. Connection Object and Data Pump Task

    7. 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

    8. The Environments Separate Servers Easiest to work with as long as Database Names are the same Separate Instances Can’t 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

    9. 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

    10. Disconnected Edit – shorten to two part name

    11. Dynamic Properties

    12. DTSRun.exe passing Global Variables at run time Dtsrun /S Server /E /N PackageName /A PUBSSERVER:8=“MBINB1” /A PUBSCATALOG:8=“PUBS” /L C:\DTSLogs\PackageLog.Log

    13. Gotchas Separate instances log to file not SQL server Set as a command line parameter to dtsrun.exe Can’t 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

    14. 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

    16. Pizza Break

    17. 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

    18. SQL Configuration

    19. What happened to Dynamic Properties? (Demo) Package Configurations Expressions

    20. Expression

    21. Package Configuration

    22. XML Package Configuration File

    23. 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

    24. Logging inherited

    25. Logging Set individually

    26. 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!

    27. SSIS Job Step Ability to specify configurations at run time Ability to modify connection managers Ability to set Logging

    30. SQL Logging

    31. XML Log

    32. Text Log

    33. Trace Log

    34. DTS 2000 running on 2005 Can still log to a 2000 server If log to the 2005 server can’t 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

    35. 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?scid=kb;en-us;906563 Logging Providers http://www.databasejournal.com/features/mssql/article.php/3562406 Configurations http://msdn2.microsoft.com/ms141682.aspx http://www.mutuallybeneficial.com/index_files/dts_ssis_packages_portable.htm

More Related