Here is a task that can be called from another task to execute a MS SQL Server DTS package. It takes 5 parameters...

1. BaseDirectory - a base path where the DTS log file will be written and to which the SQL Server specified in the "DBServer" parameter has access
2. DBServer - the database server where the DTS package is located
3. Username - a username that has permission to execute the DTS package
4. Password - the password of the aforementioned username
5. PackageName - the DTS package you would like to execute

If a value is not supplied for any of these 5 parameters you will prompted to enter one. This feature also allows the task to be run on its own and prompt for values for all 5 variables.

Now certainly, you could get a lot fancier with this by adding support for all the dtsrun command-line switch, but I just wanted to show the basics and you can add to it to meet your specific needs.

Disclaimer: This will only work in SQL Server 2000 and earlier. I guess the dtsrun utility has been replaced by the dtexec utility. This link explains the changes.




Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
<AMVARIABLE NAME="BaseDirectory" ISPARAMETER="YES"></AMVARIABLE> <AMVARIABLE NAME="DBServer" ISPARAMETER="YES"></AMVARIABLE> <AMVARIABLE NAME="Username" ISPARAMETER="YES"></AMVARIABLE> <AMVARIABLE NAME="Password" ISPARAMETER="YES"></AMVARIABLE> <AMVARIABLE NAME="PackageName" ISPARAMETER="YES"></AMVARIABLE> <AMIF EXPRESSION="%BaseDirectory% = &quot;&quot;"> <AMCOMMONDIALOG ACTION="folder" WINDOWTITLE="Choose a base directory for the log file..." RESULTVARIABLE="BaseDirectory" DEFAULTFOLDER=""> </AMIF> <AMSET VARIABLENAME="BaseDirectory">%BaseDirectory%\DTSRunner</AMSET> <AMIF EXPRESSION="%DBServer% = &quot;&quot;"> <AMINPUTBOX RESULTVARIABLE="DBServer">Enter the database server where the DTS package is located...</AMINPUTBOX> </AMIF> <AMIF EXPRESSION="%Username% = &quot;&quot;"> <AMINPUTBOX RESULTVARIABLE="Username">Enter a username that has permission to execute the DTS package...</AMINPUTBOX> </AMIF> <AMIF EXPRESSION="%Password% = &quot;&quot;"> <AMINPUTBOX RESULTVARIABLE="Password">Enter the password...</AMINPUTBOX> </AMIF> <AMIF EXPRESSION="%PackageName% = &quot;&quot;"> <AMINPUTBOX RESULTVARIABLE="PackageName">Enter the DTS package name...</AMINPUTBOX> </AMIF> <AMVARIABLE NAME="LogFile">%BaseDirectory%\%PackageName%_%Format(Now(), &quot;yyyymmdd_hhnnss&quot;)%.log</AMVARIABLE> <AMVARIABLE NAME="LogFileContents"></AMVARIABLE> <AMIFFOLDEREXISTS ACTION="not_exist" FOLDER="%BaseDirectory%"> <AMCREATEFOLDER FOLDER="%BaseDirectory%"> </AMIF> <AMRUN FILE="dtsrun /S%DBServer% /U%Username% /P%Password% /N&quot;%PackageName%&quot; /L&quot;%LogFile%&quot;" WAITFORINPUT="NO" WAITFOREND="YES"> <AMFILEREAD FILE="%LogFile%" RESULTVARIABLE="LogFileContents" MAXBYTES="250"> <AMIF EXPRESSION="%InStr(LogFileContents, &quot;succeeded&quot;) = 0%"> <!--- if the word "succeeded" is not found, then the package did not run successfully ---> <AMSTOPTASK STATUSTYPE="ERROR" ERRORNUMBER="-1" ERRORTEXT="The exection of %PackageName% resulted in a DTS failure. To investigate further, check the log file, %LogFile%."> </AMIF>

-------------------------
Thanks,

Noel Scheaffer