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.
 |
|
|
|
|
|
|
But double check your title. Says "Colfusion".
Sami
This is an incredibly useful idea.
Mike.
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)
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
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.
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)
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
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?
I could be wrong - perhaps a reader will correct me.
-mark
shows you exactly how to do this.
<cfquery...>
exec sp_startJob @jobname='job'
</cfquery>
Haven't tested this, just wondering if anyone knows pros/cons...
Interesting discussion here.
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
"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 :)
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
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
------------------------------------------------------------------------
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...
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.
//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>
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