ColdFusion Muse

DTS Over Coldfusion - Revisited

Mark Kruger August 17, 2006 1:29 PM MS SQL Server Comments (3)

If you stumbled onto my Coldfusion and DTS and tried to make use of it - you may have stumbled onto a particularly ticklish error that's difficult to troubleshoot. First however, I want to rectify an omission that may have caused you trouble from the outset. This DTS package execution does not automatically throw an error if it fails. It requires that you set a property - "FailOnError" - true. Here's the revised code:

// create dts package object
pkg = createObject("COM","DTS.Package");
// load package
pkg.FailOnError = "true";
// execute
Setting it to true will cause a failure to throw an error to the calling page. In other words, without it, you get nothing and you think it has succeeded. Now for that tricky bug....

Server Name Resolution

You may or may not know that MS SQL server is designed to use multiple network transports to communicate. By default it enables TCP/IP and "Named Pipes". When the web server tries to make a connection through the COM to the server name it will attempt to use named pipes to establish credentials. Here's the rub. If the server is multi-homed then the package must be able to connect to the same IP address as the bound NetBIOS name of the server.

In other words, if, during installation, you named the server BOB and attached IP address to NIC card A, and the later on added nic card B with IP address, your package must be able to resolve the "BOB" to In fact, putting in the correct IP address will work as well. If you try to alias "BOB" to Why? I don't know - but I suspect it has to do with NBT and named pipes being bound to a specific IP and NIC card. There is probably even a way to change it. Someone out there can fill me in.

  • Share:


  • Phillip Senn's Gravatar
    Posted By
    Phillip Senn | 8/22/07 10:17 AM
    This worked!
  • Phillip Senn's Gravatar
    Posted By
    Phillip Senn | 8/22/07 2:11 PM
    What if I want to use a parameter?
    I'm using
    <cfset pkg.LoadfromSQLServer("myconnection","myusername","mypassword",0,"","","",'DTSPackageName /A"Filename:8=myFilename.csv"',"")>

    and it doesn't like the /A switch specified in the name of the DTS Package.
    What are the other parameters (after name and trusted connection)?
  • Phillip Senn's Gravatar
    Posted By
    Phillip Senn | 8/23/07 2:11 PM
    pkg.GlobalVariables.Item("myParameter").Value = "myValue";

    From "Running a DTS package from ColdFusion"