Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Conditional Flow in DTS Expand / Collapse
Author
Message
Posted Wednesday, March 14, 2007 1:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 5:38 AM
Points: 143, Visits: 55
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/WFillis/2929.asp


When in doubt - test, test, test!

Wayne

Post #351598
Posted Thursday, June 07, 2007 2:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:38 AM
Points: 1,144, Visits: 1,050

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

Post #371936
Posted Thursday, June 07, 2007 7:23 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 8:10 AM
Points: 445, Visits: 840

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








Post #372020
Posted Thursday, June 07, 2007 8:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 20, 2010 6:03 PM
Points: 10, Visits: 33

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

Post #372047
Posted Thursday, June 07, 2007 12:30 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 03, 2013 12:43 PM
Points: 54, Visits: 9

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.

 

Post #372125
Posted Thursday, June 07, 2007 4:58 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 31, 2007 8:22 AM
Points: 92, Visits: 1

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.

Post #372194
Posted Friday, June 08, 2007 6:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 06, 2009 11:10 PM
Points: 59, Visits: 72
 

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.

Post #372307
Posted Friday, June 08, 2007 7:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 27, 2009 11:02 AM
Points: 51, Visits: 49

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!

Post #372354
Posted Friday, June 08, 2007 8:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 05, 2009 10:58 AM
Points: 62, Visits: 38

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

 

Post #372362
Posted Friday, June 08, 2007 8:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 07, 2014 7:42 AM
Points: 26, Visits: 133
great work man...simply with great potential ..sorry my english...


Post #372364
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse