March 1, 2006 at 8:12 am
I have a developer that wants to pass a condition code in a DTS that needs to be check in DOS for further processing. Any ideas?
DTS contains...
other coding followed by
RAISERROR ('NO DENIM RECORDS TO EXPORT', 16, 1)
Bat file runs above DTS and needs to check for above condition to determine further processing is necessary or exit.
March 1, 2006 at 11:19 am
a few different ways to do this...
first would be creating an sp to execute the DTS. IF COUNT(*) > 0 DTS ELSE RAISEERROR
another way (inside the dts) is add an Execute SQL Task to the package. IF ISNULL(COUNT(*),0) = 0 RAISERROR. Use the "on success" work-flow to continue to the datapump.
March 1, 2006 at 11:53 am
We don't have an issue in determining when to issue the raiseerror condition, but being able from DOS being able to capture the error to determine to continue process other processes or not.
The DTS reads SQL tables for certain records since the last update. For each record it finds, it creates a text record. If there are no records to process the whole process ends. If it finds and creates text records, the process continues to execute a program on one of our citrix servers. So the batch
file would contain:
1. dtsrun /s servername /e /n nameofdts
2. check of raiseerror...goto endprocess
3. run program
4. dtsrun /s servername /e /n nameofseconddts
5. endprocess
March 1, 2006 at 12:48 pm
just call the second dts from the first if that condition occurs.
IF (SELECT ISNULL(COUNT(*),0) FROM Table [WHERE ...]) = 0
BEGIN
RAISERROR()
END
Use the "on failure" work-flow to call the DTS
March 1, 2006 at 12:58 pm
the second dts does not unless there are records to be processed by the program that MUST run from a DOS prompt on a different server. How do you propose to kick off the other program? XP_CMD can not be used because the other program runs on another server.
March 1, 2006 at 1:12 pm
I am unsure what DOS prompt refers to...
There are connection properties on the Execute DTS Package Task. If you must run via a sp, then add another connection object.
March 1, 2006 at 1:19 pm
MS DOS... or command prompt...
Disk Operating System (DOS)...
Operating System before Windows came along...
The batch file would be scheduled to run from task scheduler.
A batch file would contain DOS commands...
To get to your DOS prompt:
click your Start button like when you need to reboot your pc
choose run
key in cmd and press enter...DOS prompt...
March 1, 2006 at 1:40 pm
it's been a long day.. I was Reading it as D.O.S. Though that should have clued me in as well. I am assuming you are using DtsRun.exe. In any case, you should not have a problem executing the second DTS from the first, despite having different servers.
March 2, 2006 at 11:32 am
the issue is not executing the second dts from the first dts. I know I can use work flow to determine to execute the second dts or not. The issue is after the first dts runs successfully I need to run a second PROGRAM (not a DTS) on another server. I think the second program is a VB program.
March 2, 2006 at 11:39 am
create a connection to computer #2. Create an execute sql task object referencing the connection. EXEC xp_cmdshell '...\program.exe'
March 2, 2006 at 11:42 am
if you are adamant about sticking to the DOS prompt, you can pipe the results of an osql.exe query to a DOS script after running DTS #1.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply