INF: DTS Package Development, Deployment and Performance

View products that this article applies to.

This article was previously published under Q242391

SUMMARY

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.

Back to the top

MORE INFORMATION

Making Data Connections Flexible

A 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:

   Function main()
   dim oPackage
   dim oConn

     'Get package object
     set oPackage = DTSGlobalVariables.parent
     'Get a handle to the desired connection.
     set oConn = oPackage.connections(1)
     'Modify the datasource.
     oConn.datasource = "newServerName"

     'Release object handles.
     set oPackage = nothing
     set oConn = nothing

    Main = DTSTaskExecResult_Success
End function

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:

239454 (http://support.microsoft.com/kb/239454/EN-US/ ) INF: ScriptPkg Tool Generates DTS Object Model Code to Help Use DTS Programmatically

Using this template as a reference, write your own code to generate a package based on user input.

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.

Back to the top

Controlling Packages with Global Variables

Global 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':

Function Main()
   dim oPackage
   dim oPump

        'Get a handle to the Package object.
	set oPackage = DTSGlobalVariables.Parent
	'Get a handle to the desired Datapump Task.
	set oPump = oPackage.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask   
	'Alter the datapump SQL statement.
	oPump.SourceSQLStatement = "select * from pubs..titles WHERE type _
  = ''" + DTSGlobalVariables("booktype") + "''"
	Main = DTSTaskExecResult_Success
End Function

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.

Back to the top

Passing Parameters to a DTS Package

The 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.

Function main()
  Dim fso	'This will serve as a handle to a file system object.
  Dim ofile	'Handle for accessing a file.

    'Get a file system object for manipulation files.
    Set fso = CreateObject("Scripting.FileSystemObject")

    'Open the text file.
    Set ofile = fso.OpenTextFile("c:\test.txt")

    'Read line from the file into the global variable.
    DTSGlobalVariables("myGlobalVar").Value = ofile.ReadLine

    'Close the "parameter" file.
    ofile.Close

    Main = DTSTaskExecResult_Success
End function

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.

Function main()
Dim oServer 		'DMO Server object.
Dim oResult		'Result set.
Dim sParamValue

	'Create a SQLDMO server object.
	Set oServer =  CreateObject("SQLDMO.SQLServer")

	' Make a connection to the local server.
	oServer.Connect ".", "sa"

	'Select the desired row from the table.
	set oResult = oServer.Databases("pubs").ExecuteWithResults_
("select  paramvalue from  my_param_table")

	'Retrieve the first row, first column from the results.
	sParamValue = oResult.GetColumnString (1,1)

	'Set the global variable.
	DTSGlobalVariables("MyGlobalVar").Value = sParamValue

	Main = DTSTaskExecResult_Success
End function

Back to the top

Writing Custom DTS Tasks in Visual Basic

It 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:

Custom tasks written in Visual Basic that implement an interface like DTS.CustomTaskUI MUST implement every event in the interface or they will cause an Access Violation.
If you are developing a Visual Basic custom task with a UI, it is recommended that you apply SQL Server Service Pack 1 (SP1). SP1 incorporates several enhancements to ease UI development.
Visual Basic Tasks are apartment threaded, while DTS is free threaded. In order to execute a task written in Visual Basic, it must be executed on the main thread or it will cause an Access Violation. DTS sets the appropriate flag by default. The user should not change this flag.

Back to the top

Obtaining Information on How to Code DTS Packages

In 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:

Producing Visual Basic examples by scripting an existing DTS package.
Showing how to code the DTS flatfile driver, which is not documented in SQL Books Online.
Converting a package into text so it can be added to and tracked in SourceSafe.

The following are several known issues with the ScriptPkg example:

Lookups for a data pump that are not scripted correctly.
If the package is too large then the text generated cannot be treated as one function in Visual Basic due to a limitation on the number of lines in one function. You'll have to break the script file into multiple functions on your own to get Visual Basic to compile the code.

Back to the top

Enhancing DTS Package Performance

Back to the top

Using Parallel Loads

DTS 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 -> B
A -> B and A -> C
A -> B and C -> B

Whether or not you use the same or different icons for the connection makes no difference.

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.

Back to the top

Insert Commit Size

The 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.

Back to the top

DTS and Distributed Transactions

DTS 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).

Back to the top

Error Message

When 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."

Back to the top