December 15, 2005 at 7:48 am
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.
December 15, 2005 at 8:01 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 15, 2005 at 8:55 am
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?
December 15, 2005 at 9:46 am
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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 15, 2005 at 10:12 am
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
December 15, 2005 at 11:27 am
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.
December 15, 2005 at 4:14 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply