More CF and DTS Troubleshooting
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.
DTS and Logging locally
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.
Where do I create the package
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.
2 Comments
-
One thing you need to watch out for is that (in 2000 at least), the log file is not written-to until the task is complete or bombs out. That is, the entire thing is logged out all at once. If you are hoping to gather real-time info, you should look into querying the msdb.dbo.sysdts* tables directly.
I just happen to have a blog entry about it here:
http://rickosborne.org/blog/?p=51
Querying the tables directly has the downside that your user account must be able to query those tables, but the upside that you don't have to go mucking about in the file system or parsing the text with CF. However, if anyone actually wants to go that route, I've written a fairly comprehensive DTS log parser in CF and would be willing to share with anyone that wants it. -
The problem with the log tables is that they occur on the SQL server side of the equation. If the problem is connection related on the client then the error is not "seen" by the server - it has to be logged by the client. That's where the log file comes in handy.
Sites to Watch
Aggregators
RSS
