July 16, 2002 at 6:34 am
2 PART QUESTION
1) I have a package that will run every month, (click on this link to print out the DTS flow diagram: http://www.gr-ipa.org/DTSExe/DTS_flow.html) that uses a data text file. I need to move this data file from dir A to dir B when the package 1st runs. However if I check to see where the Data file lives prior to running it for the current month then the following assumptions can be made:
1)If found in dir A then the package has not been run therefore run it and move the data file to dir B
2)If found in dir B then the package has already run for the current month therefore lets rerun it. In this process we must 1st remove any data that had been placed in the destination table from the previous load. I accomplish this by using a SQL Task to Delete all records WHERE Month(Ins_Date) = Month(getDate())
3)No file found then do not run it
The problem that seems to be occurring is, if the assumption is either 1 or 2 from above the package will only run as far as checking for the global variable then the package ends stating that it has completed. In fact the package has not been completed.
Package stops here: The ActiveX script for checking the global variable value uses a dynamic properties task that sets the global variable based on a SQL query that looks for a record in another table. If the value is 1 then the package again will continue. If the value is 0 then the package will end. Here is the ActiveX script to get the value of dynamically generated global variable based on Dynamic Properties Task.
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
Here is the Dynamic Properties task:
Declare @gvQueryCount numeric(1)
SET @gvQueryCount = (SELECT Count (*)
FROM Balancing_totals
WHERE (data_src = 'XeroxClaims') AND (Paid_Year_Mo = CONVERT(varchar(6), DATEADD(m, - 1, GETDATE()), 112)))
SELECT @gvQueryCount
2) The second part to this question is:
Is there a way in ActiveX scripting in addition to writing IF THEN statements to indicate precisely which task will be next in line to run. I know that you can skip tasks and we can even right ActiveX scripts to run certain tasks according to specified constraints (Main = DTSStepScriptResult_ExecuteTask or Main = DTSStepScriptResult_DontExecuteTask)
And also create workflow scmatics to run certain tasks based on Main = DTSTaskExecResult_Success or Main = DTSTaskExecResult_Failure
But is there a way to specifically indicate which task to move to or go to based on ActiveX criteria using IF THEN’s
It seams that even though using workflow at time is just not as dynamic or at time is buggy. Meaning in the flow diagram - look at the ActiveX script that checks the value of the global variable. If I remove the success workflow coming from ActiveX (Check if File Exists) and I know for sure that my file is dir B then the package indeed gets to the finish. And the same holds true if I know that the package is running for the 1st time and I remove the on completion workflow going to check Global variable coming from Delete/ Rollback it will run. WHY CAN’T I have multiple workflows going to one task?
WEIRD Huh
Any help would help.
Thanks
July 16, 2002 at 8:11 am
You want to do something like this:
Set objPackage = CreateObject("DTS.Package")
objPackage.LoadFromSQLServer "Morpheus","","","256",,,,"mypackage"
Set objExecSQLPackage1 = CreateObject("DTS.Package")
objExecSQLPackage1.LoadFromSQLServer "Morpheus","","","256",,,,"myotherpackage"
If foo = 0 Then
objExecSQLPackage1.Execute
Else
objPackage.Execute
End If
Hope this helps!
Michael
quote:
2 PART QUESTION1) I have a package that will run every month, (click on this link to print out the DTS flow diagram: http://www.gr-ipa.org/DTSExe/DTS_flow.html) that uses a data text file. I need to move this data file from dir A to dir B when the package 1st runs. However if I check to see where the Data file lives prior to running it for the current month then the following assumptions can be made:
1)If found in dir A then the package has not been run therefore run it and move the data file to dir B
2)If found in dir B then the package has already run for the current month therefore lets rerun it. In this process we must 1st remove any data that had been placed in the destination table from the previous load. I accomplish this by using a SQL Task to Delete all records WHERE Month(Ins_Date) = Month(getDate())
3)No file found then do not run it
The problem that seems to be occurring is, if the assumption is either 1 or 2 from above the package will only run as far as checking for the global variable then the package ends stating that it has completed. In fact the package has not been completed.
Package stops here: The ActiveX script for checking the global variable value uses a dynamic properties task that sets the global variable based on a SQL query that looks for a record in another table. If the value is 1 then the package again will continue. If the value is 0 then the package will end. Here is the ActiveX script to get the value of dynamically generated global variable based on Dynamic Properties Task.
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
Here is the Dynamic Properties task:
Declare @gvQueryCount numeric(1)
SET @gvQueryCount = (SELECT Count (*)
FROM Balancing_totals
WHERE (data_src = 'XeroxClaims') AND (Paid_Year_Mo = CONVERT(varchar(6), DATEADD(m, - 1, GETDATE()), 112)))
SELECT @gvQueryCount
2) The second part to this question is:
Is there a way in ActiveX scripting in addition to writing IF THEN statements to indicate precisely which task will be next in line to run. I know that you can skip tasks and we can even right ActiveX scripts to run certain tasks according to specified constraints (Main = DTSStepScriptResult_ExecuteTask or Main = DTSStepScriptResult_DontExecuteTask)
And also create workflow scmatics to run certain tasks based on Main = DTSTaskExecResult_Success or Main = DTSTaskExecResult_Failure
But is there a way to specifically indicate which task to move to or go to based on ActiveX criteria using IF THEN’s
It seams that even though using workflow at time is just not as dynamic or at time is buggy. Meaning in the flow diagram - look at the ActiveX script that checks the value of the global variable. If I remove the success workflow coming from ActiveX (Check if File Exists) and I know for sure that my file is dir B then the package indeed gets to the finish. And the same holds true if I know that the package is running for the 1st time and I remove the on completion workflow going to check Global variable coming from Delete/ Rollback it will run. WHY CAN’T I have multiple workflows going to one task?
WEIRD Huh
Any help would help.
Thanks
Michael Weiss
Michael Weiss
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply