{"id":292,"date":"2012-07-02T12:53:11","date_gmt":"2012-07-02T19:53:11","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=292"},"modified":"2012-07-02T12:56:26","modified_gmt":"2012-07-02T19:56:26","slug":"ssis-compare-variables-and-change-one-with-a-script-task","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2012\/07\/ssis-compare-variables-and-change-one-with-a-script-task\/","title":{"rendered":"SSIS Compare Variables And Change One With A Script Task"},"content":{"rendered":"

This article falls into the half baked posts category.\u00a0 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.\u00a0 Doh!\u00a0 Still a useful bit of code though so I'm posting it here for posterity.<\/p>\n

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.\u00a0 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.\u00a0 As is usually the case it was a very simple solution:<\/p>\n

' Microsoft SQL Server Integration Services Script Task\r\n' Write scripts using Microsoft Visual Basic 2008.\r\n' The ScriptMain is the entry point class of the script.\r\n\r\nImports System\r\nImports System.Data\r\nImports System.Math\r\nImports Microsoft.SqlServer.Dts.Runtime\r\n\r\n<System.AddIn.AddIn(\"ScriptMain\", Version:=\"1.0\", Publisher:=\"\", Description:=\"\")> _\r\n<System.CLSCompliantAttribute(False)> _\r\nPartial Public Class ScriptMain\r\n\u00a0\u00a0 \u00a0Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase\r\n\r\n\u00a0\u00a0 \u00a0Enum ScriptResults\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure\r\n\u00a0\u00a0 \u00a0End Enum\r\n\r\n\u00a0\u00a0\u00a0 Public Sub Main()\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If Dts.Variables(\"T_PackageLastRunDateTime\").Value < Dts.Variables(\"MinCapitalFlowDate\").Value Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dts.Variables(\"T_PackageLastRunDateTime\").Value = Dts.Variables(\"MinCapitalFlowDate\").Value\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'MsgBox(Dts.Variables(\"T_PackageLastRunDateTime\").Value)\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dts.TaskResult = ScriptResults.Success\r\n\u00a0\u00a0\u00a0 End Sub\r\n\r\nEnd Class<\/pre>\n
 <\/p>\n
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.<\/p>\n
\"\"<\/p>\n
That's it.\u00a0 A simple bit of VB to allow you to change a variable in your SSIS package (without hitting the database).<\/p>\n","protected":false},"excerpt":{"rendered":"
This article falls into the half baked posts category.\u00a0 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.\u00a0 Doh!\u00a0 Still a useful bit of code though so I'm posting […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,47,46],"tags":[20,10,9,12,11,49,48,67,68],"class_list":["post-292","post","type-post","status-publish","format-standard","hentry","category-businss-intelligence","category-etl","category-sql-server-integration-services-ssis","tag-business-intelligence","tag-ms-sql","tag-sql-server","tag-sql-server-2005","tag-sql-server-2008","tag-sql-server-integration-services","tag-ssis","tag-vb","tag-visual-basic"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/292","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=292"}],"version-history":[{"count":3,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/292\/revisions"}],"predecessor-version":[{"id":295,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/292\/revisions\/295"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=292"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=292"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=292"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}