SQLServerCentral / Discuss Content Posted by Wayne Fillis / Article Discussions / Article Discussions by Author / Conditional Flow in DTS / Latest Posts

RE: Conditional Flow in DTS

Esalter — Tue, 03 Mar 2009 04:17:58 GMT

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:

RE: Conditional Flow in DTS

John Dempsey — Thu, 21 Jun 2007 08:38:00 GMT

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.

RE: Conditional Flow in DTS

Francisco Rodriguez-252375 — Fri, 08 Jun 2007 09:34:00 GMT

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,

RE: Conditional Flow in DTS

alexx — Fri, 08 Jun 2007 08:05:00 GMT

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

RE: Conditional Flow in DTS

Ralph D. Wilson II — Fri, 08 Jun 2007 08:02:00 GMT

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."

RE: Conditional Flow in DTS

shailendra-251422 — Fri, 08 Jun 2007 07:51:00 GMT

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!

RE: Conditional Flow in DTS

Chada — Fri, 08 Jun 2007 06:43:00 GMT

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.

RE: Conditional Flow in DTS

Carlos Urbina — Thu, 07 Jun 2007 16:58:00 GMT

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.

RE: Conditional Flow in DTS

Lawrence Rajan — Thu, 07 Jun 2007 12:30:00 GMT

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.

RE: Conditional Flow in DTS

Francisco Rodriguez-252375 — Thu, 07 Jun 2007 08:29:00 GMT

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

RE: Conditional Flow in DTS

mrpolecat — Thu, 07 Jun 2007 07:23:00 GMT

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_"&tskelse DTSGlobalVariables.Parent.Steps("DTSStep_DTSDataPumpTask_"&tsk).DisableStep = Trueend ifend sub

RE: Conditional Flow in DTS

Michael Lysons — Thu, 07 Jun 2007 02:01:00 GMT

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

Conditional Flow in DTS

Wayne-153714 — Wed, 14 Mar 2007 13:51:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/WFillis/2929.asp