May 7, 2004 at 1:03 pm
I got a dts package that check a table. If there're records then other steps get fire. However, if there's no record then I don't want to DTS to fire the other steps. How do I terminate the DTS package w/o FAILURE and the other steps from running? thanks in advance for your help..
May 7, 2004 at 1:16 pm
Take a look at this thread. http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=106984#bm110538
May 10, 2004 at 7:21 am
I have sometimes found the don't execute task method to not be completely reliable. You could also try a variation of this. I use this to disable the subsequent step. You would make it contingent on the 0 or even the affected records:
Set pkg=DTSGlobalVariables.Parent
Set stpDataPump=pkg.Steps("DTSStep_DTSActiveScriptTask_2")
Set fso=CreateObject("Scripting.FileSystemObject")
Set folder=fso.GetFolder("Folder Path")
Set filecollection=folder.Files
If filecollection.count=0 Then
stpDataPump.DisableStep=True
Else
stpDataPump.DisableStep=False
End If
Main = DTSTaskExecResult_Success
May 11, 2004 at 1:03 pm
I'm somewhat confused. I tried several way of approaching what is described here but I can't get to do what I wanted. I created an ActiveX to count the records in a table. If there're records then Main = DTSStepScriptResult_ExecuteTask. But if there's no record then Main = DTSStepScriptResult_DontExecuteTask. However, the subsequent steps keep firing. Can you give me a more detail approach how to do this? thanks in advance..
May 11, 2004 at 1:07 pm
I too have found the Do not execute to not be the best way to do this, so why not try my suggestion and disable the subsequent step if there are no records?
May 11, 2004 at 1:28 pm
I created what you described also. But when I executed the ActiveX Script, I get either a
Object required: 'stpDataPump'
or the Step 'Next' is not found.
Here's the code:
Set pkg=DTSGlobalVariables.Parent
Set stpDataPump=pkg.Steps("NextStep")
Set fso=CreateObject("Scripting.FileSystemObject")
Set folder=fso.GetFolder("C:\Folder")
Set filecollection=folder.Files
If filecollection.count=0 Then
stpDataPump.DisableStep=True
Else
stpDataPump.DisableStep=False
End If
Main = DTSTaskExecResult_Success
End Function
Thanks for your help..
May 11, 2004 at 2:34 pm
While there is a way to use your own step names using Disconnected Edit, I have not used that, and so have always had to use the name given to the step by the package. You can use the dynamic properties (I hope you're using SQL2K) to determine the step. The name will appear similar to the one I'm using above.
You also have to substitute my file counting code for your record counting code.
Let me know if that helps, or what else you need.
May 11, 2004 at 3:08 pm
Got it to work. Just got confused w/ the DTS properties. Your was a 'DTSStep_DTSActiveScriptTask_2' and my next step was a 'DTSStep_DTSExecuteSQLTask_2.' thanks for the help..
May 11, 2004 at 3:14 pm
You're welcome.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply