August 9, 2009 at 4:47 pm
I am trying to make a simple package to load something from an Excel file to the server. That part I have completed, and is fairly simple.
The next part was to create a script task that checks for the file's existence before performing the data flow task. Everything seems to check out, but when I execute the package, only the script task completes.
If I break the precedence constraint, both executables process. I'm not sure why the script task is not then allowing the data flow task to complete.
I have included error checking to see that the variable I'm checking in the precedence constraint evaluates to True. I'm sure it's something simple I'm missing.
I receive no error messages of any kind.
I would attatch the project, but don't see that the dtsx exetension is supported.
Thanks in advance!
August 9, 2009 at 6:00 pm
I believe I have narrowed the problem down to the precedence constraint.
When I remove the contstraint, the Script task and data flow task execute as expected.
The precedence task is pretty straight forward @[User::FileExists]==True
I have message boxes that the variable is in fact True, so not quite sure what the problem is.
This is not a complicated issue, not quite sure why it's not working.
Thanks again.
August 9, 2009 at 6:56 pm
I have put breakpoints in my script, and I know that it is working correctly. The script is evaluating to True for my variable, and 'Success' for the task.
Is there a way to debut precedence constraints.
August 9, 2009 at 7:54 pm
willmiss20 (8/9/2009)
I believe I have narrowed the problem down to the precedence constraint.When I remove the contstraint, the Script task and data flow task execute as expected.
The precedence task is pretty straight forward @[User::FileExists]==True
I have message boxes that the variable is in fact True, so not quite sure what the problem is.
This is not a complicated issue, not quite sure why it's not working.
Thanks again.
What is the "Evaluation operation" for the precedence constraint? Only "Expression" or a constraint as well?
Does the constraint have access to the User::FileExists variable? Check the variable scope to see this.
August 9, 2009 at 8:27 pm
Thanks for the reply.
The constraint is set to 'Expression' only.
I have been able to now get the package to work, when I set it to 'Constraint', and then Success
I think you're right about the constraint having access to the variable.
The variable scope is the Script, so I think it should. But I'm not sure how else to check.
August 9, 2009 at 8:32 pm
Well, scratch that last reply. Turns out with the script set to "Constraint" and "Success", it is success full every time now, even when the file does not exist.
Back to square one!
August 9, 2009 at 8:34 pm
willmiss20 (8/9/2009)
Thanks for the reply.The constraint is set to 'Expression' only.
I have been able to now get the package to work, when I set it to 'Constraint', and then Success
I think you're right about the constraint having access to the variable.
The variable scope is the Script, so I think it should. But I'm not sure how else to check.
Not sure why the Expression is not working for you without more info. Possibly attach a few screenshots (variables window, precedencen constraint window, control flow showing the script task and the 2nd task).
August 9, 2009 at 8:37 pm
willmiss20 (8/9/2009)
Well, scratch that last reply. Turns out with the script set to "Constraint" and "Success", it is success full every time now, even when the file does not exist.Back to square one!
Yep. This may be enough for you depending on what the purpose of the script task is. Is it only checking whether a file exists? If so then one option would be to fail the script task (possibly by throwing an exception and catching and logging it in the OnError event handler) if the required file doesn't exist. If the file does exist then mark the script task as successful and use the Contraint set to Success only. It all depends on the requirements though...
August 9, 2009 at 8:57 pm
This is the code in the script
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'
' Add your code here
'
Dim fileLoc, fileName As String
Dim test As String
If Dts.Variables.Contains("User::ExecDir") = True AndAlso _
Dts.Variables.Contains("User::FileName") = True Then
fileLoc = CStr(Dts.Variables("User::ExecDir").Value)
fileName = CStr(Dts.Variables.Item("User::FileName").Value)
'System.Windows.Forms.MessageBox.Show(”FileDir:”+fileLoc+”FileName:” + fileName)
If File.Exists(fileLoc + fileName) Then
Dts.Variables.Item("User::FileExists").Value = True
test = "True"
System.Windows.Forms.MessageBox.Show("File exists")
Dts.TaskResult = Dts.Results.Success
Else
Dts.Variables.Item("User::FileExists").Value = False
test = "False"
System.Windows.Forms.MessageBox.Show("File not exists")
Dts.TaskResult = Dts.Results.Failure
End If
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
MsgBox(test)
End Sub
End Class
I have attatched a few screen shots
August 9, 2009 at 9:23 pm
willmiss20 (8/9/2009)
This is the code in the script' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'
' Add your code here
'
Dim fileLoc, fileName As String
Dim test As String
If Dts.Variables.Contains("User::ExecDir") = True AndAlso _
Dts.Variables.Contains("User::FileName") = True Then
fileLoc = CStr(Dts.Variables("User::ExecDir").Value)
fileName = CStr(Dts.Variables.Item("User::FileName").Value)
'System.Windows.Forms.MessageBox.Show(”FileDir:”+fileLoc+”FileName:” + fileName)
If File.Exists(fileLoc + fileName) Then
Dts.Variables.Item("User::FileExists").Value = True
test = "True"
System.Windows.Forms.MessageBox.Show("File exists")
Dts.TaskResult = Dts.Results.Success
Else
Dts.Variables.Item("User::FileExists").Value = False
test = "False"
System.Windows.Forms.MessageBox.Show("File not exists")
Dts.TaskResult = Dts.Results.Failure
End If
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
MsgBox(test)
End Sub
End Class
I have attatched a few screen shots
Ok screenshots show that the FileExists variable's scope is tied to the Script Task only. The precedence constraint won't be able to evaluate the expression since it won't have access to the variable. The package shouldn't even run because of this issue so I'm not sure how your script task managed to execute. Anyway re-create that variable at the package level and see how it goes.
August 9, 2009 at 10:07 pm
THANK YOU!!!!!!!
Figured it was something dumb I was over looking. Although, I am new to SSIS, and not even really an IT person.
It works like a charm now!
Thanks for taking the time on a sunday night. 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply