Wednesday 6 February 2013

Value of variable during runtime

Case
I want to know the value of a variable during runtime of an SSIS package.
Value of variable between two tasks


















Solution
You can either use a breakpoint or a Script Task to find out the value of a variable.

A) Script Task

A1) Add Script Task
Add a Script Task between the two tasks and select the FilePath variable as readdonly variable.
Script Task - Readonly variable



















A2) The script
There are three options that you could choose. Pick one.
//C# code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_d730d75a40304a6bb675bc184c2aa717
{
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 {

  public void Main()
  {
        // Choose one of these methods:

        // 1) Fire event and watch the execution result tab
        bool fireAgain = true;
        Dts.Events.FireInformation(-1, "Value of FilePath:", Dts.Variables["User::FilePath"].Value.ToString(), string.Empty, -1, ref fireAgain);


        // 2) Use the .Net framework trace log and see Debug View: http://technet.microsoft.com/en-us/sysinternals/bb896647.aspx
        System.Diagnostics.Trace.WriteLine("Value of FilePath: " + Dts.Variables["User::FilePath"].Value.ToString());


        // 3) Good old messagebox and click to continue
        System.Windows.Forms.MessageBox.Show("Value of FilePath: " + Dts.Variables["User::FilePath"].Value.ToString());


        Dts.TaskResult = (int)ScriptResults.Success;
  }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
 }
}

or VB.Net

'VB.Net code
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

    Public Sub Main()
        ' Choose one of these methods:

        ' 1) Fire event and watch the execution result tab
        Dim fireAgain As Boolean = True
        Dts.Events.FireInformation(-1, "Value of FilePath:", Dts.Variables("User::FilePath").Value.ToString(), String.Empty, -1, fireAgain)


        ' 2) Use the .Net framework trace log and see Debug View: http://technet.microsoft.com/en-us/sysinternals/bb896647.aspx
        System.Diagnostics.Trace.WriteLine("Value of FilePath: " + Dts.Variables("User::FilePath").Value.ToString())


        ' 3) Good old messagebox and click to continue
        System.Windows.Forms.MessageBox.Show("Value of FilePath: " + Dts.Variables("User::FilePath").Value.ToString())


        Dts.TaskResult = ScriptResults.Success
    End Sub

#Region "ScriptResults declaration"
    'This enum provides a convenient shorthand within the scope of this class for setting the
    'result of the script.

    'This code was generated automatically.
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

#End Region

End Class

A3) The Result
Now run the package and watch the result.
Oops, no flat file but an Excel file























B) Breakpoints

B1) Breakpoint
Right click the Data Flow Task, Choose "Edit Breakpoints..." and then select the OnPreExecute event. This is the event right before starting the Data Flow Task.
Add breakpoint























B2) Excute package
Now execute the package and wait for it to hit the breakpoint.
Run package and wait for breakpoint

















B3) Locals
Wait for the package to hit the breakpoint. Then go to the Debug menu and click Windows and then Locals (Ctrl+Alt+V,L). This wil open a new window.
Locals
















B4) The result
Now downdrill the variables in the Locals window and search for your variable and its value.
Oops, no flat file but an Excel file

























This last method is probably a lot easier.

2 comments:

  1. how can we archive samething using PACKAGEINFO object ?

    ReplyDelete
    Replies
    1. You have to use a Script Task to transfer properties of packageinfo to variables.

      But most properties are already available in System Variables.

      Delete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...