Where does the variable get the value from

Question

Hi All,
We have an old SSIS package developed by a vendor which has a variable @[User::CurrentDivision] , which has a value 2, please see the attached screen shot. But our look up table has 3 values (1,2,3) , we are wondering how to approach this as the new division id's that are added are not showing in the data load. Please need help

In the beginning of the Package there is a scrip task editor names (SETcounty and division)
When i opened the script only county value mentioned and not division
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System  
Imports System.Data  
Imports System.Math  
Imports Microsoft.SqlServer.Dts.Runtime  
  
<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute> _  
<System.CLSCompliantAttribute(False)> _  
Partial Public Class ScriptMain  
	Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase  
  
	Enum ScriptResults  
		Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success  
		Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure  
	End Enum  
  
	' The execution engine calls this method when the task executes.  
	' To access the object model, use the Dts object. Connections, variables, events,  
	' and logging features are available as static members of the Dts class.  
	' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.  
	'   
	' To open Code and Text Editor Help, press F1.  
	' To open Object Browser, press Ctrl+Alt+J.  
  
	Public Sub Main()  
		'  
        ' Add your code here  
        Dim strDivision As String  
        Dim strCounty As String  
        Dim strFile As String  
  
        strFile = Dts.Variables("CurrentFile").Value.ToString  
        strDivision = Mid$(strFile, 4, 1)  
  
        ' Replace Variable Set with Substring when input files updated  
        'strCounty = Mid$(strFile, 5, 2)  
        strCounty = "49"  
  
        Dts.Variables("CurrentDivision").Value = strDivision  
        Dts.Variables("CurrentCounty").Value = strCounty  
		'  
		Dts.TaskResult = ScriptResults.Success  
	End Sub  
  
End Class  

Answer 1

Variables just store data. How the data gets in there is up to your SSIS package. In many cases the variable's value is set when it is created in the designer (see Variables window). However if you need it to be dynamic then you need to add a step in your SSIS package to set the variable as discussed here.

In cases where your variable(s) need to be set based upon a query that runs then you generally set up the query to run as normal (data flow task) and then you define a variable within the scope and set its value to an expression that pulls the current value from the query. There is no need for a script task for this. After that the variable will retain its value until it is set to something else or goes out of scope.

As an example a common thing to do is enumerate the rows of a query using a Foreach task. Each time through the loop you want to assign a value from the current row to a variable. To do that you'd go to the Foreach task and it has the option of defining variables. You define your variable and you set its value to be an expression that is evaluated from the current row's value (available via the Foreach) task. Then each time through the loop SSIS will update the variable to the current value as defined by the row. An example can be seen here.

For static variable values just define the variable and set its value directly.

Answer 2

Hi @bk ,

Variables store values that a SQL Server Integration Services package and its containers, tasks, and event handlers can use at run time.

The scripts in the Script task and the Script component can also use variables. The precedence constraints that sequence tasks and containers into a workflow can use variables when their constraint definitions include expressions.

More details you may refer:

integration-services-ssis-variables

variables-window

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October