SQLTeam.com | Weblogs | Forums

Using file system task to check if file exists

SSIS and Import/Export
#1

Hi

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.

SSIS_Script_Task

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:

SSIS_FileExist_Variables

The Variables in the package like this:
SSIS_FileExist_Variables_2

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.

Thanks

G

#2

hope this link helps

#3

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:

@[User::FileExist]=True (Success)
@[User::FileExist]=False (Failure)

SSIS_For_Loop_with_Script_Task

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?

Thanks

G

#4

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

#5

@mike01 Strange thing is it's showing False in the variable FileExist when I do a breakpoint on the loop.

SSIS_Script_Task_Variable_Value

#6

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

image

#7

I did a quick poc and this worked for me

Public Sub Main()
    Dts.Variables("User::FileExists").Value = CType(File.Exists(Dts.Variables("User::FileName").Value.ToString()), Boolean)
    Dts.TaskResult = ScriptResults.Success
End Sub
#8

these two highlighted parameter buttons have same look and feel but do 2 different things

image
image1549×172 9.2 KB

left one is Control Flow level, right hand side package level parameters

#9

My parameters list is empty.

#10

which parameter list. as shown above there are multiple sources of parameters

#11

I don't know what other parameter list there is as you can see this is what I see below:

SSIS_ParameterLIst
SSIS_ParameterLIst1053×123 5.48 KB

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?

#12

image
image1035×114 8.18 KB

left side parameters button component level parameters
right hand side parameters button (package level parameters)

also notice what @mike01 posted.

Dts.Variables("User::FileExists")

yours

Dts.Variables("xlFileName")
#13

did that code I posted work? As Yosiasz said, you'll need to change the parameter names to match yours

#14

It's still giving an error in the output- as below, although the package itself is finishing with success.

The variable "User::xlFileName" was not found in the Variables collection. The variable might not exist in the correct scope

The xlFlieName variable is at package level, it's not changed.

I don't know what I'm missing regarding parameters, I can't see any parameters anywhere, only the list of variables?

#15

my idea
please ask in SSIS forum ..

#16

What ssis forum are you meaning?

#17

Grifter, send me your email and I'll send you a working ssis package

#18

he has asked in this SSIS forum.

#19

i mean in "all other" SSIS forums .. please google search

my idea is
Somewhere someone can provide the solution

#20

@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#