For example, if the SSIS package contains a Send Mail task, you can create an expression for the Subject property and for the MessageSource property. You can use the Subject property expression to dynamically update the subject of an e-mail message. How to use Variables in Script Task in SSIS Package by using VB NET Scripting Language Public Sub Main Dim filename As String Dim SourceFolder As String 'Set the value of local variables in SSIS Script from SSIS Variables filename = Dts.Variables('User:.
- Use SSIS Variables and Parameters in a Script Task In SSIS 2012 there is a new task to set the value of variables: Expression Task. For previous versions of SSIS or for more complex constructions you need the Script Task. Here is an example on how to use variables and parameters in a Script Task.
- This task allows you to read variable and log them to Execution Log, Message Box or File. This is very convenient way for debugging purpose during development of runtime of production Jobs. SSIS Logging Task support DateTime formatting, Dumping object resultset (From ExecuteSQL resultset output) as HTML table.
- The biggest frustration for new SSIS developers writing VB script is dealing the error message. The resolution is simple. Either the variable name listed in the Script Task Editor or the variable name in the script doesn’t match, so one must be changed to match the other.
Some time in order to debug variables in SSIS you want to see what’s the values that the variable is holding or you want to check the Result set which is stored in the variable.
If you want to show or check the variable value or want to show the value inside a Message box than it can be done through Script task.
Below I will create a test table and will insert a row into the table and will display the ColB value i.e. Hello How are You in SSIS using SQL Execute Task and Script task.
Table script
insertintotblvalues('Test Message','Hello How are You')
Now we will open SSIS and will drop a SQL Execute task and a Script task in the Package
Follow the below steps
Ssis Script Task
Double click on SQL execute task
Create a data connection to the database where above table created
In SQL Statement add -select*fromtbl
Select Resultset as Single Row as below Screenshot
Add a variable with message and ResultName make it as 1 (Index of your column) Since we will show ColB value in the Message box as below screenshot
Next connect the SQL Execute task to script task
Now, double click on Script task
Ssis Script Task Message Box Variable Error
Select Read only variable as User Message which we created above as below screenshot
Click on edit script and add Message box (MessageBox.Show(Dts.Variables['Message'].Value.ToString());) inside Main Function as below Screenshot
Run your Package this will display your variable value in the Message box as below.
Ssis Script Task Msgbox Variables
Please write in Comments If you'r stuck with any step or need any help.