SSIS Package fails when incoming file not Exist in Source path.

Question

SSIS Package fails when incoming file not Exist in Source path.

Saintmount.Sql

Ten Centuries

Points: 1023
More actions
June 4, 2011 at 2:46 pm

#240873

Hi All,
I have a package which contains a data flow task to read Flat file and populate a SQL table. Flat File source task will read input file from the INPUT folder and process them and at the end it will move the processed input files to the Achieve folders. So, sometimes there will not be any input files present (if support team does not populate new input files) in the INPUT folder and my package throws error that source file does not exist.
I've used Script task if file not present (i.e. already moved to Archive folder) in the source folder don’t process the Data Flow task. But still try trying to execute the Dataflow task, so getting an error. I want to pass to else block such situation.
Any one knows how to fix this issue? I wanted to make package should NOT run such situation. Since we do not have the File in Source path.
Thanks in advance.
Vijay

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

One easy way is to put your dataflow inside a Foreach (file) container - using the input file's name as the filespec. Then it will not execute if the file is not there.

You may also have to set the delay validation property to true on your input file connection.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Saintmount.Sql Ten Centuries Points: 1023 More actions · Answer 2

Yes,I've created Data flow task in the For each loop only.But Still it's throwing error in the For each loop Container.

Pls find my package in the attachment. thanks.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Vijay.Sql (6/4/2011)
Yes,I've created Data flow task in the For each loop only.But Still it's throwing error in the For each loop Container.
Pls find my package in the attachment. thanks.

If the dataflow is inside the for each loop, how come you are starting the dataflow for a file that doesn't exist?

If it doesn't exist, the for each loop shouldn't pick it up and that iteration of the loop should be skipped.

You can try putting a script task in front of the dataflow. Check for existence of the file and set a boolean variable with the result (e.g. FileFound).

Connect the script task to the dataflow and in the precedence constraint set it to success and expression. The expression should be FileFound == True.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Saintmount.Sql Ten Centuries Points: 1023 More actions · Answer 4

Thanks for your reply. My requirement slightly changed.

I need to unzip the file from source path, that is not a issue I’ve handled by Execute process task.

Problem is If I do not have file any file(Zip file). I don’t want to execute the Execute process task.

How can I handle in script task for the checking the incoming file not Exist in Source path.

I have handled in the precedence constraint @[bolFileExists]==True ( will execute the execute process task for unzip the file)

another path precedence constraint set to @[bolFileExists]==False( will execute the another part of script task for showing message(like file not source path)

But package trying to execute the execute process task, even thought if i do not have file in source path. Finally throws error. It should not execute the process task.

Could you please help out how handle this issue.

Thanks.

SQLFanatic SSCommitted Points: 1640 More actions · Answer 5

Hi Vijay,

As Koen mentioned above, you may use a simple script task before any other process/task in your package to check the input file existence and hold the value in a variable accordingly. Let's say you use @File_Exist = 'YES' incase the input file is present.

Then defiene the precedence constraint as following:

Evaluation Operation: Expression and Constraint

Value: Success

Expression: @File_Exist == "YES"

Select 'Logical And' operation.

You can use another precedence constraint in a similar fashion for @File_Exist = 'NO' i.e. if there's no input file.

🙂

________________________________________________________________
"The greatest ignorance is being proud of your learning"