DTS problem .. One more question :o)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS problem .. One more question :o)

Author

Topic

TJ
Posting Yak Master

201 Posts

Posted - 2002-05-01 : 12:29:54

I’m looking for a second opinion. I have a DTS that consists of the following processes:


Begin
        Run a batch file that creates the dbaseIII files 
On Success
        Connect to a dbaseIII file
Transform
        Export the data from dbIII to an SQL table
On Success
        Connect to a dbaseIII file
Transform 
        Export the data from dbIII to an SQL table
On Success
        Run an sproc
On Success
        Run an sproc
On Success
        Delete a dbIII file
On Success
        Delete a dbIII file

This job runs without errors. However, it’s not running the sprocs. I think the connection should be 'On Completion' instead of 'On Success'.

There is no way to test this. The only time I know it fails is when it’s run in production and the reports are incorrect or the DTS itself fails.

Any ideas?

Thanks,
Teresa

Edited by - tj on 05/02/2002 17:52:49

izaltsman
A custom title

1139 Posts

Posted - 2002-05-01 : 16:20:42

Are you saying that it doesn't run the procs, but does delete the dbaseIII files? That doesn't sound right...
I think the procs do run, but just don't produce the result you expect. Can you temporarily alter the proc so that it raises an error as soon as it is launched? Try that, and if your DTS package fails on this step, you'll know that at least it was trying to run the proc.

---------------
Strong SQL Developer wanted in the Boston area. Please e-mail if interested.

Edited by - izaltsman on 05/01/2002 16:23:21

TJ
Posting Yak Master

201 Posts

Posted - 2002-05-01 : 16:24:57

quote:

Are you saying that it doesn't run the procs, but does delete the dbaseIII files?

Yes, that's what it appears to be doing.

quote:

Can you alter the proc so that it raises an error, to see if your DTS package will fail?

I have done that. It does fail and it does not delete the dbase files.

I wondered if the the two sprocs were running simultaneously. The second does depend on the 1st sprocs completion.

I can tell you that the the DTS does not fail and does not raise any errors. However, the data that is returned from the second sproc is incorrect. If I run that sproc as a job after the DTS is complete, I get correct data.

Thanks again for your help.

Teresa

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-01 : 16:34:03

How about combining the 2 SP's into one DTS task step? I assume that they are each called as an Execute SQL Task, just modify the first task to include calls to both SP's. If either one of them fail, then the step will fail, and it won't change the rest of the workflow.

TJ
Posting Yak Master

201 Posts

Posted - 2002-05-01 : 16:37:45

Rob,

If I do that, will the second be constrained to run after the first one completes without errors?

Thanks!
Teresa

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-01 : 16:41:40

It should, I've done it that way many times and they run in a serial manner (one AFTER the other, not concurrently)

TJ
Posting Yak Master

201 Posts

Posted - 2002-05-01 : 16:43:31

quote:

It should, I've done it that way many times and they run in a serial manner (one AFTER the other, not concurrently)

Thanks! I'll change it and we'll find out in the am if it works as expected.

Have a great day! :o)

Teresa

TJ
Posting Yak Master

201 Posts

Posted - 2002-05-02 : 10:06:44

This DTS ran again this morning. The 2 sprocs ran (one after the other). However....

The 2nd sproc (Update Preparer Counts) ran, but it did not update the table with the new counts. I ran the JOB called Update Preparer Counts and it updated the table. With this in mind, I started searching for a difference with the two of them. They both call the same code. The only difference is that one is in the DTS and one isn't. I'm at a complete loss.

The sproc Update Preparer Counts consists of 6 sprocs. The 7th sproc is to 'Run' the the previous 6. It says to execute each sproc and in which order. Should I call each sproc in the DTS instead of the 7th one used to run the others?

I know I'm missing something, but I can't figure out what it is.

Does anyone have any idea what I need to look for?

Thanks,
Teresa

Edited by - tj on 05/02/2002 10:40:38

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources