Customize DTS

John Woods

In the August 1999 issue (see "Build Your Own Palm Pilot Data Collector"), John Woods described how he implemented a Palm Pilot to SQL application. Here, he tackles DTS (Data Transformation Services), commenting briefly on how it compares with bcp and BULK INSERT, and then describing a clever technique that uses a stored procedure to dynamically modify a DTS package.

Lots of us have a recurring need to import text data into our central data store. If you’ve worked with Microsoft Access, you know how nicely it handles text–even HTML text in Access 2000–via its TransferText action (which you can run in VB using the TransferText method of the DoCmd object).

Wouldn’t it be nice to have this power and flexibility directly in SQL Server? Well, you can. Instead of having to rely on sophisticated client processing (and the attendant network traffic) or the usual hassles of bcp format files, you can use DTS in conjunction with a custom stored procedure that dynamically modifies the DTS specification. But before I describe this technique, I’m going to briefly review the limitations of bcp and the new BULK INSERT statement.

Why not just bcp or BULK INSERT?

Anyone who’s used bcp to transfer text files with anything but the simplest of transformation requirements knows that creating proper format files isn’t always easy. There are articles in the Knowledge Base that describe how to modify a bcp format file to work with text files that have more or fewer columns than the destination table. There are yet more articles on how to work with datetime information, and the particularly ugly problem of text files that have double quotes surrounding character data. This last problem alone can be enough to scare you away from bcp format files forever.

The new BULK INSERT statement is really just a subset of the bcp functionality with a (somewhat) friendly T-SQL interface. BULK INSERT only works for importing data and requires special handling for all but the simplest import requirements.

More than just bcp on steroids

Enter DTS. You could say that DTS is the most powerful bcp ever–with a graphical interface and the ability to chain multiple data exchanges together in a predefined workflow, with parallel execution, conditional branching, customized column transformations, and . . . well, the list goes on.

Although DTS can be much more than just a supercharged bcp, this article will describe a technique for emulating the Microsoft Access text import (specifically, its TransferText action) using a DTS package combined with a stored procedure. For this example, we’ll create a simple DTS package that performs the same function as the Access text import wizard. Then we’ll write a stored procedure that dynamically modifies the definition of the DTS package in order to change the source file specification (that is, full path to the text data), and then execute the package to complete the import. The stored procedure will include a few enhancements, such as the option to initially clear the destination table and to import multiple files with one call to the procedure.

Creating the base DTS package

We could just run the DTS Import Wizard to create our simple package, but it’s more interesting to carry out these three steps manually from the DTS designer window. We must define three things in our package:

In the SQL Enterprise Manager, expand the Data Transformation Services folder, right-click on Local Packages, and select New Package. The DTS designer window opens with the proverbial "blank slate" for creating a graphical view of our import procedure.

Specify the source text file connection and destination database

Right-click in the white space in the designer pane and choose Add Connection. In the Connection Properties dialog box, enter a name for the text file next to the New Connection radio button. Let’s tax our creative abilities and call the connection TextFile. In the Data Source drop-down box, scroll to the bottom and choose Text File (Source). Then click the Builder Button next to the File Name text box and locate a sample text file in your file system. Click on the Properties button to open the Text File Properties dialog box. This dialog box is similar to the selections available with the Microsoft Access import wizard. Make appropriate selections for your text file format, and when you’re finished, press OK to complete the TextFile connection.

Right-click again in the blank designer pane and choose Add Connection. In the Connection Properties dialog box, enter a name for the SQL Server destination–let’s call the connection SQL70. The Data Source drop-down already displays the Microsoft OLE DB Provider for SQL Server. Specify the name of your Server and the type of logon authentication required. We’ll use a trusted connection for this example. Choose the database in the drop-down list that contains the destination table, and press OK to finish the SQL70 connection.

Specify the data transformation from TextFile to SQL70

Now that we have two connections (a source and a destination), we must specify the transformation that must take place between the two. For this example, we’ll assume that some of the columns in the source text file must be skipped when importing into the destination table in SQL Server. Click on the TextFile icon in the designer pane and then hold down the Shift key and click on the SQL70 icon so that both are selected. Then right-click and select Workflow, then Transform Data. An arrow appears between the two connections, pointing from TextFile to SQL70. Double-click the arrow to open the Data Transformation Properties dialog box. On the Source tab, provide a description for the text file. On the Destination tab, select a table in the SQL70 database. On the Transformations tab, first delete all of the connecting lines that appear joining column names from the text file to column names in the destination table. Then click on the first column in the text file list box, hold down the Shift key, and click on the last column to select all columns. Repeat the same process on the destination columns list box. In the New Transformation drop-down list, choose ActiveX Script, and then press the New button. In the ActiveX Script Transformation Properties dialog box, edit the text in the scrolling script window by deleting rows that refer to columns in the source text file that should be skipped, and then modifying the destination field names as appropriate to properly map the source columns to their destination columns. Click OK to finish the transformation.

On the Package menu, choose Save As and specify a package name. We’ll use the name TextImport. Leave the default location as SQL Server to save the package on the Server. Test the new package by selecting Execute from the Package menu.

Writing the ImportText stored procedure

Our stored procedure makes use of the special OLE Automation stored procedures in SQL Server to modify and execute the saved Package definition that was just created. The server must have the resource library DTSPKG.RLL installed (on my machine, it’s in the Binn\Resources\1033 folder). If you decide to work with DTS programs in VB or Access, then you’ll need to add a reference to the Microsoft DTSPackage Object Library (which should be available as long as you’ve installed SQL Server’s Enterprise Manager client tools). Here’s the procedure (without the error handling code after each automation call):

Create Procedure TextImport 
  @PkgName varchar(50), @Server varchar(25), 
  @FileConnName varchar(50), 
  @ClearTableName varchar(30), 
  @FilePath varchar(8000)
Declare @Pkg int, @dataSource varchar(50), @conn int, 
@hr int
Set nocount on
Exec @hr = sp_OACreate 'DTS.Package', @Pkg out
-- Error handling goes here after each automation call
-- See BOL for sp_OAGetErrorInfo 
-- Load the Package definition 
-- Flags is for Trusted Connection
Exec @hr = sp_OAMethod @pkg, 'LoadFromSQLServer', 
NULL, @Server, @PackageName = @PkgName, @Flags = 256
-- Get a handle to the TextFile connection
Declare @propName varchar(50)
Set @propName = 'connections(' + @fileConnName + ')'
Exec @hr = sp_OAGetProperty @Pkg, @propName, @conn out
- Optionally clear the destination table
Declare @stmt nvarchar(100)
If DataLength(@clearTableName) > 1
  BEGIN
  Select @stmt = N'delete ' + @clearTableName
  Exec @hr = sp_executesql @stmt
  END
-- @FilePath can have multiple full path file specs 
-- separated by commas (no trailing comma)
Declare @temp varchar(8000), @ipos int, @total int
Set @total = Datalength(@filePath)
Set @ipos = CharIndex(',',@filepath)
While @ipos > 0
  BEGIN
  Select @temp = left(@filePath, @ipos — 1)
  Select @filepath = substring(@filepath,.@ipos + 1, 
@total - @ipos + 1) ##indent?
  Set @ipos = CharIndex(',',.@filepath)
-- Set the FilePath and Execute the Package
  Exec @hr = sp_OASetProperty @conn, 'datasource', @temp
  Exec @hr = sp_Oamethod @pkg, 'Execute'
  END
-- Execute the first file (when only one) or the 
-- last file when more than one
Exec @hr = sp_OASetProperty @conn, 'datasource', @filepath
Exec @hr = sp_Oamethod @pkg, 'Execute'
-- Cleanup
Exec @hr = sp_OADestroy @conn
Exec @hr = sp_OADestroy @pkg

Call the stored procedure as follows to clear the destination table and import three text files:

Exec 'ImportText', 'MyServerName', 'TextFile', 
'MyTableName', 
  '\\Machine\Share\File1,\\Machine\Share\File2,
  \\Machine\Share\File3'

Download TXTIMPRT.SQL