File counter expression in SSIS using execute process task output variable

Question

I have a console application in c# that downloads files from a website. I have created a variable named Filecount that counts the number of files downloaded in that instance.

In SSIS, I have set the StandardOutputVariable in the Execute process task configuration as User::FileCount that should pass through the number of files that it has downloaded.

I want to create an SQL task that will truncate the table if the file count is greater than 0.

However, when I try to evaluate my expression, it always comes back as true however, this should not occur as no counts have been passed through yet meaning it should be evaluated as false.

Can someone explain if I have either written the expression wrong or set the variable incorrectly?

int fileCount = 0;

using (var client = new HttpClient(handler))
{
    client.DefaultRequestHeaders.Clear();
    client.DefaultRequestHeaders.Add("Accept", "application/json;odata=verbose");
    var response = await client.GetAsync(siteUrl + "/_api/Web/GetFolderByServerRelativeUrl('XXX')/Files");
    response.EnsureSuccessStatusCode();

    json = await response.Content.ReadAsStringAsync();
    Root deserializedResults = JsonConvert.DeserializeObject<Root>(json);               

    foreach (Result result in deserializedResults.d.results)
    {
        if (result.TimeCreated > DateTime.Today.AddDays(-7))
        {
            DownloadFileViaRestAPI(siteUrl, credentials, "XXX", result.Name, "XXX");
            fileCount++;
        }                    
    }
}

Console.Write(fileCount);

SSIS Execute Process Task;

StandardOutVariable = User::FileCount

SQL Task expression;

Property: Disable 
Expression: Len(Trim(@[User::FileCount])) > 0 ? False : True

Answer 1

tl;dr; What you've described and my implementation work fine so something else is going awry in your situation.

Set up

I built out a simple SSIS package. Execute Package Task -> Script task (to dump variable values to console) -> A Sequence Container that has your disable logic on it => Another script task (to dump to console if the disable works/does not work)

I have your original variable, User::FileCount of type String and added User::HasFile of type Boolean with an expression of Len(Trim(@[User::FileCount])) > 0 to test your logic.

SO_71067359.bat

I created a batch file that will serve as your C# app. When needed, I'll remove line 2 so no output is generated. Contents are

@echo off
echo 1

Results

C:\ssisdata>SO_71067359.bat
1

EPT Download File

Execute Package Task. Working directory set to the location of my batch file. Executable is the batch script. StandardOutputVariable is User::FileCount

SCR Echo Back

This is my standard echo back script and I'll add User::FileCount and User::HasFile. When the package runs, this will dump the values to the Console output which is something I can copy and paste unlike the pretty Results tab most are familiar with

Content in case my blog goes away is

bool fireAgain = false;
string message = "{0}::{1} : {2}";
foreach (var item in Dts.Variables)
{
    Dts.Events.FireInformation(0, "SCR Echo Back", string.Format(message, item.Namespace, item.Name, item.Value), "", 0, ref fireAgain);
}

SEQC Simulate Other

This is a sequence container that has your Disable logic directly added to it. Yes, I have the variable but since you have the inversion of the results, I didn't want to sully the test.

Yes it ran

Inside the sequence container is a copy/paste of the SCR Echo Back except I renamed it to "Yes it ran" and specified the only variable is System::TaskName If the task runs, the console will print the name of the task.

Test round 1

The output of the batch script will be "1" so we expect to see the Inner task fire. Let's check the console

SSIS package "C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\SO_71067359.dtsx" starting.
Information: 0x0 at SCR Echo Back, SCR Echo Back: User::FileCount->1
Information: 0x0 at SCR Echo Back, SCR Echo Back: User::HasFiles->True
Information: 0x0 at Yes it ran, SCR Echo Back: System::TaskName->Yes it ran
SSIS package "C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\SO_71067359.dtsx" finished: Success.
The program '[2268] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).

Test Round 2

Clearing line 2 of the bat file

SSIS package "C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\SO_71067359.dtsx" starting.
Information: 0x0 at SCR Echo Back, SCR Echo Back: User::FileCount->
Information: 0x0 at SCR Echo Back, SCR Echo Back: User::HasFiles->False
SSIS package "C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\SO_71067359.dtsx" finished: Success.
The program '[75252] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).