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 'MsgBox(Dts.Variables("T_PackageLastRunDateTime").Value) 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).