INF: ScriptPkg Tool Generates DTS Object Model Code to Help Use DTS Programmatically (239454)

SUMMARY

The ScriptPkg tool generates a script for a given Data Transformation Server (DTS) package to help use the DTS object model programmatically. The ScriptPackage tool is contained in the DTSDemo.exe file on \DevTools\Samples\DTS folder SQL Server 7.0 CD.

One quick way to verify whether an OLE DB provider or ODBC driver works with DTS is to use DTS Wizards to create and save a package that imports or exports data involving that provider or driver. After you have saved a working package in SQL Server, you can use the ScriptPkg tool to script the package to a text file. The script generated by this tool gives you code examples that you can use in your application to manipulate DTS object model.

This tool is especially useful for data sources for which OLE DB provider properties are not documented, such as the DTSFlatFile provider for importing/exporting text files. The tools can also be used to help troubleshoot connection string problems with ODBC drivers and OLE DB providers.

MORE INFORMATION

Steps to Use ScriptPkg Tool

Open the compressed DTSDemo.exe file from the \DevTools\Samples\DTS folder SQL 7.0 CD into another folder.
Search for the Designer sub-folder and run the Visual Basic project, ScriptPkg.vbp.
Make an .exe file, ScriptPkg.exe, from the File menu in Visual Basic.
Design a DTS package using the SQL 7.0 Enterprise Manager and save it on local SQL Server.
Run ScriptPkg.exe and enter the package name. The tool writes a script file (packgename.txt) to the \TEMP folder.
Copy and paste selected portions of code into your Visual Basic application that uses the DTS object model. For example, you can load a Visual Basic DTS sample from the \DevTools\Samples\DTS folder and modify the source or destination properties to use the code generated by the ScriptPkg tool.

ScriptPkg is provided as a sample to help understand how to program to the DTS object model. The following are several known limitations of the sample:

ScriptPkg assumes that the package is saved in the local server (not the repository or a file) and that the SQL Server sa password is blank. To change these assumptions, you must modify the LoadServerPkg subroutine at the bottom of the code. There is commented-out code in LoadServerPkg for retrieving from the repository.
If the package contains too many columns or tables, the script that is generated will be bigger than Visual Basic can return from one function; the result is that the output script will be truncated. To work around this, you must modify ScriptPkg to manually break up the script into multiple smaller blocks of text and write each out separately.
Sometimes connection providers will incorrectly identify read-only properties. If the scripted package fails when setting a connection property, comment out the line that sets that property; it may not be needed.
If more than one lookup exists in the package, the script will not be generated correctly. This has been fixed in SQL Server 7.0 SP2.