DTS Over Coldfusion - Revisited
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.LoadfromSQLServer
("NameOfSQLServer",
"SQL_Username",
"sql_password",
0,
"",
"",
"",
"nameOfTheDtsPackage",
"");
pkg.FailOnError = "true";
// execute
pkg.Execute();
</cfscript>
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 10.1.1.1 to NIC card A, and the later on added nic card B with IP address 10.1.1.2, your package must be able to resolve the "BOB" to 10.1.1.1. In fact, putting in the correct IP address will work as well. If you try to alias "BOB" to 10.1.1.2. 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.
3 Comments
-
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)? -
pkg.GlobalVariables.Item("myParameter").Value = "myValue";
From "Running a DTS package from ColdFusion"
http://mandalapu.blog.com/1587529/
Sites to Watch
Aggregators
RSS
