Skipping a step in a package IF a directory folder is empty

Question

Skipping a step in a package IF a directory folder is empty

Viewing 7 posts - 16 through 21 (of 21 total)

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

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 1

SSIS packages are not interactive processes - they are background processes and do not provide for user interaction. There are no options to prompt a user to continue.

As for truncating - the tasks inside the container will only execute if you get into the container. If using a foreach loop - over a directly of files - it will only get inside the container when there are files found to be processed. If no files exist then you never get into the container and none of the tasks in that container execute. So - using a truncate inside a foreach loop will only execute when there is a file to be processed. No need to check if the file was found or not before executing a truncate inside a foreach loop.

A sequence container allows you to group tasks together so they execute within their own set of precedence constraints. If you only want a set of tasks to process for a specific condition - then you would have some task before the sequence that identifies that condition and then place a precedence constraint between that task and the sequence. If the precedence is met - that *all* tasks in the sequence container will be executed. No need to check for each individual task inside the container.

The simpler you make the process - the simpler it will be to manage and maintain. Don't make it more complex than it needs to be.

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

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 2

I've got my Foreach Loop Container in PROD and it is doing what it is supposed to do so this is really just a learning experience.

I put this in TEST: I have at the start a Script Task with the code below (which is failing Error: "The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there."), 3 variables (TrainingFilePresent(set default to 1), TrainingFilePath (set to C:\Users\wally\desktop\Training\), TrainingFileName (set to Training.csv), 2 Sequence Containers (one for constraint Success (load Training File), one for constraint Failure (Load Alt File)).

public void Main()
        {
            String filepath = Dts.Variables["user::TrainingFilePath"].Value.ToString() + Dts.Variables["user::TrainingFileName"].Value.ToString();
            if (File.Exists(filepath))
            {
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            else
            {
                 Dts.TaskResult = (int)ScriptResults.Failure;
            }   
        }

FileName

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 3

Are these SSIS variables or package variables or project parameters - and did you add them as read-only variables in the script task properties?

I understand what you are trying to do here, I just don't think it is necessary - and haven't found a reason yet that would require that type of workflow.

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

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 4

The variable for the path is ReadOnly, the variable for the filename is readOnly, the variable for the FilePresent is Read/Write

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 5

Okay - assuming they are all SSIS variables, then make sure you use the proper case. Also, I would rewrite the code...

// Get the DTS variables into local string variables
string filePath = Dts.Variables["User::TrainingFilePath"].Value.ToString();
string fileName = Dts.Variables["User::TrainingFileName"].Value.ToString();

// Combine file path and name
string fullFileName = Path.Combine(filePath, fileName);

if (File.Exists(fullFileName))
{
     Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
     Dts.TaskResult = (int)ScriptResults.Failure;
}

Also - make sure the path includes the trailing slash - as in: \\someserver\somedirectory\ or D:\somefolder\

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

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 6

Jeffrey Williams wrote:

SSIS packages are not interactive processes - they are background processes and do not provide for user interaction. There are no options to prompt a user to continue.

Technically you could put a message box in a script task, just make sure you don't deploy it like that :P.

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 7

I found the problem - the Constraint needed just an expression and it needed double equal signs: @[User::TrainingFilePresent]==1 and @[User::TrainingFilePresent]==0

Thanks!