title:
calling a dts package with cold fusion
text:
OK, first off if you are a cold fusion guy, you may not know what 'a dts package' is.
Data Transformation Services (DTS) is a feature of Microsoft SQL Server (versions 7.0 and above)
that allows an individual to combine several data-related tasks into one common object.
That object is called a DTS 'package' and can do just about anything
you want on a sql server. As an example, one could write a DTS package that downloads
several files from multiple ftp servers, then uses activex script to pull data via an xml feed off
of the web. Once the data was retrieved you could combine it, update tables, refresh lookup tables, or
perhaps repackage the data as an RSS formated XML document and place it an a location that was
accessible to the DTS job such as a repository for RSS . A DTS package could also perform a more mundane task.
Backups of databases that you would like to schedule with cold fusion scheduler, for instance.
If you would like to know more about DTS there is a wealth of knowledge available on the internet.

There is at least one other way I know of to execute DTS packages remotely via cold fusion.
It involves calling stored procedures and I have seen it published a couple of other places
already. I didn't really like that method because it used a *lot* more code than this. I had
executed DTS packages remotely using vbscript when I was doing DBA work so I knew there was a
way to do this. You could actually do a lot more than this with the DTS.Package2 object, but
my need at the time, and the purpose of this article, concerned only package execution.

Here are some links to the other method:
http://www.findarticles.com/p/articles/mi_m0MLU/is_9_5/ai_108331156
http://www.findarticles.com/p/articles/mi_m0MLU/is_10_5/ai_109039752

Requirements:
You will have to have the Microsoft DTS Custom Tasks Object Library available as a COM
object on your machine. All the machines I work on generally at the least have the MS-SQL
Tools installed which appears to contain this. The DLL that contains this is typically
located in %SQL Server Directory%\80\Tools\Binn\custtask.dll as far as I know.

If you are on a windows box and want to see if you can create the object, the way I normally
test is to create a test vbs file and run it that contains only the following line:

set obj = createobject("DTS.Package2")

if that line fails, you don't have it installed.
-->

<!--
name: dts.cfm
author: roy ashbrook
email: royashbrook@yahoo.com
description:
this script demonstrates how to call a dts package via a cfm script.
-->

<cfset s = "servername" >
<cfset u = "username" >
<cfset p = "password" >
<cfset n = "dtsname" >
<cfset c = "0" >
<cfset null = "" >

<cfobject action="Create" type="COM" class="DTS.Package2" name="obj">
<cfset obj.LoadFromSQLServer(s,u,p,c,null,null,null,n,null) >
<cfset obj.FailOnError = "True" >
<cfset obj.Execute() >