SSIS Script Component Increment variable

Question

I am trying to do what seems simple to me, but can't manage to implement it. I want to increment a simple variable in a Data flow Task...

The variable is set in the ReadWriteVariables, there is no output nor input columns.

This is the end-goal (I'll avoid sharing the monstrosity my current code is) :

public class ScriptMain : UserComponent
{
    public override void PostExecute()
    {
        base.PostExecute();

        Variables.intDatasourceUpdated++;
    }
}

I suppose I'm missing something (very junior with C# and .Net), so any help would be appreciated.

Edit: I want to increment my "updated" or my "inserted" variables depending on the lookup : lookup printscreen. Here, it is always "updated".

My error is : error printscreen. Note that it says "at Variables.get_intDatasourceInserted()" but I never go to that branch here. So I commented the increment line in the "insert script" and it worked.

But then, when I'll have the "insert" case, as I currently have it deactivated, it won't increment.

What makes you think it isn't being incremented? Note that when your package stops executing, all variables go back to their default value - they are not preserved. There is quite likely an easier way to do what you are doing. If you are doing a lot of script inside SSIS, I suggest you're doing somehing wrong. — Nick.Mc
– Nick.Mc, Commented Dec 3, 2019 at 12:37
I always had an error (now that I think of it, I should have posted it too...). I'll edit my post with new findings. — Renaud L.C.
– Renaud L.C., Commented Dec 3, 2019 at 12:41
Is all data on the same server? Don’t use SSIS use a stored procedure. — Nick.Mc
– Nick.Mc, Commented Dec 3, 2019 at 13:57
I can't do that: Company policy. Furthermore, this would be a nightmare to maintain. — Renaud L.C.
– Renaud L.C., Commented Dec 3, 2019 at 14:02
IMHO it’s easier to maintain in a stored proc. It’s certainly faster. But I don’t know the full story. I’ve certainly seen my fair share of slow complicated SSIS packages full of script created by programmers that don’t know how to use databases! — Nick.Mc
– Nick.Mc, Commented Dec 3, 2019 at 14:05

vhoang · Accepted Answer · 2019-12-03 13:52:48Z

1

"The collection of variables locked for read and write access is not available outside of PostExecute".

You are posting your "Update" snippet, the error is saying you are trying to update intDatasourceInserted which likely in your "Insert"s PreExecute(). Which isn't allowed.

In either case you'll still have an issue, since each task there execute simultaneously, waiting for pipeline data, and variables don't work well between tasks inside one data flow, you'll probably need to mangle the data itself as it flows or access the altered variable outside the data flow in the control flow.

answered Dec 3, 2019 at 13:52

vhoang

1,4891 gold badge10 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Renaud L.C. Over a year ago

Well, that's what I guessed, and also why I commented the line in the other script, on a hunch. But it makes little sense to me... I imagine it's linked to variable contexts or concurrency, or something like that? The solution I found was to use a rowcount and another variable with an expression to add my rowcounts. It requires 20 variables... For cleanliness's sake I'd like to redo it with the script component as I find it way better. I'll keep coming to this post in hope someone has the solution I want. Thanks for the help.

vhoang Over a year ago

Context and concurrency is a good way to put it. I set writable variables (and sometimes larger object variables) in data flow often and predictable only by not letting two task edit the same variable and with the sole intent to use it after the data flow is done in a downstream control task (any task after exiting the dataflow including other dataflows).

CyberLord · Accepted Answer · 2023-02-21 15:31:19Z

0

Usually this can be done, by defining a new C# variable. Set the value of the SSIS-variable to the new variable and then back again.

// Declare user-defined variable and increase the value by 1
int variableValue = Convert.ToInt32(Dts.Variables["myVariable"].Value);

variableValue++;

// Write the new value back into the variable
Dts.Variables["myVariable"].Value = variableValue;

edited Feb 21, 2023 at 15:31

answered Feb 21, 2023 at 15:30

CyberLord

191 silver badge4 bronze badges

Comments

KeithL · Accepted Answer · 2019-12-03 18:31:43Z

-1

You need to decalre the variable outside of row processing.

public int counter = 0;

public void main()
{
     counter++;
}

And at the end set the variable to counter.

post execute...

Variables.Counter = counter;

answered Dec 3, 2019 at 18:31

KeithL

5,6193 gold badges23 silver badges30 bronze badges

Collectives™ on Stack Overflow

SSIS Script Component Increment variable

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related