SSIS Compare Variables And Change One With A Script Task

This article falls into the half baked posts category.  Once I figured out how to compare two variables and then overwrite one with a script task in SQL Server Integration Services, I also realized I did not in fact need to perform the task.  Doh!  Still a useful bit of code though so I’m posting it here for posterity.

The goal was to compare two variables, both dates in my case, and if one date was greater than the other, overwrite the earlier date with the later date.  Seemed simple enough but being that I’m a database guy through and through and that I fear all languages other than SQL I was pretty hesitant to use the Script Task to solve my problem.  As is usually the case it was a very simple solution:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

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

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<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

    Public Sub Main()

        If Dts.Variables("T_PackageLastRunDateTime").Value < Dts.Variables("MinCapitalFlowDate").Value Then
            Dts.Variables("T_PackageLastRunDateTime").Value = Dts.Variables("MinCapitalFlowDate").Value
        End If


        Dts.TaskResult = ScriptResults.Success
    End Sub

End Class


Here’s a screenshot in case you can’t live without color coding (I can’t) or if the formatting doesn’t hold up when I post this.

That’s it.  A simple bit of VB to allow you to change a variable in your SSIS package (without hitting the database).

Leave a Reply

Your email address will not be published.