What you need to know about DTS
November 28, 1999
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.
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 (\\MachineName\ShareName\Path\File.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.
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 \Mssql7\DevTools\Samples\DTS\ 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:
There is also a Microsoft Knowledge Base Article on ScriptPkg now:
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)
The package listing in Enterprise Manager has a column called Owner (Domain\UserName), 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.