Many people use SQL Server to store their data, however SQL Server is much more involved than simply a server that allows storage for data. Data Transformation Services (DTS) is part of SQL Server and allows you to import and export data, manipulate files on the system, use FTP, among other things. Using some of SQL Server's built-in DTS functionality along with ColdFusion gives your web application access to some of this functionality.

This article assumes some knowledge of the Enterprise Manager and SQL Server. You can open up the Enterprise Manager on your local machine to follow the tutorial. You will need a login to SQL Server that has access to the MSDB database, which is where DTS package information is stored.

Starting with DTS

The simplest way to use DTS is to import or export data. Follow these steps to export a table:

  1. Open Enterprise Manager to the Northwind database. If you don't have it installed, any database will do, but you'll have to adjust any table references made in this article.
  2. Open the Northwind tree.
  3. Right-click on Tables and choose Export Data from the All Tasks fly out. This brings up the Data Transformation Services Import/Export Wizard. Click Next.
  4. Your current server and database should be pre-selected in the first screen. If it isn't, choose your connection and the Northwind database. Click Next.
  5. We will export to a text file, so choose Text File as the destination.
  6. Click the browse button to browse to a location on your hard drive to store the text file. For the purposes of this demo, store it in your local ColdFusion webroot in a folder named downloads. Name it "Products.csv" and click Next.
  7. Leave the next screen set to the default. As you can see, you can export the entire table or use a query to filter your data. We'll export the entire table. Click Next.
  8. For the data source, choose the Products table. Leave the Delimited button checked and check the "First row has column names" box. Leave the rest set to defaults as well and click Next.
  9. Finally, the next screen allows you to run the package. Up until this point, you may have done this part of the procedure many times, however you'll notice a checkbox on the screen that allows you to save the package. Check this box before clicking the Next button and choose the SQL Server option.
  10. Save the package as "Sample Northwind Products Export", choose a login to own the package, and click Next.
  11. Click Finish.

At this point, the data is exported, but more importantly the DTS package is saved for later execution. This will come in handy for us. Check the directory where you exported the Products.csv file to and make sure it's there. If it is, examine it to make sure it's correct, then delete it. We'll be using this location in our ColdFusion code later. If it's not there or something was wrong with the file, go back and check your steps.

Now, at any point, you can open the Data Transformation Services tree in the Enterprise Manager and execute this package without having to follow the export steps. Try it now: open Data Transformation Services , click Local Packages. Here you can view all of your saved DTS packages, and use the right-click menu to Execute the package. This will dump the text file directly to your pre-selected location without having to deal with any dialog boxes.

Executing the package.
Figure 1: DTS pane in the Enterprise Manager

Now we'll use this package to give your web site a fresh product list every time you execute a pre-defined ColdFusion template. To make DTS work in ColdFusion, we'll be using the <cfexecute> tag and the command-line utility dtsrun.exe.

Tip: You can find out more about DTSRUN.exe from the MSDN web site.

In order to use DTSRUN.exe, you'll need these prerequisites:

  1. Know where the DTSRUN.exe file exists on your system. It will be in C:\Program Files\Microsoft SQL Server\80\Tools\Binn by default, but you can easily locate it using the Windows file search from the Start menu. If you don't have SQL Server installed on the ColdFusion server, you can copy the DTSRUN.exe and DTSRUN.dll file to a specified location in the ColdFusion server and still use it's functionality.
  2. ColdFusion Server needs to have permission to run the DTSRUN.exe program. The steps to give permission will vary depending on your system.
  3. You need a login within SQL Server that is either an Administrator or a user that has specific access to the MSDB database as well as the database you are working with (in this case Northwind.)

With that in place, let's look at the command line utility.

DTSRUN.exe

The command line utility needs several arguments to run: the server name, the login name and password, and the package name. We supply those using the following syntax (from a command line, all one line):

dtsrun.exe /S myserver /U myloginname /P mypassword
/N Sample Northwind Products Export

In addition, we can also pass other arguments to it, which we will do in the next part of the series.

Try it out from a command line by navigating to the location of the dtsrun.exe file and running the command. It should work flawlessly at this point.

ColdFusion

Let's bring ColdFusion into the equation now. The scenario is this: we want a user or site administrator to be able to download the latest product list from our server. From ColdFusion, we do the same as we did at the command line, only using a <cfexecute> tag. The following syntax should work, in a default blank ColdFusion page (with html and body tags, etc). We'll set a variable for the arguments to simplify:

<cfset dtsArguments = "/S dellserver /U mylogin /P mypassword /N Sample Northwind Products Export">
<cfexecute name="C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe"
  arguments="#dtsArguments#"
  timeout="1000"
  variable="dtsoutput"/>
<cfoutput>#dtsoutput#</cfoutput>

Save this page as export_products.cfm

The dtsoutput variable will give us a report as to the success or failure of the package execution. In this case, after browsing the file, I get this result:

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: Copy Data from Products to
J:\jrun4\servers\cfusion\cfusion-ear\cfusion-war\test\downloads\Products.csv Step DTSRun OnProgress: Copy
Data from Products to J:\jrun4\servers\cfusion\cfusion-ear\cfusion-war\test\downloads\Products.csv Step; 77
Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 77 DTSRun OnFinish: Copy Data
from Products to J:\jrun4\servers\cfusion\cfusion-ear\cfusion-war\test\downloads\Products.csv Step DTSRun:
Package execution complete.

You should also have the Products.csv file now in the downloads folder once again. We can now add the following the to export_products.cfm file to give the user the option to download the file:

<cflocation url="downloads/products.csv">

Now, upon execution of the DTS package, the user will automatically be redirected to the file and prompted to download.

To make this a little more safe, we'll wrap the whole thing in some error handling code and call it complete. The code for the completed page is as follows, and is also included in the download package:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Export Products</title>
</head>

<body>
<cfset dtsArguments = "/S myservername /U myusername /P mypassword
/N Sample Northwind Products Export">
<cftry>
  <cfexecute
   name="C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe"
   arguments="#dtsArguments#"
   timeout="1000"
   variable="dtsoutput"/>
  <cflocation url="downloads/products.csv">
  <cfcatch>
    <p>There was an error: </p>
    <p><cfoutput>#dtsoutput#</cfoutput></p>
  </cfcatch>
</cftry>

</body>
</html>

Conclusion

This article presented a simple way to export some data using DTS packages and ColdFusion together. The next part will show how to import a text file that a user uploads, using global DTS variables and arguments.