For those of you that have made use of my blog post on DTS and Coldfusion and the subsequent blog on Troubleshooting DTS and Coldfusion, I have a 2 more useful tips. One has to do with a way to trap errors on the server and get information about what's going on on the client - the second tip has to do with protocol selection and conflict.
One of the things that's frustrating about using DTS packages within your CF code is the lack of error information. Some things happen on the server, and some things happen on the client (in this case the web server). Good logging would include information from both - but that is not possible if the server is unaware of an error thrown on the client. It's doable - and it's not that hard. Using Enterprise Manager go into the package designer. From the menu choose "package-properties". Select the logging tab. In the section titled "Error Handling" select "Fail package on first error." Put the full path to the file you want to contain the logging information in the "error file" area. The secret is that this path should be a path on the system destined to run the package - in other words, a path on your web server. When you run the package you will get client side information about why it errored out.
Keep in mind that this DTS logging is pretty verbose, and it always appends to the file. So unless you want a large log file over time you will want to enable it while you are troubleshooting then disable it.
You create your package from Enterprise Manager. When you do it make sure that it is using the same network libraries that you will use on the web server. In other words, if you create an OLE connection on your workstation, change paths and then try to run it on the web server (where ole and named pipes may or may not be available) it could error out with a connection failure - very hard to troubleshoot. My advice is to create it using the "Microsoft ODBC driver for SQL server". It is likely to be the most ubiquitous choice.