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 (\\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.
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 \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:
Examples of how I've used ScriptPkg are evident in the Text File Connection file name article and in the development of GVCustomTask.
- 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
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
5 Package Owner or Creator
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.