SQL Server Performance Forum – Threads Archive

DTS Steps Not Run

Created a DTS package consisting of a number of steps. Each step runs successfully individually. Set up workflow between each step with On Success specified. Run package, everything runs fine. All steps run. Looked at the package log and noticed the step names were obscure so changed the step names using Disconnected Edit. From that point on it appears only the first step will run. It says success and the rest of the steps say not run. I did lose the workflow arrows at that point as well and did re-create them. Tried changing the step names back to original ones, didn’t work either. Any suggestions would appreciate. Feel free to email at [email protected] Thank you!

Can you submit: OS, SQL Server and SP version?
Also, kind of job (maintenance?)
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

OS – Windows Server 2003, SQL Server 8.000.760, The job is to load data from an Oracle database. It’s a mix of SQL steps and data transformation steps.

Enable DTS package logging and check the steps of workflow.
http://www.microsoft.com/technet/community/chats/trans/sql/sql0122.mspx technet chat about DTS inside & out. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I did enable package logging and it says the first step ran successfully, second step not run. Is there something else I should do? The error handling log just shows that the second step and the rest were not executed. There wasn’t any indication as to why they weren’t executed.

Then the last resort would be to run PROFILER during this DTS package execution. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Try the following: 1. remove any precedence setting, i.e., remove any workflow arrows.
2. In Disconnected Edit, edit the Name variable of every task in the "Tasks" tree as you want.
3. The most important step you have missed: In Disconnected Edit, edit the "Name" variable of every step in the "Steps" tree as you want AND the "TaskName" variable of every step to the name you specified for the corresopnding task in the previous step.
4. set again your workflow arrows. the DTS pakage interpretates the precedence setting by the STEP name not by the TAKS. You can see this name if you right-click on a task(in data pump task – the gray arrow)–> Workflow properties -> Options. look for the "Name" which is the step name. This should be working. I generaly use the follwing naming conventions in Disconnected Edit: TASK_taskname for "Name" in Tasks and "TaskName" in Steps, STEP_stepname for "Name" in Steps. I hope it make any help. Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

Aviel – Thanks. I had specified the task name in the step. I thought you might be on to something though with the order I did things. So, I followed your steps, but still have the same issue. I did check the step name as you suggested and it looks correct. It does seem to show the description as opposed to the actual step name. Any other suggestions? Sayta – Thanks for the suggestion, I’ll try it.

Cindy,
If you see the description in the Name field of the step and not the actual step name it means that something is probably wrong, or you specified the name to be exactly as the decription. Try to choose differnet name conventions for Descriptions, step names and task names. To my experience, the only variables the affect this issue are Name of tasks and steps, and TaskName of step -nothing more. Also, after you removed your precedence arrows (step 1), save the dts and close it. Then open it again, and follow step 2 and on.
Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

Aviel, Thanks for all your suggestions. I ended up re-creating it and fixing the names as I went along before I set up the workflow. That seemed to work OK. No matter what I did couldn’t seem to get the first one I created to work. Cindy

]]>