Stored Procedure in DTS

[clb99]

I need some suggestions. I have a DTS that runs every night that basically contains the importing of 2 text files into tables. Then the execution of a stored procedure. The problem is when I run the stored procedure in the query ananlyzer it takes @ 20 mins. The whole DTS runs about 5 mins at night. There are no errors. It looks like the files are being imported but the stored procedure doesn't finish. I'm not sure why.

Anyone else experience anything like this?

Thanks.
CB

[navneet]

Have you scheduled this package to run as a job. If so, then look at the history for this step to know how long does it run.

[clb99]

I checked the history. It runs around 5 mins. Not enough time. And the data that's updated in the tables hasn't all be updated either. It updates properly when the stored procedure is run from the query analyzer.

Thanks. CB

[clb99]

Is there any kind of timeout parameter on the sql function in a dts? I can't find one but I thought maybe that's what's killing it.

CB

[pan]

Suggestion:

Why not run the stored procedure as a different step of the DTS job.

[clb99]

I tried deleting that last step, where I execute the Stored Procedure, and placing in a separate SQL step in the job. Same thing. No error. It just doesn't complete. If I run it in the query analyzer it does.

[rmiao]

Did you check job step details? Any message for that step? If the step didn't complete, the job will not complete either.

[clb99]

I checked the job history. I looked at the details for that step and there's nothing to indicate a failure.

[rmiao]

Then you should find out the way to verify that sp is completed or not.