dts running from batchfile help!
-
hi guys, i have a a simple dts in my sql server , i got the command line syntax from start, run, dtsrunui. and then i created a batchfile for the user to run , in the command line i also specifiy the sql server login and password. It runs perfectly. However when it errors out I need for the user who is running the batchfile to get a msgbox or an error message window saying it failed, I know i can do it to send an email throught the dts but i don't have exchange server so my email take a few minutes to get to the person. I need this msgbox or message window to come out as soon as this dts failed. can this be done?
-
I don't know if this is of any help (I'm no great expert) but we use an encrypted version of the dtsrun string within the following script:
SET ServerName=<SERVERNAME>
SET Package=<DTSPACKAGENAME>
SET logfile=DataImport.log
cls
echo Server is %ServerName%
echo About to run DTS package %Package%
REM --------------------------------------------------------
REM Run the DTS package
REM --------------------------------------------------------
echo Starting DTS package...
echo.
if exist %logfile% del %logfile%
dtsrun /!Y /!C /S <SERVERNAME> /U <USERNAME> /P <PASSWORD> /N <DTSPACKAGENAME> /E /L DataImport.log
if ERRORLEVEL 1 GOTO DTSERR
GOTO DTSOK
TSERRecho.
echo.
echo An error in %Package% has occurred. Import terminated
GOTO END
TSOKecho.
echo %Package% run has succeeded.
:END
echo.
echo Batch file ended
pause
This results in the normal dts messages being displayed to the user - I also edit the Task and Step names via 'Disconnected Edit' (in the dts package) so that the output is a bit more meaningful than the system generated names.
It also captures errors via the ErrorLevel (0 being no error).
It also creates an Import log for the user to check - just in case they accidentally close the window.
Hope that's relevant & sorry if not!
Helen
-
Thank you!!! exactly what i needed, i tried it but for some reason it does not work, can you tell me what i did wrong please, i tried this.....
SET ServerName=<MySQLServerName>
SET Package=<MyDTSName>
SET logfile=NewLog.Log
cls
echo Server is %ServerName%
echo About to run DTS package %Package%
REM --------------------------------------------------------
REM Run the DTS package
REM --------------------------------------------------------
echo Starting DTS package...
echo.
if exist %logfile% del %logfile%
my previous dtsrun script /E /L NewLog.log
if ERRORLEVEL 1 GOTO DTSERR
GOTO DTSOK
DTSERR
echo.
echo.
echo An error in %Package% has occurred. Import terminated
GOTO END
DTSOK
echo.
echo %Package% run has succeeded.
:END
echo.
echo Batch file ended
pause
do i need to create the log file first?
-
The labels should be preceded by a colon [:]DTSERR without the brackets. (The board changes it to a smiley face.)
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply