Parameterized DTS Packages

A natural thing to want to do with a DTS package is to parameterize it so that, for example, it can work with different data sources/destinations than the ones it referenced when it was originally built. Things like global variable values, connection properties, object names, and the like are natural targets for parameterization.

There are a couple of ways to parameterize DTS packages. The first is to use a Dynamic Properties task. A Dynamic Properties task allows you to set the value of any property in a package from one of six sources:

  1. A global variable from the package

  2. A value in an INI file

  3. An environment variable

  4. A T-SQL query (only the first column of the first row in the query result set is used)

  5. A data file

  6. A constant ...

Get Guru's Guide to SQL Server Architecture and Internals, The now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial