Skipping a step in a package IF a directory folder is empty
-
I have an SSIS package (that is running fine) that has several steps each of which loads a .csv file from a folder to a SQL Server table. What I need to happen is for a step to be skipped if the file is not in the specific folder.
I have the basics put in place so far: a data flow task with a script task preceding it that looks for the file using 3 variables: TrainingFileName (Training.csv), TrainingFilePath (C:\Users\wally\desktop\Training), and TrainingFilePresent (1 or 0, default = 1). I have the C# script also in place that passes or fails the process:
public void Main()
{
String filepath = Dts.Variables["user::TrainingFilePath:"].Value.ToString() + Dts.Variables["user::TrainingFileName"].Value.ToString();
if (
File.Exists(filepath))
{
Dts.Variables["user::TrainingFilePresent"].Value = 1;
}
Dts.TaskResult = (int)ScriptResults.Success;
}I placed a Precedence Constraint between the Script Task and the Data Flow Task:
What I don't think I have in place is the ability to skip the step and go on to the next or does what I have in place for a plan simply fail the execution of the package at that step?
- This topic was modified 3 years, 5 months ago by DaveBriCam.
-
If you want to "skip" a step, you'll need to have 2 paths to the step *after* the one you want to skip. The flow that goes to the skippable task will need a Expression and Constraint Evaluation Operation like you have above. For 2 flows that go to the "After Task" the one from the skippable task won't need an expression, but the flow from the Initial Task to the "After Task" will need one, with the opposite evaluation to the the skippable task (perhaps
@Variable == 0). You'll also need to select Logical OR at the bottom. You'll Control Flow will then look something like this (ignore the Error icons, they are due to be not configuring the Expression Tasks):Notice that the 2 other lines are dotted, meaning a Logical OR, rather than Logical AND.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
So do I still need the Script Task with the C# Code?
- DaveBriCam wrote:
So do I still need the Script Task with the C# Code?
Depends if you need to do what you need to do in the script task. You're using it to determine if the file exists, so presumably "yes"; though you could use a different methods to do the same job.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
I was about to write something similar, but Thom A beat me to it. In my case, both precedent constraints are "Logical OR, one constraint must evaluate to true" so both constraints coming from the Initial task (C170... here) would be dashed green lines, with mutually exclusive conditions.
E.g., at the end of a control flow, the decision to run a file archive step depends on if the package is running in production. If it is in production, run the archive step, if not, branch around it.
The precedence expressions for the branches out of C170 are:
@[User::_SQLDATABASE] != @[User::_PROD_DB_NAME]
@[User::_SQLDATABASE] == @[User::_PROD_DB_NAME]
In your case, you'd probably want to use the value returned by the script task in the contraints to control branching.
-
My preferred way of handling such things is to use a FOREACH container. The contents of the (appropriately configured) FOREACH container will execute only if the specified file is found, otherwise execution continues to whatever follows the container.
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 -
So I could in fact use the Expression Tasks alone without the Script Task, and those Expression Tasks could determine if the file existed in the folder?
If I wanted to take everything a step further and have a warning message to show during the execution like "Your training folder is empty, continue?" I would definitely want the C script capabilities (?).
@phil-parkin: do you mean use the "Foreach Loop Container"? Would I then have in the container Expression Tasks or a Script Task?
-
@phil-parkin: do you mean use the "Foreach Loop Container"? Would I then have in the container Expression Tasks or a Script Task?
Yes, that's it.
Configure the 'Collection' node properties as follows:
- Enumerator - Foreach file enumerator
- Folder - as required
- Files - as required
You can use Expressions to set these properties dynamically at runtime.
Once you've got that done, anything you put inside the container executes only if the specified file is found in the specified folder. If not found, execution skips to the next task in the control flow.
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 - Phil Parkin wrote:
@phil-parkin: do you mean use the "Foreach Loop Container"? Would I then have in the container Expression Tasks or a Script Task?
Yes, that's it.
Configure the 'Collection' node properties as follows:
- Enumerator - Foreach file enumerator
- Folder - as required
- Files - as required
You can use Expressions to set these properties dynamically at runtime.
Once you've got that done, anything you put inside the container executes only if the specified file is found in the specified folder. If not found, execution skips to the next task in the control flow.
I find that this greatly simplifies the package - in most cases. The only time this doesn't work appropriately is when you have multiple files in the folder that have to be processed in a specific order. For example, date stamped files - or files that start with 'A_', 'B_', etc...
For those cases, I use a script task to build a recordset object and return a sorted dataset in that object. Then use the foreach loop container to loop over that object.
You can also use a sequence container - everything in the sequence container executes as a unit. Using a constraint prior to the sequence container you can then branch to different sequence containers based on some logic.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I believe the method of utilizing the Foreach Loop Container is the most straightforward solution, but I'm sure I need to learn more about the other methods, as situations requiring them are sure to arise. I configured the collection node with as yet no known issues.
What I have now inside the container is first a Script Task to set the value of my TrainingFilePresent to "1" then I have my Precedence Constraint again like below:
Following that is my Data Flow task. So I'm assuming if the script testing my variable "TrainingFilePresent" fails it will return a "0" and exit the container and go on to the next step.
Is this what I should be putting into place?
-
Why are you still checking - inside the foreach loop - for file existence? The purpose of the foreach loop is to only process the tasks inside the container *if a file has been found*.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
There is no need to use script tasks to set the value of variables - use Expression tasks for that.
If you want to set a variable to indicate a file's existence, set it to false before the foreach container and then to true within the foreach container, using Expression tasks.
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 -
If I needed still, with using the Foreach Loop container, to check for the existence of the file do I place an Expression task before the step to set the variable to "0" with a line like "@FileFound==0" then inside the Foreach Loop container another Expression task that sets it to "@FileFound==1" and do I need to do the first one in a Precedence Constraint before the container?
-
Why would you check for the existence of the file - when the foreach loop has already found the file? If the tasks inside the foreach loop container are executed - it is because the foreach container found a file that matches the criteria you set for that container.
Again - if the foreach loop container does not find a file then the tasks inside that container will not be executed. So why bother setting a variable? What is the purpose of that variable and how is it going to be used? It seems you want to use that variable to determine if a task should or should not execute - but that doesn't matter because the tasks won't execute if a file is not found.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
What I would like is the flexibility of things like displaying a message like "File not found - continue Yes/No?" or truncating a SQL Server table based on the results of the file being found or not, several eventual requirements may be asked for - and true I probably just want a Sequence Container instead of a Foreach Loop Container.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply