I have a package that loads data to a DB from a file that is placed in a folder by a user. I have this part of the package working ok, but if the file is not there, then I want to be able to check this before running anything, and decided to use a script task, along with an email task for when the file is not found.
I've set this up with a new variable "FileExist" and created the script as per below:
Public Sub Main()
'
'
Dts.Variables("FileExist").Value = File.Exists(Dts.Variables("xlFileName").Value)
Dts.TaskResult = ScriptResults.Success
End Sub
And the variables defined in the task like this:
The Variables in the package like this:
I keep getting this error thrown every time I run the package:
The type of the value (Boolean) being assigned to variable "User::FileExist" differs from the current variable type (String).
How can a boolean value being assigned to the variable, which is a Boolean type, cause it to fail?
I'd read that the scope of the variable (Package) might be the issue and to change it from package to the task, although the option to do this seems to be grayed out.
Could there be any other reasons this keeps failing? The strange thing is it's raising that error in the output but the actual package is not failing, but the email is not sending.
I've already came across that and tried changing the variable scope to the script task, which made no difference. I moved the script task and email inside the for loop as well and that made no difference either.
It just stops on the loop and doesn't do anything. I've put a break on the loop to check the output and variables, the xlFileName = "" and FileExist variable is set to false, which is the conditions I want to then be able to send the email.
I've two paths coming out the script task, one for success, one for failure that goes to email send task the paths have the expression and Constraint option set to:
I've reset the script task code to below just to see what if any difference it's making. I don't think it's even getting to the code, but the first line is underlined as if there is something wrong with it, and when you hover over it, the pop up says "Implicit conversion from Object to String"
Public Sub Main()
If (File.Exists(Dts.Variables("xlFileName").Value).ToString) Then <<<<Underlined
Dts.TaskResult = ScriptResults.Success
Else
Dts.TaskResult = ScriptResults.Failure
End If
End Sub
Does file.exists not return True or False? If so why does it keep talking about a string causing a problem?
it "should" return boolean, but yours isn't. How about breaking it into separate steps. Put filename into a string variable, then check for existence and put results into another variable. Then test that for True/False and return Success or Failure. At least that'll give you an idea what's happening
Could be that you already have a FileExist in the package level and then in another level and it is picking up the string version of FileExist? as follows. 1 package level one work flow level, same named variable but different types
Public Sub Main()
Dts.Variables("User::FileExists").Value = CType(File.Exists(Dts.Variables("User::FileName").Value.ToString()), Boolean)
Dts.TaskResult = ScriptResults.Success
End Sub
I've just realised something, before I had the script task and just the loop working to load the file, all the variables are set at runtime with delay validation set to true, when I put a breakpoint on the script task inside the loop none of the variables have any values in them when the script task starts. So how can I do this script task as I need to load the file variable name, which doesn't happen to later?
@Grifter, send me your email and I'll send you a working SSIS package. Did the code I provided not work for you? I used package deployment and not project, so the variables were local to the package. Also, you may need to change variable names to match your solution. Lastly, I used VB because that is what you were using, but would rather use C#
