November 27, 2007 at 12:59 pm
I have a series of Execute SQL tasks that need to be executed only if one of my variables holds a certain value. I did this through a ActiveX script task in DTS but I can't figure out how to do it in the new script task. The dts object doesn't seem to be able to set enabled/disabled for other tasks in the package. Does anyone have an Idea how to do this?
November 27, 2007 at 1:36 pm
If I understand you correctly, you can accomplish by using a script task to populate a boolean variable (in this example validating wether or not a file exists):
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
'Purpose: Determines if the file exists and sets the varFileExists boolean value.
Public Sub Main()
Dim fileLoc, fileName As String
If Dts.Variables.Contains("User::varFileName") = True Then
fileName = CStr(Dts.Variables.Item("User::varFileName").Value)
'System.Windows.Forms.MessageBox.Show("FileDir:"fileName)
If File.Exists(fileName) Then
Dts.Variables.Item("User::varFileExists").Value = True
'System.Windows.Forms.MessageBox.Show("File Exists!")
Else
Dts.Variables.Item("User::varFileExists").Value = False
'System.Windows.Forms.MessageBox.Show("File Does Not Exist!")
End If
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
End Class
Then use an expression on a precedence constraint to evaluate:
@[User::varFileExists]==True
More info on precedence constraints:
http://msdn2.microsoft.com/en-us/library/ms140153.aspx
Tommy
Follow @sqlscribeNovember 27, 2007 at 1:59 pm
What I ended up doing and it seemed to work was in the expressions on the task I needed to disable I set the disable property to @[User::Variable] != "Enable" Thanks for the reply.
November 28, 2007 at 5:15 am
Here is a how-to for precedence constraints with pictures that I created for someone else.
It should help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply