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. Part 1 of this series showed how to export data from SQL Server to a CSV file triggered by a ColdFusion page. This part will show how to upload a CSV file to the server and import it into a new table. It will show how to pass information (a filename) to a DTS package from a ColdFusion page and use it in the package.

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. You will also need access to the ColdFusion Administrator.

Importing with DTS

The first part of the series showed a simple export procedure. The following steps will allow you to import a file into a new table in SQL Server:

  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 Tables tree.
  3. Right-click on Tables and choose All Tasks > Import Data. This brings up the Data Transformation Services Import/Export Wizard. Click Next.
  4. In the first screen, choose to import from a Text File from the dropdown list, and choose the NewProducts.csv file from the download package at the end of the article. We'll import it as a new table. Click Next.
  5. The file format should be Delimited, and the box for First Row has Column Names should be checked. The Text Qualifier is double quote by default, and the other settings should be left to defaults also. Click Next.
  6. Click the Comma radio button to choose comma-delimited text. Click Next.
  7. Your current server and database should be pre-selected in the next screen. If it isn't, choose your connection and the Northwind database. Click Next.
  8. The file and table should be pre-selected in the next screen (NewProducts table). Click the Transform button to change the data types for incoming data (as shown in figure 1). Make the ProductID, CategoryID, and SupplierID all data type of int, and change the data length of all others to 255.

Figure 1: The Transform button allows you to change data types
Figure 1: The Transform button allows you to change data types

  1. Finally, the next screen allows you to run the package. Choose the SQL Server option to save the package. Click Next.
  2. Save the package as "Import New Products", choose a login to own the package, and click Next.
  3. Click Finish.

At this point, the DTS package is run and also saved for later execution. Again, at any point you can open the Data Transformation Services tree in the Enterprise Manager and execute this package without having to follow the import steps.

Adding a Step Manually

DTS packages can be created or modified manually as well. We'll use the package we created and add a manual step to it to drop the table if it already exists. This is a simple SQL statement, as follows:

if exists (select * from dbo.sysobjects where id =
object_id(N'[Northwind].[dbo].[NewProducts]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Northwind].[dbo].[NewProducts]
GO

Tip: We can easily grab the SQL code to drop a table if it exists by right-clicking on a table in the Enterprise Manager, choosing All Tasks > Generate SQL Script, then clicking the Preview button to see the SQL code generated to DROP and CREATE the table. Then simply copy the code to use in your own script.

Next, open the Data Transformation Services tree > Local Packages, and double-click the package Import New Products. This opens up the DTS package designer. Each step that the package has to execute is shown visually in the designer, which can then be modified. We'll modify the Create Table step to add the DROP statement. Simply do the following:

  1. Double-click the Create Table step.
  2. The SQL statement to create the table should be in place already. Paste the DROP code above the CREATE code.
  3. Click OK.
  4. Right-click on the step, and click Execute Step to make sure it works properly. If so, save the package (using the Save icon.)

Global Variables

Next, we'll add a global variable to the mix. Global variables are nice because they can be passed in through the command line and used in your package. In this case, we'll be passing a file path as a global variable. To create a global variable, follow these steps:

  1. Right-click on the Connection 1 (the text connection) icon and choose Properties.
  2. Select and copy the Filename (path) text. We'll need this in a moment. Close the screen by cancelling.
  3. Right-click inside the package screen (on the white background) and choose Package Properties (or choose Package > Properties from the main menu).
  4. Click the Global Variables tab.
  5. Add a new global variable called FilePath. The variable should be a String.
  6. Paste the file path that you copied earlier as the value (shown in Figure 2).
  7. Click OK and save the package once again.

Figure 2: Setting a global variable
Figure 2: Setting a global variable

The variable is created, but now we need to assign it to the text connection. We can do this with a script. Follow these steps to add a script task:

  1. Right-click in the package and choose Add Task > ActiveX Script Task (or choose ActiveX Script Task from the main Task menu).
  2. Double-click the new task to bring up a script window.
  3. Paste the following script into the window (shown in Figure 3) and click OK.

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
  dim pkg
  dim txtConn
  set pkg = DTSGlobalVariables.Parent
  set txtConn = pkg.Connections("Connection 1")
  txtConn.DataSource = DTSGlobalVariables("FilePath").Value
  Main = DTSTaskExecResult_Success
End Function

Figure 3: Creating a script to set the file path
Figure 3: Creating a script to set the file path

We are simply assigning the global variable to the DataSource property of the text connection. This will be passed by our ColdFusion page, which we'll build next. Click OK and save the package.

Finally, add a workflow step between the new ActiveX Script Task and the Create Table task:

  1. Shift-click the two tasks
  2. Right-click on one of them and choose Workflow > On Success (shown in Figure 4).
  3. Click OK and save the package once again.

Figure 4: Creating the workflow between steps
Figure 4: Creating the workflow between steps

You can test the execution of the package now and make sure everything is correct.

Create the Upload Form

Uploading a file in ColdFusion is pretty straightforward. Details can be found in the ColdFusion documentation at http://livedocs.macromedia.com as well as several article on Community MX, including CFFILE: Uploading files to your server with ColdFusion. We'll assume you know how to do a file upload, and give the simple steps below:

  1. Create a ColdFusion page named UploadProducts.cfm and save it.
  2. Create a directory name uploads in your site, at the same level.
  3. Add a file field using Insert > Forms > File Field. When prompted to add a form tag, click OK.
  4. Name the field MyFile.
  5. Add a submit button next to the field.
  6. Add a bit of code to your page to handle the upload:

<cfif IsDefined("form.MyFile">
   <cffile action="upload"
    filefield="myfile"
    destination="#getDirectoryFromPath(getCurrentTemplatePath())#/uploads/"
    nameconflict="overwrite" />
</cfif>

Test the page now and make sure the uploads are saving properly.

DTSRUN.exe

Next we'll use the package created earlier to give your web site a way to upload a new product list when you run the ColdFusion template. As in the last part of the series, we'll be using the <cfexecute> tag and the command-line utility dtsrun.exe. You'll need to set up the command line utility, if you haven't already done so. Setting it up is described in part 1.

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

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) as we did last time:

dtsrun.exe /S myserver /U myloginname /P mypassword /N Import New Products

In addition, we are going to pass a variable filename to the package by specifying an external argument using the command /A to specify an argument:

/A argumentName:type argument

as in the following:

/A FilePath:8=C:\webroot\uploads\NewProducts.csv

Executing from ColdFusion

In part 1 we exported a CSV file of a products table. In this part, we'll upload a new products table from a CSV file, drop the current table, and import the data into the new table. From ColdFusion, we'll use the command line syntax within a <cfexecute> tag. The following syntax should work if placed directly below the <cffile> tag created earlier (within the <cfif>). We'll set a variable for the arguments to simplify:

<cfset dtsArguments = "/S dellserver /U mylogin /P mypassword /N Import NewProducts">
<cfset dtsArguments = "#dtsArguments# /A FilePath:8=#cffile.serverDirectory#/#cffile.serverFile#">
<cfexecute name="C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe"
  arguments="#dtsArguments#"
  timeout="1000"
  variable="dtsoutput"/>
<cfoutput>#dtsoutput#</cfoutput>

Save the page.

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: DTSStep_DTSActiveScriptTask_1 DTSRun
OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: Create Table [Northwind].[dbo].[NewProducts]
Step DTSRun OnFinish: Create Table [Northwind].[dbo].[NewProducts] Step DTSRun OnStart:
Copy Data from NewProducts to [Northwind].[dbo].[NewProducts] Step DTSRun OnProgress:
Copy Data from NewProducts to [Northwind].[dbo].[NewProducts] Step; 77 Rows have been
transformed or copied.; PercentComplete = 0; ProgressCount = 77 DTSRun OnFinish: Copy
Data from NewProducts to [Northwind].[dbo].[NewProducts] Step DTSRun: Package execution
complete.

You should also have the NewProducts.csv file now in the uploads folder.

To make this a little more safe, we'll wrap the whole thing in some error handling code and call it complete:

<!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>Untitled Document</title>
</head>
<body>

<cfif IsDefined("form.MyFile")>
<cftry>
  <cffile action="upload"
   filefield="myfile"
   destination="#getDirectoryFromPath(getCurrentTemplatePath())#/uploads/"
   nameconflict="overwrite" />
  <cfcatch>
    <p>There was an error uploading your file.</p>
    <cfabort>
  </cfcatch>
</cftry>

<cfset dtsArguments = "/S dellserver /U myusername /P mypassword /N Import New Products">
<cfset dtsArguments = "#dtsArguments# /A FilePath:8=#cffile.serverDirectory#/#cffile.serverFile#">
<cftry>
<p>
<cfexecute name="C:\Program Files\Microsoft SQL  Server\80\Tools\Binn\dtsrun.exe"
 arguments="#dtsArguments#"
 timeout="1000"
 variable="dtsoutput"/>
<cfcatch>
There was an error:
</cfcatch>
</cftry>

<cfoutput>#dtsoutput#</cfoutput></p>
</cfif>

<form name="form1" id="form1" enctype="multipart/form-data"
method="post" action="">
<input type="file" name="myfile" />
<input type="submit" name="Submit" value="Submit" />
</form>
</body>
</html>

Conclusion

This article presented a simple way to import some data using a DTS package and ColdFusion together. This part showed how to create DTS steps manually, and also how to pass arguments to the package as a global variable.