How to do

Question

Post reply

How to do

TriggerMe

SSChampion

Points: 11752
More actions
March 6, 2007 at 7:00 pm

#117770

I have an SSIS Package that does the following.
I have Excell Spreadsheet with data in it that gets uploaded to a table in a SQL 2005 Database. This works as it's suppose to.
What I would like to do is check the directory for the existence of the Excell Spreadsheet and if the spreadsheet is not there, then I do not want the remainder of the SSIS Package to continue but rather end the SSIS Package.
I know I can write a VBScript using the "file system object" to check for the excell file but how do I run that first in the SSIS Package and if it doesn't exist, how do I stop the SSIS Package from continuing?
Or better still, does SSIS have a much more professional way of doing this?
Kindest Regards,

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

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

Leonce SSC Enthusiast Points: 198 More actions · Answer 1

You could use a ForEach loop container and put the data flow task inside.

Leonce

TriggerMe SSChampion Points: 11752 More actions · Answer 2

I tried your suggestion.

I deleted the file from the destination and then ran the SSIS Package but the package fails with errors.

If I place the file back into the destination, the SSIS Package works.

So I'm trying to get the package not to proceed if the file doesn't exist.

Kindest Regards,

S. Kusen SSChampion Points: 10936 More actions · Answer 3

You could create a Script Task Component that sets the value of a package variable to true or false based on whether or the file is there.

Then, you can change the constraint on the "success" arrow of the script task to "Expression and Constraint". For the Expression, you would put @variable_name = 1 (where 1 equates to the file being there).

First, create a variable called "is_file_there" of type int32 and have the scope be of the package level.

Then, the control flow would be like this:

1. Script task checking for the file.

If file is there, set is_file_there = 1,

Else, set is_file_there = 0

2. Your first task after the file is there

Between the 2 components, set a precedence constraint and then double click on the constraint and change it to "Expression and Constraint" and set the constraint to success and the expression to "@is_file_there = 1" (it may be @is_file_there == 1, not 100% sure)

I would also suggest putting another component on there to send out an email notification when @is_file_there = 0 just so you know the file wasn't there.

Let me know if that helps you out.

Steve

TriggerMe SSChampion Points: 11752 More actions · Answer 4

Stepehen,

I will try your suggestion. I just have to learn how to create a variable and where to find the script task.

Yes! I'm new to SSIS as well.

I'll let you know how i go.

Kindest Regards,

Leonce SSC Enthusiast Points: 198 More actions · Answer 5

I created a package with a Foreach Loop container. It loops on all files in a directory and put the current file name in a variable "FileName".

In that container, there's a script task. The script task simply shows the value of the "FileName" variable.

Well the package works whether the are files or not... I guess I didn't understand your problem.

Leonce