ColdFusion Muse

Coldfusion and DTS - Easy Exporting

What do you do when you are required to provide a CSV export of a large dataset from MS SQL Server? You could use query2csv and export it in Coldfusion - but don't be surprised if you end up taking a long time to complete that request. Coldfusion, for all it's advantages, it is not suited to to this sort of thing. We had a process that exported 30,000+ records (just a few fields) for the purpose of sales calculations. In Coldfusion this resulted in a 6 meg file - that doesn't sound like much, but the process could take 5 minutes or more. We thought of DTS, but one of our requirements was to make the file accessible via FTP. Fortunately there was an easy way.

In short, we used a DTS package on the CF Server to create the file locally with DTS services. We shortened the time from 5 minutes to about 35 seconds (not bad). Here are the steps.

Step 1: Create the DTS on the MS SQL Server

Go through the process of creating the DTS. This might involve simply creating a query and using it in the DTS or it might involved a stored procedure, view or whatever - the main thing is that the DTS uses a query to export to a "flat file". The flat file option will ask you for a location. The location will be on the local drive (where you are running it from). Then change the path in step 2.

Step 2: Change the Path in the DTS Package

Determine the correct path of the file on the web server and enter that path into the package using the package designer. Note: You will not be able to test this step until you run the DTS package from the CF server.

Step 3: The Coldfusion Code

You will now write code that resembles this sample.

<cfscript>
   create dts package object
pkg = createObject("COM","DTS.Package");
      // load package       
pkg.LoadfromSQLServer
   ("NameOfSQLServer",
   "SQL_Username",
   "sql_password",
   0,
   "",
   "",
   "",
   "nameOfTheDtsPackage",
   "");
      // execute       
pkg.Execute();
   </cfscript>
If your path is correct in the package you will be able to look in the designated folder and find the newly created file.

DTS's Other Uses

Incidentally, I've found DTS to be useful for moving data around outside of MS SQL. For example, with enterprise manager and 2 ODBC connections to 2 different MySQL servers you can migrate data back and forth willy nilly. Or how about from dbase to a flat file? Or from a flat file to Oracle? It's easy to forget that DTS is designed to translate data between a number of different platforms and file types - not just MS SQL.

Comments
Sami Hoda's Gravatar Nice!

But double check your title. Says "Colfusion".

Sami
# Posted By Sami Hoda | 7/26/06 8:14 PM
mkruger's Gravatar Doh'! ... that will teach me to drink and blog - thanks for the heads up :)
# Posted By mkruger | 7/26/06 8:32 PM
Mike Kelp's Gravatar Great post!

This is an incredibly useful idea.

Mike.
# Posted By Mike Kelp | 7/27/06 1:58 AM
Falcon79's Gravatar 1)
A problem:
the DTS create a file on db-server and i've cfmx on app-server, how i can get a file without ftp?

I think the query2csv that is only solution for this problem..

2)
csv2db exists? or i can read only the file step by step (loop all the row with insert)
# Posted By Falcon79 | 7/27/06 5:03 AM
mkruger's Gravatar Falcon,

I'm sorry if I wasn't clear - but this is exactly the problem that the solution is intended to fix. DTS creates a file in the location specified by the file mapping. If uses the drive of the system RUNNING THE PACKAGE - not of the DB server.

That's why one of the steps is to alter the path of the destination file so that it matches the web server.

-Mark
# Posted By mkruger | 7/27/06 8:28 AM
Tony Petruzzi's Gravatar I'm having a hard time trying to figure out why you would want to do this.

You could easily create a SQL JOB to run the DTS package every 5 minutes to create the file. You can also use DTS to move the file to an FTP server.

The only thing that CF should be doing is grabbing the file for download using cfcontent.

Just looking at this code and then hearing the time it takes, something like this could easily bring the server down or to a crawl.

Maybe I'm wrong, who knows.
# Posted By Tony Petruzzi | 7/27/06 9:00 AM
mkruger's Gravatar Tony,

Please explain how you could use DTS to ftp the file OUT. I know you can use FTP to retrieve a file for import or transformation - but I was not able to find a way to PUT the file anywhere - that's why we chose to do it this way. Can you post some steps?

-Mark

(in addition, DB servers tend to be pretty "locked down" - only allowing DB interaction and restricting other protocols)
# Posted By mkruger | 7/27/06 9:09 AM
Mkruger's Gravatar Tony,

As for time - it's easy... a few lines of code. As for performance - it's quite fast for routines of this nature - at least as fast as FTP'ing a file of several megs into the server :)

-Mark
# Posted By Mkruger | 7/27/06 9:10 AM
Scot's Gravatar I get an error using this code:

An exception occurred when instantiating a Com object.
The cause of this exception was that: java.lang.RuntimeException: Can not use native code: Initialisation failed.


Does this mean I do not have the required dts objects registered on the CF server correctly?
# Posted By Scot | 7/27/06 9:20 AM
mkruger's Gravatar You do have to have it registered (it's part of mdac I believe) but that is not the source of this error. If it wasn't registered you would get a "com not found" type of error. I believe that the source of this error is that "createobject()" is not enabled - or perhaps they have a way of specifically locking down COM.

I could be wrong - perhaps a reader will correct me.

-mark
# Posted By mkruger | 7/27/06 9:52 AM
Tony Petruzzi's Gravatar http://www.sqlteam.com/item.asp?ItemID=12408

shows you exactly how to do this.
# Posted By Tony Petruzzi | 7/27/06 10:00 AM
Scot's Gravatar I have no tag limitiations, all wide open (intranet). I would love to execute a DTS package from a CFM page.
# Posted By Scot | 7/27/06 10:34 AM
todd's Gravatar Anyone look into saving a DTS package as a Job and using cfquery to run it? I'm thinking something like this:

<cfquery...>
exec sp_startJob @jobname='job'
</cfquery>

Haven't tested this, just wondering if anyone knows pros/cons...

Interesting discussion here.
# Posted By todd | 7/27/06 11:08 AM
todd's Gravatar or <cfstoredproc....>
# Posted By todd | 7/27/06 11:08 AM
Falcon79's Gravatar ok todd, but export file csv will be created on db-server! or not?
# Posted By Falcon79 | 7/27/06 12:40 PM
Gus's Gravatar Todd,

I like to use sp_startjob so I don't have to deal with using com or installing things on the webserver that maybe shouldn't really be there, like MDAC.

I also generally include some logging and reporting in the DTS package so I can have a view into how/when/what ran.

Gus
# Posted By Gus | 7/27/06 2:14 PM
Fernando da Silva Trevisan's Gravatar This "MySQL" in the phrase is correct or am I misunderstandig your post?

"For example, with enterprise manager and 2 ODBC connections to 2 different *MySQL* servers you can migrate data back and forth willy nilly."

And thanks as always for your great and useful posts :)
# Posted By Fernando da Silva Trevisan | 8/1/06 11:59 PM
mkruger's Gravatar Fernando,

You read correctly. DTS can be used to move data between 2 databases or file formats that have nothing to do with MS SQL. It's not "just" an import export service.

-Mark
# Posted By mkruger | 8/2/06 7:19 AM
Ben Davies's Gravatar This is very interesting. I guess one of the challenges I face at work is that it is relatively easy to do a lot of point-to-point data connections between databases and/or files. But it gets *extremely* hard to manage, especially from a schedule and configuration management point of view, when you have about 16 Oracle and MSSQL application databases, Access Databases, spreadsheets, remote sites and server maintainence windows.

I generally get a dedicated CF server to manage as much as it can, to centralise the command and control. There are often performance trade-offs. But sometimes it is clock-cycles you can spare, and stuff-ups you can't.

Anyway, to bring this back on topic; using DTS from CF is another tool in the arsenal. Thanks for the tip.

Cheers, Ben
# Posted By Ben Davies | 8/2/06 8:47 AM
Brad's Gravatar I got this error when trying to run this script, any advise would be greatly appreciated!

------------------------------------------------------------------------
An exception occurred when instantiating a Com object.
The cause of this exception was that: coldfusion.runtime.com.ComObjectInstantiationException: An exception occurred when instantiating a Com object..
------------------------------------------------------------------------
thanks...
# Posted By Brad | 1/29/07 4:28 PM
Brad's Gravatar Your suggestion as resolved the error, thanks. However even though the script appears to run no data was transfered to the database. Thanks again for your help!
# Posted By Brad | 2/1/07 12:29 PM
Ajas Mohammed's Gravatar Hi,
I used the script and it works great but it didnt perform the action what my DTS does. Quick overview :
DTS process will import data present in a holding table into main table.

I had one row sitting in holding table and ran your script. I didnt get any errors but when I queried holding table, the 1 row was still there. The dts deletes rows at end of process and this didnt happen which brings to another question.

1) How do you track success/failure messages from DTS using this script? This is very important because I want to let you user know what happened after the dts was run.

2) The process runs but then nothing is happening? why?

Please help.
# Posted By Ajas Mohammed | 2/13/07 11:09 AM
mark kruger's Gravatar The most successful way to troubleshoot this process is by turning on tracing and logging to a database table. I'll see if I can scare up instructions for you from Mike.
# Posted By mark kruger | 2/13/07 11:15 AM
Ajas Mohammed's Gravatar I am able to crawl with this code below. I get the step names and there result. Now I need to see the Final DTS status i.e. how u get stored proc status i.e 0 for success and 1 for error.

//your cfscript code at top

<cfset sErr = "">
<cfset bSuccess = "True">
<cfset test = StructNew()>
<cfset DTSStepExecResult_Failure = 1>

<cfloop item="oStep" collection="#pkg.Steps#" >
   <cfset sErr = sErr & "<p> Step [" & oStep.Name & "] ">

   <cfif oStep.ExecutionResult eq DTSStepExecResult_Failure>
      <cfset sErr = sErr & " failed<br>" >
      <cfset   bSuccess = "False">
   <cfelse>
    <cfset sErr = sErr & " Succeeded <br>" >
   </cfif>
      
   <cfset sErr = sErr & "Task """ & (oStep.TaskName) & """</p>">
            
   <cfoutput>#sErr#</cfoutput>
      
</cfloop>

<cfif bSuccess >
   <cfset sErr = sErr & "<p>Package [" & Pkg.Name & "] succeeded</p>">
<cfelse>
   <cfset sErr = sErr & "<p>Package [" & Pkg.Name & "] failed</p>" >
</cfif>
   
<cfoutput>#sErr#</cfoutput>
# Posted By Ajas Mohammed | 2/13/07 12:24 PM
Michael Beveridge's Gravatar Hey Brad!

In an unrelated situation, I also got this error with a COM object:
------------------------------------------------------------------------
An exception occurred when instantiating a Com object.
The cause of this exception was that: coldfusion.runtime.com.ComObjectInstantiationException: An exception occurred when instantiating a Com object..
------------------------------------------------------------------------

What was the cause and solution for your error?

Michael
# Posted By Michael Beveridge | 8/12/08 2:05 PM
Brad's Gravatar Make sure you have proper permissions, this resolved my problem.
# Posted By Brad | 8/12/08 6:28 PM



Blog provided and hosted by CF Webtools. Blog Sofware by Ray Camden.