SSIS: cant keep variable values

[doritoni]

I am using a Script Task and I want to pass a value to a variable. Here is my code:

Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Runtime.VariableDispenser

Public Class ScriptMain

Public Sub Main()
Dim strConnection As String
Dim vars As Variables

strConnection = "Data Source=vm-db02;Initial Catalog=master; Integrated Security= True"

Dim connection As New SqlClient.SqlConnection(strConnection)
connection.Open()

Dim strQuery As String
strQuery = "select PROJECT_CODE from dbo.UPLOAD_PROJECT_DATA"
Dim command As New SqlClient.SqlCommand(strQuery, connection)
Dim srReader As SqlClient.SqlDataReader
srReader = command.ExecuteReader()

Dim strTheId As String

While (srReader.Read())
strTheId = srReader.Item(0).ToString
End While

Dts.VariableDispenser.LockForWrite("User::gvProjectCode")
Dts.Variables("User::gvProjectCode").Value = strTheId

MsgBox(Dts.Variables("User::gvProjectCode").Value)

connection.Close()
Dts.TaskResult = Dts.Results.Success
End Sub

End Class

While printing the variable it has the correct value, but it does not appear in the variables window.What am i doing wrong?i want to use the specified variable in other steps but i cant because it does not have the correct value.Please Help!!!

 

[fuzzyocelot]

I'm not the greatest when it comes to SSIS, but I'll try to help. First, did you create the variable in the variable window (i.e. the place where you can create global variables)? If so, make sure the scope is correct.

Next I would check the Script Task Editor. Select "Script" and make sure your variable is in the "ReadWriteVariables" spot.

Also, I'm not the greatest at scripting, but here's an example of how I set my variables. I'm sure the way you did it is probably fine. I don't know.

Dts.Variables.Item("strQuery").Value = strQuery

I hope that helps.

 

[MDXer]

FuzzyOcelot is correct in that you have to set the value of your variable within your script task as shown. TO do this you have to Pass that variable as a ReadWriteVariable.

Also the variable window will never update and display the variable that is populated at runtime but maintains the variable you set at designtime.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck