ColdFusion Muse

Coldfusion and DTS - Easy Exporting

Mark Kruger July 26, 2006 4:43 PM MS SQL Server, Coldfusion Tips and Techniques Comments (26)

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.

  • Share:

26 Comments

  • Sami Hoda's Gravatar
    Posted By
    Sami Hoda | 7/26/06 8:14 PM
    Nice!

    But double check your title. Says "Colfusion".

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

    This is an incredibly useful idea.

    Mike.
  • Falcon79's Gravatar
    Posted By
    Falcon79 | 7/27/06 5:03 AM
    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)
  • mkruger's Gravatar
    Posted By
    mkruger | 7/27/06 8:28 AM
    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
  • Tony Petruzzi's Gravatar
    Posted By
    Tony Petruzzi | 7/27/06 9:00 AM
    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.
  • mkruger's Gravatar
    Posted By
    mkruger | 7/27/06 9:09 AM
    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)
  • Mkruger's Gravatar
    Posted By
    Mkruger | 7/27/06 9:10 AM
    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
  • Scot's Gravatar
    Posted By
    Scot | 7/27/06 9:20 AM
    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?
  • mkruger's Gravatar
    Posted By
    mkruger | 7/27/06 9:52 AM
    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
  • Tony Petruzzi's Gravatar
    Posted By
    Tony Petruzzi | 7/27/06 10:00 AM
    http://www.sqlteam.com/item.asp?ItemID=12408

    shows you exactly how to do this.
  • Scot's Gravatar
    Posted By
    Scot | 7/27/06 10:34 AM
    I have no tag limitiations, all wide open (intranet). I would love to execute a DTS package from a CFM page.
  • todd's Gravatar
    Posted By
    todd | 7/27/06 11:08 AM
    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.
  • todd's Gravatar
    Posted By
    todd | 7/27/06 11:08 AM
    or <cfstoredproc....>
  • Falcon79's Gravatar
    Posted By
    Falcon79 | 7/27/06 12:40 PM
    ok todd, but export file csv will be created on db-server! or not?
  • Gus's Gravatar
    Posted By
    Gus | 7/27/06 2:14 PM
    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
  • Fernando da Silva Trevisan's Gravatar
    Posted By
    Fernando da Silva Trevisan | 8/1/06 11:59 PM
    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 :)
  • mkruger's Gravatar
    Posted By
    mkruger | 8/2/06 7:19 AM
    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
  • Ben Davies's Gravatar
    Posted By
    Ben Davies | 8/2/06 8:47 AM
    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
  • Brad's Gravatar
    Posted By
    Brad | 1/29/07 4:28 PM
    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...
  • Brad's Gravatar
    Posted By
    Brad | 2/1/07 12:29 PM
    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!
  • Ajas Mohammed's Gravatar
    Posted By
    Ajas Mohammed | 2/13/07 11:09 AM
    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.
  • mark kruger's Gravatar
    Posted By
    mark kruger | 2/13/07 11:15 AM
    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.
  • Ajas Mohammed's Gravatar
    Posted By
    Ajas Mohammed | 2/13/07 12:24 PM
    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>
  • Michael Beveridge's Gravatar
    Posted By
    Michael Beveridge | 8/12/08 2:05 PM
    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
  • Brad's Gravatar
    Posted By
    Brad | 8/12/08 6:28 PM
    Make sure you have proper permissions, this resolved my problem.