Article ID: 242391 - View products that this article applies to.
This article was previously published under Q242391
The purpose of this article is to provide some detailed information to assist in the development and deployment of SQL Server 7.0 Data Transformation Services (DTS) packages. Hints for improving package performance are also provided.
For information on this subject in SQL Server 2000, see SQL Server Books Online.
Making Data Connections FlexibleA DTS package is a completely self contained unit. All connection information is read into the connection properties collection at design time, even connection information in UDLs and ODBC DSNs. If you change the ODBC DSN or the OLEDB UDL after creating the package, that change has no effect on the package. This is important to remember when trying to migrate packages from a development environment to production.
When you use SQL Server data connections, use (Local) for the server name to make it easier to move the package from server to server since the name of the server is resolved at package execution time. The one caveat to this approach is that it requires that the package be run and edited on the SQL Server computer itself.
You can also use an ActiveX script task or a wrapper program to modify package properties at run-time. To modify the package from within itself, create an ActiveX Script task that precedes the data pump tasks and modify the package while it runs. To obtain a handle to the package reference DTSGlobalVariables.Parent. From there you can change any of the package properties, although it requires a little programming skill.
Following is an example from the SQL Books Online topic, "ActiveX Script Tasks", on how to change the DataSource connection property with an ActiveX script:
If you want more control and do not mind even more coding, consider modifying the package with an external program before it is run or creating the package from scratch.
To create a program to load and modify a package, create the template package using the DTS Wizard or the DTS Designer and save it. Write a Visual Basic program that loads the package and modifies the desired properties, then call the Execute method on the package object to run the package.
To create a program to generate a package from scratch, create a prototype package using the DTS Wizard or the DTS Designer and save it to the local SQL Server. Use the ScriptPkg utility in the following Microsoft Knowledge Base article to create a template for the package code:
239454Using this template as a reference, write your own code to generate a package based on user input.
(http://support.microsoft.com/kb/239454/EN-US/ )INF: ScriptPkg Tool Generates DTS Object Model Code to Help Use DTS Programmatically
Note that for both of the preceding situations, the Visual Basic project needs to have a reference to the Microsoft DTSPackage Object Library.
IMPORTANT: When executing a package from a program written in Visual Basic and monitoring package or task events, all steps in the package must be configured to execute on the main thread. This is due to a limitation in Visual Basic that prevents it from properly handling multiple simultaneous calls to its events.
Controlling Packages with Global VariablesGlobal variables are only accessible from ActiveX script tasks, Workflow scripts, and ActiveX transformations. They cannot be used directly in SQL statements or connection properties. However, an ActiveX script task can be used to modify a SQL Statement or connection properties based on a global variable. Here's an example of ActiveX script task that can be used to change the SQL Statement for a data pump task based on the global variable named 'booktype':
NOTE: The description of a task is displayed in the DTS designer, but it cannot be used to directly reference a task in an ActiveX script. The name of the task should be used to reference the task from an ActiveX script. To get the name of the task look at the workflow properties for the step name. The task name is the same as the step except the word 'Step' is replaced with 'Task'. For example, DTSStep_DTSDataPumpTask_1 becomes DTSTask_DTSDataPumpTask_1.
Passing Parameters to a DTS PackageThe DTSRun program does not accept any command line parameters that can be passed to the package at execution time. To pass information to a package at run-time, the parameters must be read from a file or queried from a database table programmatically.
NOTE: If SQL Server 2000 tools or a named instance of SQL Server 2000 is installed on a server that has SQL Server 7.0 installed as the default instance, the DTSRun program can accept the command line parameter /A to pass values from the command line into global variables defined in a SQL Server 7.0 DTS Package.
Here is an example of an ActiveX Script Task that reads a line from a text file to set a global variable. This global variable could then be used to modify package behavior as shown in the "Controlling Packages with Global Variables" section of this article.
The following example shows how to use an ActiveX Script task to read parameters from a SQL Server table. The code reads the value of the paramvalue column in the my_param_table and uses it to set a global variable. This example uses SQL Distributed Management Objects (DMO) to interact with SQL Server, but you can accomplish the same thing by using ActiveX Data Objects (ADO) or another data access method.
Writing Custom DTS Tasks in Visual BasicIt is possible to write custom DTS tasks in Visual Basic. An example of a Custom Task in Visual Basic without a UI is available in the \Devtools\Samples\DTS directory on the SQL Server CD-ROM. Following are several common issues encountered when dealing with custom Visual Basic tasks in DTS:
Obtaining Information on How to Code DTS PackagesIn SQL 7.0, there are several samples on the CD-ROM under the \Devtools\Damples\DTS folder. These include a sample Custom Task (Visual Basic), and a Custom Transform (C++). Some sample packages are also in the self extracting executable DTSDemo.exe.
The most versatile example is ScriptPkg, which is part of Dtsdemo.exe. ScriptPkg contains the Visual Basic source code that converts a package saved to the local server into Visual Basic code. This example is best used for:
Enhancing DTS Package Performance
Using Parallel LoadsDTS is capable of running multiple tasks in parallel. However, each connection in a DTS package can only handle one thread at a time. That means that the following data pumps in a package are all serialized:
A -> B and A -> BWhether or not you use the same or different icons for the connection makes no difference.
A -> B and A -> C
A -> B and C -> B
The only way to get parallel execution of tasks is to create multiple connections to the same database with different names. For example A -> B and C -> D are executed in parallel even if B and D are different connections to the same server and database.
Precedence constraints can be used to enforce task execution order and prevent parallelism for selected tasks as necessary.
Insert Commit SizeThe most important parameter on the data pump task is the Insert Commit Size. This property is only relevant when the destination is SQL Server. It controls how many rows are bulk inserted before the transaction is committed. By default this is 0; all of the rows are part of a single transaction. This is the safest setting because on any error SQL Server rolls back all changes. However, it can require a very large transaction log, lead to very long response time when a package is cancelled and be counter productive when a single row at the end of a large load causes the whole load to fail. A noticeable pause is seen at the end of each commit interval while the rows are committed. A commit size of 0 is fastest, but other good values are 10,000, 1000 and 1 in order of decreasing speed. 1 is useful because only 1 row is rolled back on any failure.
Note that setting the error count to any number > 1 has no effect if the insert commit size is 0. The error count counts the number of transactions with errors, not the number of rows.
In SQL Server 7.0, you can set the Insert Commit Size value in the Data Movement section on the Advanced tab of the properties for the transform data task.
DTS and Distributed TransactionsDTS packages provide support for distributed transactions for those providers that support DTC. The DTC service must be running for package and step transactions to work. In addition, each provider in the transaction must support DTC.
The package transaction properties are controlled on the Advanced tab of the package properties.
By default, each step in a package handles it's own transactions and does not coordinate it's transactions through DTC. To enlist a task in a DTC transaction you must set the "join transaction if present" check box in workflow properties of the task. Other DTC transaction options for a task are to "Commit transaction on successful completion of this step" and "Rollback transaction on failure."
Note that packages do not support Microsoft Transaction Server (MTS).
Error MessageWhen you use Local for the database server for a connection from a package and the package is then edited from another server and that person wants to look at the transformations, the following error message occurs:
Error Source : Microsoft OLE DB Provider for SQL Server Error Description : Cannot open database requested in login 'xxxxxxx'. Login fails."