What you need to know about DTS

1 Packages execute under the NT account of the user that executes them.

You must have privileges to access any files, directories or SQL objects utilised by the package.
This immediately apparent when designing a package, you cannot access what you cannot access!
Conversely when a scheduled via SQL Server Agent, the SQLServerAgent service must be run under an
account with appropriate rights, usually a Domain account.

A common problem is packages that fails when scheduled because
the SQLServerAgent service account does not have the same rights as the user who developed the package,
or it is running under the local system account which as no authentication outside the local server.

Another example of this appears when using the Send Mail task. The task will fail unless the package
is executed under an account with valid MAPI mail profile. This is not problem running under your own account,
assuming you have email, but it can cause problems when scheduled or executed by a user without access to email.

2 Packages execute on the machine from where execution was initiated.

If you use something like Visual Basic or Enterprise Manager to execute a package it will run on your machine.
Beware of this when specifying paths, as C:MyDir is not necessarily the same on your machine as the server,
although this is easily overcome by using UNC paths (\MachineNameShareNamePathFile.Ext).
A bigger issue in deciding were to run a
package from, is transfer of data via the network. If you are importing a file that is
resident on the server, consider the impact of running that package locally. The data will
have to do a round trip from the server, to your machine and back.

Another potential issue arises if you make use of any secondary components
such as the OLAP Services Processing Task (See
SQL Server Links).
When designing and testing Packages you need to install the Task kit locally,
and to run these as scheduled packages you also need to install the Task Kit on the server.

3 Dynamically change package properties utilising ScriptPkg.

ScriptPkg is a very powerful utility provided free on the SQL Server CD-ROM, and is installed as part of the Code Samples.
(See under Mssql7DevToolsSamplesDTS on your PC or the CD. Extract the archive DTSDesigner.exe and then look under the new folder Designer)

ScriptPKG will produce a text file of the VB code required to create a given package.
It’s greatest benefit lies in what it can teach us about the inner structure of a Package
and the properties associated with Tasks and Connections.

Many people want to lever more power from a package by dynamically changing a
property such as the file name or delimiter of a Text File Connection, or the SQL Statement
of an Execute SQL Task. The mechanics of this are easily achieved using an Active Script Task
that runs prior to the connection or task is used. The difficult part is getting a handle
on the relevant object and identifying the property to change. The output from ScriptPkg
reveals all this information for you to exploit.

There are four easy steps to making
the most of this:

  • Create a sample package
  • Run ScriptPkg on the sample package
  • Identify the object reference and property to change in the ScriptPkg output
  • Code the relevant information into an Active Script Task

Examples of how I’ve used ScriptPkg are evident in the Text File Connection file name article and in the development of GVCustomTask.

For a detailed description and examples of changing Task properties see the
DTS How to… articles:

  • How to dynamically change…Connections
  • How to dynamically change…Tasks

    There is also a Microsoft Knowledge Base Article on ScriptPkg now:

  • Article ID: Q239454
  • INF: ScriptPkg Tool Generates DTS Object Model Code to Help Use DTS Programmatically
  • http://support.microsoft.com/support/kb/articles/Q239/4/54.asp

    4 Physical Storage of DTS Packages and Backup

    Both Server and Repository packages are both physically held within the msdb database.
    Server packages reside in the sysdtspackages table. Repository packages are more complicated
    but identifiers are held in TfmPackage. This is illustrated in
    How to Obtain a List of DTS Packages.
    Backing up msdb will also backup all your server and repository based packages.
    Packages can also be saved to COM-structured storage files, which can be used as
    an alternative means of backing them up. (See DTSBackup for an example of this)

    5 Package Owner or Creator

    The package listing in Enterprise Manager has a column called Owner (DomainUserName),
    which refers to the packageObject.CreatorName. This is not the same as an Owner for Database objects
    and in the SQL 7.0 has no real function. Creator is a better description, as this is what it really is.
    It does not matter at all, if the user or domain does not exist on your network.

  • Latest Articles