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.
create dts package object
pkg = createObject("COM","DTS.Package");
// load package
pkg.LoadfromSQLServer
("NameOfSQLServer",
"SQL_Username",
"sql_password",
0,
"",
"",
"",
"nameOfTheDtsPackage",
"");
// execute
pkg.Execute();
</cfscript>
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.
26 Comments
-
Doh'! ... that will teach me to drink and blog - thanks for the heads up :) -
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) -
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 -
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. -
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) -
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 -
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? -
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 -
-
I have no tag limitiations, all wide open (intranet). I would love to execute a DTS package from a CFM page.
-
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. -
ok todd, but export file csv will be created on db-server! or not?
-
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 -
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 :) -
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 -
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 -
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... -
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. -
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.
-
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> -
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
Sites to Watch
Aggregators
RSS
