User defined variable value is not being updated in the Script Task

Question

User defined variable value is not being updated in the Script Task

rtp

SSC-Addicted

Points: 456
More actions
December 28, 2016 at 9:55 am

#333036

Using SSIS 2008, I was able to make my variable global. My Script task has a Message.Show with the variable. Example, The number of rows are (then the variable results). However, my variable here is zero and it should read 101. When I execute my package, in the Data flow tab, I see the "101 rows" between Excel Source and
the Row Count. But the 101 is not making it to the Script task, Simple code:
MessageBox.Show("There are " + Dts.Variables["NumberRows"].Value + " rows");
NumberRows is my variable.
Also, I am getting the following warning for each of my column headings from Excel workbook
and how do I correct it:
[SSIS.Pipeline] Warning: The output column "Id" (41) on output "Excel Source Output" (9) and component "Excel Source" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
On the Data Flow Tab, Excel Source connected to the RowCount. Double click on RowCount and the Editor for
Row Count appears and at bottom for Variable Name, hit the ellipse button and I find the user defined variable
I created, NumberRows
On the Control Flow Tab(I did this first), Data Flow Task. I tested what I have to this point and everything
worked out because the Row Count returned 101 rows.
Thanks,
rtp

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

1. How are you populating your variable? If the variable is not populated correctly, it will always show 0 (or the initial value that you have set).

2. The warnings are shown because SSIS doesn't detects that you're using the columns. Just uncheck them in the source if you're truly not using them. Less columns means less data to be loaded into memory, which would result in a faster execution.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

rtp SSC-Addicted Points: 456 More actions · Answer 2

1. The Row Count Transformation should populate my user defined variable NumberRows. Before it

gets to the Script task, gets the records from the Excel workbook(101 records) and the Row Count

Transformation populates the variable. I set the Row Count Transformation to my the variable NumbersRows.

I get the correct row count when I execute the package in the Data Flow Tab, but it becomes 0 when the

Script Task runs. Note - I did change my variable default to 5 and then ran package but the Script

Task changes it to 0 when it display my Message box.

This should work maybe I am missing something else where in my setup?

Thanks.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Two ideas come to my mind.

1. You have 2 variables and you're populating one and showing another one (SSIS can cause these confusions).

2. Your variable wasn't correctly set in the Row Count transformation (it happened to me with a different object).

I hope this helps, but I'm not sure what else could be happening.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

rtp SSC-Addicted Points: 456 More actions · Answer 4

Luis,

You were correct. I was not populating the variable. In SSIS 2008 r2 release, the Expression ... is

in the property window for that variable. You have to go to the property window, Expression ellipse button.

This step I was missing. I have to drag my user defined variable NumberRows to the evaluation

window and click OK. This is were it gets set.

Thanks for your help.