CounterPoint SQL DTS Errors

One of the software packages our company sells / supports is a Point of Sale package called CounterPoint by Radiant Systems. For more information about CounterPoint, check out our website at www.accelerando.net

One of our project managers was migrating a customer from the older Pervasive based version to the new SQL based version. CounterPoint provides a utility to import historical data the uses the DTS engine to push the data into the new databases.

As you may know, DTS is a depreciated feature and can be difficult to get running if running on a new system as the SQL installs no longer include it.

There is a backwards compatibility toolkit available for SQL that provides DTS capabilities and this was installed. However, CounterPoint was still firing errors when the DTS package kicked off. The errors we were getting in the log file when running were

Step 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:ActiveX Scripting Transform 'DTSTransformation__4' was not able to initialize the script execution engine.

Ultimately, this came down to missing DLL files in the DTS implementation. Following is a list of the steps I came up with to enable DTS functionality.

Requirements

• SQL 2005 Backward Compatibility Components (source below)
• The redistributable DTS files (available on SQL 2000 Media)

Steps

• Install the SQL 2005 Backward compatibility components
• Open a command prompt and change directory to C:\Program Files\Microsoft SQL Server\80\Tools\Binn
• Register the following list of DLL’s using the syntax regsvr32 dllname.dll
  • dtsffile.dll
  • dtspkg.dll
  • dtspump.dll
  • axscphst.dll
  • custtask.dll

In our case, the axscphst.dll file was not registered because it had not installed with the Backward Compatibility Components. I was able to retrieve the file from the SQL 2000 media

Note that each of the above DLL files has an associated .RLL file that is located in Resources\1033 under the Binn folder listed above. The .RLL files are also available from the SQL 2000 Media.

NOTE: For my fellow co-workers, I have a zip file put together with all the files involved. It’s available on the ADrive FTP site. Contact me for connection information if needed. There’s a publically downloadable link here - http://tinyurl.com/sql2000dts

In coming up with this solution, I relied on the following sources:

• Redistributing DTS with your program
  • List of files needed to redistribute DTS
• SQL 2005 Feature Pack - Microsoft SQL Server 2005 Backward Compatibility Components
  • Install pack for the DTS files
• Technet: How to install support for DTS Packages
  • A listing of the procedures for installing DTS on various platforms