Problem Altering SSIS Package Variable Using VB.NET Script Task

Question

Problem Altering SSIS Package Variable Using VB.NET Script Task

I H

Mr or Mrs. 500

Points: 518
More actions
November 19, 2009 at 6:12 am

#217763

Hi,
I’m having trouble altering the value of a package variable using the script task of SSIS 2008.
In the variables window (right click package design window – variables) two user variables are listed, one called MODIFYSCRIPT (string), the other is called TEXTFILE also a string. Both have a scope value of custom_import_with_script, this is the name of the package so I’m assuming that these variables have a package wide scope.
I’ve dragged a script task onto the control of flow area for the package, set it’s language to VB.NET and assigned the variable MODIFYSCRIPT to the ReadOnlyVariables section of the task (has a syntax of User::MODIFYSCRIPT), the variable TEXTFILE has been assigned to the ReadWriteVariables in the task.
The variable of MODIFYSCRIPT currently has a value of “test” whilst the value of TEXTFILE has a value of a single space. These I am viewing in the package explorer.
The code I’ve written so far is as follows:
Public Sub Main()
Dim StrModifyScript As String
StrModifyScript = Dts.Variables("User::MODIFYSCRIPT").ToString
Dts.Variables("User::TEXTFILE").Value = StrModifyScript
Dts.TaskResult = ScriptResults.Success
End Sub
I execute the task, it runs, turns green but when I check the value of the variables in package explorer the TEXTFILE variable still has a value of “ “ as opposed to having been assigned the value from MODIFYSCRIPT.
Could anyone let me know what I’m missing please as I’ve battling with this for over half a day now!
Thanks in advance!

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

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

William J. Fellows SSC Journeyman Points: 88 More actions · Answer 1

Assuming I'm understanding correctly, you have two variables modifyscript and texfile. Assuming initial values of C:\Mod.txt and C:\text.txt respectively. After running the package and presumably changing the value of textfile, it's not showing as such in the variable window?

The original values assigned to those variables will always read the same---they're design-time values. Instead, you will want to examine the value in the local window as that will reflect the run-time values. I don't have Windows running at the moment or I'd tell you the exact names of the display windows.

@billinkc

I H Mr or Mrs. 500 Points: 518 More actions · Answer 2

William,

Thanks for the reply and advice, as far as I can tell after a bit of Googling the way to watch a variable is to put a break point in the script, start debugging and then when the breakpoint is reached and the code window opens, you can right-click on a variable and select add-watch.

The string variable TEXTFILE still seems to not be getting the vale I'd expect, it in fact gets the value:

"Microsoft.SqlServer.Dts.Runtime.Variable" {String}

This is in fact the value which goes to the variable StrModifyScript when it's assigned the value from the MODIFYSCRIPT package variable; though this could be a reference to the correct value and me just not understanding the technology.

Either way think I've found a way to achieve what I'm after using expressions without having to battle with the script task to achieve something so simple!

Cheers,

Iain

Todasdasdfd asfasf Ten Centuries Points: 1340 More actions · Answer 3

You are going to kick yourself... change this:

StrModifyScript = Dts.Variables("User::MODIFYSCRIPT").ToString

to this:

StrModifyScript = Dts.Variables("User::MODIFYSCRIPT").Value.ToString

Todd McDermid - SQL Server MVP, MCSD.Net, MCTS (SQL08BI)
Find great SSIS extensions - most are open source - at SSIS Community Tasks and Components[/url]
Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.

I H Mr or Mrs. 500 Points: 518 More actions · Answer 4

Doh!

That got it, so often the simplest things which are overlooked.

Thanks very much!

Iain