Conditional Flow in DTS

Question

Post reply

Conditional Flow in DTS

Wayne-153714

Hall of Fame

Points: 3536
More actions
March 14, 2007 at 1:51 pm

#175643

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/WFillis/2929.asp
When in doubt - test, test, test!
Wayne

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Michael Lysons SSCertifiable Points: 6491 More actions · Answer 1

I'm still in shock from the fact that I've only just found out you can do this!

Nice one - you've made my day

mrpolecat SSCertifiable Points: 7014 More actions · Answer 2

I have always found that workflow scripts are hard to manage because you can't see them in the designer. When you go to edit old packages that you have forgotten or someone elses code it is hard to figure out what is going on when steps don't fire because of the workflow script. Instead I use activeX scripts to enable and disable future steps based on previous actions. These are easier to maintain because youcan see the step in the designer.

I use the following subs to set precedence and diable steps.

sub setprec(dest,src)

Set oPkg = DTSGlobalVariables.Parent

Set Destination = oPkg.Steps (cstr(dest))

Set Source = oPkg.Steps (cstr(src))

Set Prec = Destination.PrecedenceConstraints.New(Source.name)

Prec.PrecedenceBasis = DTSStepPrecedenceBasis_ExecResult

Prec.value = DTSStepExecResult_Success

Destination.PrecedenceConstraints.Add Prec

Destination.DisableStep = False

end sub

sub chkfiles(flnm,tsk)

set fso=createobject("scripting.filesystemobject")

if fso.fileexists (flnm) then

DTSGlobalVariables.Parent.Steps("DTSStep_DTSDataPumpTask_"&tsk).DisableStep = False

setprec "DTSStep_DTSExecuteSQLTask_2","DTSStep_DTSDataPumpTask_"&tsk

else

DTSGlobalVariables.Parent.Steps("DTSStep_DTSDataPumpTask_"&tsk).DisableStep = True

end if

end sub

Francisco Rodriguez-252375 SSC Enthusiast Points: 162 More actions · Answer 3

Hi, Wayne, good article.

I have one question.

I run a step that moves info from one table to another and if one or more records were copied I'd like to move to another step (just if records where copied) and if there was an error or no records were copied I'd like to move to a different step. I don't want to depend on success of the step because if there were no records to copy I would receive a success status.

How can I do this ?

Thanks in advance

Lawrence Rajan SSC Journeyman Points: 80 More actions · Answer 4

Hi Francisco,

To address your question, you can create another Execute SQL Task after the transformation task that does a COUNT(*) from the destination table and if records exist, proceed with the flow that Wayne explained in his article. Hope this helps.

Carlos Urbina SSCommitted Points: 1850 More actions · Answer 5

Superlative article! worthy of a DTS for Dummies book.

What I've seen before are MRPOLECAT type scripts which I'm too dumb to get to work.

But your scripts really helped. I have a ton of places where I can use this.

Chada Old Hand Points: 345 More actions · Answer 6

A very nice article!

Scripting is really helpful. I have a many a places to pen down where I can use this type of scripting.

shailendra-251422 Ten Centuries Points: 1111 More actions · Answer 7

This is excellent!!

I had attempted using that option but could never figure out how it should be used. This will really help me. Thanks!

Ralph D. Wilson II Old Hand Points: 394 More actions · Answer 8

At one time, I would have considered this to be a "fluff" article that was so obvious that it was a waste of time to read, much less to write. However, I have recently discovered that, apparently, an awful lot of DBAs come from a non-programming background and that makes this kind of article very valueable.

Please, don't get me wrong, I am not making a negative comment . . . I am saying that your article:

Is valuable to the community;
Has helped me recognize that I may have an advantage due to both the quantity and nature of my experience;
reminds me that the "little things" that one sometimes assumes are "common knowledge" may actually be anything but "common knowledge."

alexx Old Hand Points: 320 More actions · Answer 9

great work man...simply with great potential ..sorry my english...

Francisco Rodriguez-252375 SSC Enthusiast Points: 162 More actions · Answer 10

Hi, Lawrence.

Thanks for the advice but this is exactly what I don't want to do.

I'd like to see if in one step I can load the info and validate the number of rows transferred in order to move to a new step (depending in the number of rows).

Best regards,

John Dempsey Hall of Fame Points: 3225 More actions · Answer 11

I agree with you that it is not intuitive when you have a workflow property on a step. This happened to me on a DTS package I was given to maintain and was a real pain to find out what was going on. My solution once I found it was to just put an annotation note next to the step regarding the workflow property. After that experience, I said to myself that I would probably not use them in the future. But, after reading this article I may have to rethink that position. A simple note about a workflow property would let you know to look into the step for the code. Nice simple article.

Esalter SSC Veteran Points: 233 More actions · Answer 12

Great - just what I was looking for however I found that the If statement had to all be keyed as a single line for it to work.

Strange really as the MS SQL HELP also formatted the IF as per the example here.....

Now all I need to be find, is how to enquire on a table using ActiveX...:Whistling:

Trainee Novice:w00t: