Conditional execution of a DTS package

Question

Conditional execution of a DTS package

David.Poole

SSC Guru

Points: 76036
More actions
December 15, 2005 at 7:48 am

#68635

I have a DTS package that itterates through a series of XML files and does a bulk upload of data into a series of tables.
This runs every 30 seconds.
I have another process that is designed to run nightly but what I want it to do is execute conditionally. If there are any records in a table where the field ProcessedDate IS NULL then execute the DTS package, otherwise quit.
I am not sure how to do this.
LinkedIn Profile

Viewing 7 posts - 1 through 6 (of 6 total)

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

Phil Parkin SSC Guru Points: 246991 More actions · Answer 1

One way is to put a piece of ActiveX code as the first step in the DTS package. Perform the IS NULL checks in this step and then:

if (NULL condition) then

main = DTSTaskExecResult_Success

else

Main = DTSTaskExecResult_Failure

end if

Then you'll need Success and Failure workflows coming out of this task to control what happens next. Obviously, Success will point to your existing processing.

The DTSStepScriptResult_DontExecuteTask constant also looks promising, but I've never used that, so I'm not sure.

Regards.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

David.Poole SSC Guru Points: 76036 More actions · Answer 2

Do I have to go through the all the CreateObject("ADODB.Command") stuff to execute a query to get the count back or is there a quicker way of doing this?

LinkedIn Profile

Phil Parkin SSC Guru Points: 246991 More actions · Answer 3

That's the way I would do it - here's some sample code - notice that I have put the connection string in a global variable for ease of use:

Dim strConn

Dim objConn

Dim objCmd

Dim rs

'Create connection

strConn = DTSGlobalVariables("ConnectionString").Value

Set objConn=CreateObject("ADODB.Connection")

objConn.open strConn

Set objCmd=CreateObject("ADODB.Command")

objCmd.ActiveConnection = objConn

objCmd.CommandText = (select statement here)

Set rs = objCmd.Execute()

(process your resultset here)

'Tidy up

set rs = nothing

set objCmd = nothing

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 4

Alteratively select @@rowcount from a sql statement and use a raiserror if @@rowcount = 0.

See this related article.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=224372#bm225473

------------------------------
The Users are always right - when I'm not wrong!

PW-201837 SSC-Insane Points: 20805 More actions · Answer 5

The problem (or advantage, depending on your viewpoint) of DTS is that it often provides a half dozen different ways to do something.

Define a Connection object, and run an Execute SQL task on it. This SQL task has an output parameter, which places a boolean into a global DTS variable if there are any records where ProcessedDate IS NULL.

Following this is an ActiveX script task makes the package self-modifying. Following the ActiveX script task is an Execute Package Task. You write VBScript to set the properties of the sub-package, depending on the global variable. Specifically, you grab a reference to the Execute Package task in VBScript, and set the Task.CustomTask.PackageName property. This makes the final step conditional on what was returned by the SQL on ProcessedDate IS NULL.

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 6

Another option would be to use a Dynamic Properties Task to set a Global Variable to a Count or Return value from a Query. Then in an ActiveX Task check the Global Variable and do what needs to be done from there.

Good Luck

Darrell