June 26, 2002 at 9:10 pm
I have a DTS package where the first task is a SQL task that simply selects * From table where something = something. If this statement does not return any results then I DO NOT want the package to run at all. I have used the Workflow (success, completion, fail) but they are not robust enough for me to set any parameters for determining whether or not the package will run based on the above example.
June 27, 2002 at 10:42 am
Can you use multiple packages? One that determines what to do and calls another? Or multiple steps in a job?
Steve Jones
June 27, 2002 at 11:10 am
Well since my original posting my question really has come to fruition. Let me try again:
I have created a Dynamic Property task that creates a Global variable using a SQL string that looks like this:
Declare @gvQueryCount numeric(1)
SET @gvQueryCount = (SELECT Count (*)
FROM Balancing_totals
WHERE (data_src = 'VIADRUG') AND (Paid_Year_Mo = CONVERT(varchar(6), DATEADD(m, - 1, GETDATE()), 112)))
SELECT @gvQueryCount
I hit refresh and I get either 1 or 0 depending on whether the record exists.
Next I have an ActiveX script that checks to see what the global variable value is.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim Results
If DTSGlobalVariables("gvQueryCount").Value = 0 Then
Results = "Here is the value from the GV: " & DTSGlobalVariables("gvQueryCount").Value
MsgBox (Results)
Main = DTSTaskExecResult_Failure
else
Results = "Here is the value from the GV: " & DTSGlobalVariables("gvQueryCount").Value
MsgBox (Results)
Main = DTSTaskExecResult_Success
End if
End Function
If the value of the GV = 0 then I have set up another SQL task (On failure workflow) that simply sends an email out that says the package could not start.....
If its not 0 then it continues on to the next SQL Task step via on Success Workflow.
The problem is that no matter what the value of the global variable is I still get the following error:
The task Reported Failure on Execution.
Also in the above ActiveX script I want to display a MsgBox to show me what the value of the GV was. I get the pop-up but I never get the : DTSGlobalVariables("gvQueryCount").Value
Any help would help. Thanks
June 27, 2002 at 12:51 pm
Well I think this will do it however I’m sure they’re maybe another way.
The 1st thing I did was to create a Global variable: gvQueryCount
I made sure not to give a value at set its type to string.
Next I needed to set the Value of the Global Variable in a Dynamic Task Property by using a SQL SELECT Statement:
Declare @gvQueryCount numeric(1)
SET @gvQueryCount = (SELECT Count (*)
FROM Table
WHERE (data_src = 'VIAMRS') AND (Paid_Year_Mo = CONVERT(varchar(6), DATEADD(m, - 1, GETDATE()), 112)))
SELECT @gvQueryCount
This way depending on what the results from the SQL statement were I could then determine what the value of the global variable would be.
Next I needed a way to decide whether or not I wanted the package to continue to run or not, so I added a ActiveX script :
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim Results
If DTSGlobalVariables("gvQueryCount").Value = 0 Then
Results = "Here is the value from the GV: " & DTSGlobalVariables("gvQueryCount").Value
MsgBox (Results)
Main = DTSTaskExecResult_Failure
else
Results = "Here is the value from the GV: " & DTSGlobalVariables("gvQueryCount").Value
MsgBox (Results)
Main = DTSTaskExecResult_Success
End if
End Function
This would then allow me to go to the next task or to stop and send an email letting the appropriate people know that an error had occurred.
It was important for me to perform this task at the beginning of this package because of the dependencies throughout the package and to keep it as automated as possible.
Hope it helps someone else and also thanks for everyone who had input!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply