Unpack Decimal (Comp-3) Using SQL Function

Finally after creating a custom component for ssis I came into thinking how to do the same using a sql function after research and plenty of Google I found some similar cases and with some ingenuity modify the code to my needs after all if the ops dba doesn't like the deployment of a custom dll then the alternative is a sql function So here it is folks... Ah you still need to load the file into an table using varbinary (comp-3) format and as an added bonus submit the scale value, the next step is to create a few functions that do the opposite convert a numeric value into comp-3 format, but that is just another history.
Cheers.

CREATE FUNCTION [dbo].[udf_UnpackDecimal] (
@InputStr varbinary(50) = NULL
,@Scalar int = 0

)

RETURNS varchar(2000)
AS
BEGIN
DECLARE @UnpkgStr varchar(2000)
DECLARE @pos int
DECLARE @length int
DECLARE @temp int
DECLARE @nibble1 int
DECLARE @nibble2 int
DECLARE @hexstring char(16)
DECLARE @DecPos int

SET @UnpkgStr = ''
SET @pos = 1
SET @length = DATALENGTH(@InputStr)
SET @hexstring = '0123456789ABCDEF'

WHILE (@pos <= @length)
BEGIN
SET @temp = CONVERT(int, SUBSTRING(@InputStr, @pos, 1))
SET @nibble1 = FLOOR(@temp / 16)
SET @nibble2 = @temp - (@nibble1 * 16)

SET @UnpkgStr = @UnpkgStr + SUBSTRING(@hexstring, @nibble1 + 1, 1)

IF @pos < @length 
SET @UnpkgStr = @UnpkgStr +SUBSTRING(@hexstring, @nibble2 + 1, 1)
ELSE
IF SUBSTRING(@hexstring, @nibble2 + 1, 1) = 'D'
SET @UnpkgStr = '-' + @UnpkgStr 

SET @pos = @pos + 1
END

set @UnpkgStr = cast (cast(@UnpkgStr as decimal)as varchar(200))

Set @DecPos = (DATALENGTH(@UnpkgStr)+1)-@Scalar


RETURN isnull (Stuff(@UnpkgStr,@DecPos,0,'.'),'0')

END

SQLSaturday Charleston SCRecap and Oracle Issues

I have been having issues installing Oracle 11g R2, actually Oracle install and works just find the main issue are the OLEDB drivers when trying to connect using SSIS.  First I get an error that it can't find the oracle home, then that the variable path is not correct.. and the list goes on.
I have check most recommendations from Oracle forums and Microsoft and this issue is taking longer than expected. So let see if I can finally set that up straight, I guess installing Oracle 12 c and then uninstalling was a big mistake.
Hopefully tomorrow I'm able to figured that out.
Finally I did have a great time on my first visit to the city of Charleston, SC. I visited the old market and most of the down town area, SQLSaturday was great and the hospitality of the host was amazing. THANK YOU, hope to see you guys again next year.Now back into the Oracle Fix

1. Install Oracle Database 11gR2 64bit
2. Install Both Oracle Clients the 64 and 32 bits
3. Uninstall and re-install the Microsoft 64 bits attunity drivers 
4. Copy the TSNAME.ORA File to the 32 bits Client Network\Admin folder.

SQLSaturday 227 Count Down

Only a few day before SQLSaturday #227 in Charleston,SC
I have check the code, improved the presentation and now is time to find a hotel to spend a few days and return back to Jacksonville. It doesn't matter how many times I go to presentations as a speaker, I always get those last minutes worries and fears and the end is just a presentation, hopefully people appreciate what you done and most than all the satisfaction of some day see the fruits of my endeavors.
Cheers.

Upgrading Old Code Generator

I'm now on one of the main components of the previous code and I'm kind of enjoying the pain on having to convert this code from vb.net to C#, however, I don't fill like modifying the other that a before and after effect. So here is the VB.NET code implemented on SQLServer 2005 SSIS.  how far I'm on the translation let said I just started, so once I check and test the whole process I will post the whole code and package.
Cheers.

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

Imports System
Imports System.Data
Imports System.Math
Imports System.Text
Imports System.Xml
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask



<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

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
' 
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
    Dim var As Variables

Public Sub Main()
        'Package Variable Declaration

        'Read
        Dts.VariableDispenser.LockForRead("User::str_PkColumn")
        Dts.VariableDispenser.LockForRead("User::str_BatchId")
        Dts.VariableDispenser.LockForRead("User::str_SourceArchiveTable")
        Dts.VariableDispenser.LockForRead("User::bit_IsRetention")
        Dts.VariableDispenser.LockForRead("User::int_ArchiveExportId")
        Dts.VariableDispenser.LockForRead("User::int_FastLoadBatchSize")
        Dts.VariableDispenser.LockForRead("User::int_FastLoadCommit")
        Dts.VariableDispenser.LockForRead("User::bit_IsFastLoad")
        Dts.VariableDispenser.LockForRead("User::bit_IsSqlCmd")
        Dts.VariableDispenser.LockForRead("User::bit_IsSqlOverride")
        Dts.VariableDispenser.LockForRead("User::bit_IsTransferData")
        Dts.VariableDispenser.LockForRead("User::str_AutoSqlCmd")
        Dts.VariableDispenser.LockForRead("User::str_DestinationDatabase")
        Dts.VariableDispenser.LockForRead("User::str_DestinationSchema")
        Dts.VariableDispenser.LockForRead("User::str_DestinationServer")
        Dts.VariableDispenser.LockForRead("User::str_DestinationTable")
        Dts.VariableDispenser.LockForRead("User::str_SourceDatabase")
        Dts.VariableDispenser.LockForRead("User::str_SourceSchema")
        Dts.VariableDispenser.LockForRead("User::str_SourceServer")
        Dts.VariableDispenser.LockForRead("User::str_SourceTable")
        Dts.VariableDispenser.LockForRead("User::str_SqlCmd")
        Dts.VariableDispenser.LockForRead("User::str_SqlFilter")

        'Write

        Dts.VariableDispenser.LockForWrite("User::int_IsPkgFail")
        Dts.VariableDispenser.LockForWrite("User::str_XmlDoc")
        Dts.VariableDispenser.LockForWrite("User::int_DestCurrCnt")
        Dts.VariableDispenser.LockForWrite("User::int_DestPrevCnt")
        Dts.VariableDispenser.LockForWrite("User::int_SourceCnt")

        Dts.VariableDispenser.GetVariables(var)
        'local variables
        Dim pkg As Microsoft.SqlServer.Dts.Runtime.Package = New Microsoft.SqlServer.Dts.Runtime.Package
        Dim DestPrevCnt As Microsoft.SqlServer.Dts.Runtime.Variable = pkg.Variables.Add("int_DestPrevCnt", False, "User", 0)
        Dim DestCurrCnt As Microsoft.SqlServer.Dts.Runtime.Variable = pkg.Variables.Add("int_DestCurrCnt", False, "User", 0)
        Dim SourceCnt As Microsoft.SqlServer.Dts.Runtime.Variable = pkg.Variables.Add("int_SourceCnt", False, "User", 0)
        Dim BatchId As Microsoft.SqlServer.Dts.Runtime.Variable = pkg.Variables.Add("str_BatchId", False, "User", var("User::str_BatchId").Value.ToString())
        Dim PkColumn As String = var("User::str_PkColumn").Value.ToString()

        pkg.Variables.Add("int_ArchiveExportId", False, "User", CType(var("User::int_ArchiveExportId").Value, Int32))



        Dim ArchiveExportId As Int32
        Dim FastLoadMaxCommitSize As Int32
        Dim RowsPerBatch As String
        Dim DestCnt As Microsoft.SqlServer.Dts.Runtime.TaskHost
        Dim DestCurr As Microsoft.SqlServer.Dts.Runtime.TaskHost
        Dim DataFlowTaskHost As Microsoft.SqlServer.Dts.Runtime.TaskHost
        Dim SourceComponent As IDTSComponentMetaData100
        Dim SourceRowCount As IDTSComponentMetaData100
        Dim DestinationComponent As IDTSComponentMetaData100
        Dim MultiCastComponent As IDTSComponentMetaData100

        Dim SourceInstance As CManagedComponentWrapper
        Dim rowInstance As CManagedComponentWrapper
        Dim DestinationInstance As CManagedComponentWrapper
        Dim MultiCastInstace As CManagedComponentWrapper


        Dim BathIdColumn As IDTSOutputColumn100 ' Bathid column'
        Dim TableNameColumn As IDTSOutputColumn100 'Table Name'
        Dim PkIdColumn As IDTSOutputColumn100 'Primary Key Column '
        Dim ArchiveExportIdColumn As IDTSOutputColumn100
        Dim ArchiveExportIdProperty As IDTSCustomProperty100
        Dim BathIdProperty As IDTSCustomProperty100
        Dim TableNameColumnProperty As IDTSCustomProperty100
        Dim PkIdColumnProperty As IDTSCustomProperty100
        Dim PkIdInputColumn As IDTSInputColumn100



        Dim DestinationVirtualInput As IDTSVirtualInput100
        Dim DestinationVirtualInputColumns As IDTSVirtualInputColumnCollection100

        Dim DestinationInput As IDTSInput100

        Dim SourceCntPath As IDTSPath100
        Dim SourceMultiCastPath As IDTSPath100
        Dim DeriveColumnMulticastPath As IDTSPath100
        Dim DeriveColumnArchiveIDPath As IDTSPath100
        Dim DestinationPath As IDTSPath100
        Dim DataFlowTask As MainPipe
        Dim DestcurrRB As IDTSResultBinding
        Dim DestCurr1 As ExecuteSQLTask
        Dim DestCnt1 As ExecuteSQLTask
        Dim DataFlow As Executable
        Dim DestRB As IDTSResultBinding
        Dim SqlCounts As String = "Select count(1) from "
        Dim SqlCmdForId As String = "Select " + PkColumn + " From "
        Dim Failure As Int32 = -999



        Dim PkgLogging As LogProvider
        Dim seg As Executable
        Dim eDestCnt As Executable
        Dim eDestCurr As Executable
        Dim intDestPrev As Int32
        Dim intDestCurrCnt As Int32
        Dim intSourceCnt As Int32



        Dim seg1 As Microsoft.SqlServer.Dts.Runtime.Sequence
        Dim seg2 As Microsoft.SqlServer.Dts.Runtime.Sequence
        Dim seg3 As Microsoft.SqlServer.Dts.Runtime.Sequence
        Dim seg4 As Microsoft.SqlServer.Dts.Runtime.Sequence




        'Connection Information
        Dim SourceConStr As String = Dts.Connections("Source").ConnectionString
        Dim DestinationConStr As String = Dts.Connections("Destination").ConnectionString
        Dim HostConStr As String = Dts.Connections("Host").ConnectionString
        Dim MsdbConStr As String = Dts.Connections("msdb").ConnectionString




        Dim SourceSqlCmd As String = var("User::str_SqlCmd").Value.ToString
        Dim AutoSqlCmd As String = var("User::str_AutoSqlCmd").Value.ToString
        Dim ExecuteSqlCmd As String
        Dim SourceSqlFilter As String = var("User::str_SqlFilter").Value.ToString

        Dim PackageName As String = "ArchiveExportv2005000_"


        Dim IsFastLoad As New Boolean
        Dim IsOverride As New Boolean
        Dim IsQuery As New Boolean
        Dim IsTransferData As New Boolean
        Dim IsRetention As New Boolean


        'Source Variables
        Dim SourceSchema As String = var("User::str_SourceSchema").Value.ToString()
        Dim SourceTable As String = var("User::str_SourceTable").Value.ToString()
        Dim SourceSchTable As String = "[" + var("User::str_SourceDatabase").Value.ToString() + "]." + "[" + SourceSchema + "].[" + SourceTable + "]"
        Dim SourceServer As String = "[" + var("User::str_SourceServer").Value.ToString() + "]"

        Dim TableName As Microsoft.SqlServer.Dts.Runtime.Variable = pkg.Variables.Add("str_TableName", False, "User", SourceServer.ToString() + "." + SourceSchTable.ToString())
        Dim pkgServerName As Microsoft.SqlServer.Dts.Runtime.Variable = pkg.Variables.Add("str_SourceServerName", False, "User", SourceServer.ToString())
        'Destination Variables
        Dim DestinationSchema As String = var("User::str_DestinationSchema").Value.ToString()
        Dim DestinationTable As String = var("User::str_DestinationTable").Value.ToString()
        Dim SourceArchive As String = var("User::str_SourceArchiveTable").Value.ToString()
        Dim DestinationSchTable As String = "[" + DestinationSchema + "].[" + DestinationTable + "]"

        SqlCmdForId = SqlCmdForId + DestinationSchTable + " With (nolock)"


        'Variable Initialization
        FastLoadMaxCommitSize = CType(var("User::int_FastLoadCommit").Value, Int32)
        ArchiveExportId = CType(var("User::int_ArchiveExportId").Value, Int32)
        RowsPerBatch = var("User::int_FastLoadBatchSize").Value.ToString()
        pkg.Name = "Ssis_v2005_ArchiveExportId_" + CType(ArchiveExportId, String)

        'pkg.Variables.Add("int_PkId", False, "User", -1)
        'pkg.Variables.Add("int_GroupId", False, "User", -1)
        pkg.Variables.Add("extract_date", False, "User", DateTime.Now)

        IsFastLoad = CType(var("User::bit_IsFastLoad").Value, Boolean)
        IsOverride = CType(var("User::bit_IsSqlOverride").Value, Boolean)
        IsQuery = CType(var("User::bit_IsSqlCmd").Value, Boolean)
        IsTransferData = CType(var("User::bit_IsTransferData").Value, Boolean)
        IsRetention = CType(var("User::bit_IsRetention").Value, Boolean)

        ' Add Connections
        Dim SourceConMgr As ConnectionManager = CreateOLEDBConnection(pkg, "Source", SourceConStr)
        Dim DestinationConMgr As ConnectionManager = CreateOLEDBConnection(pkg, "Destination", DestinationConStr)
        Dim msdb As ConnectionManager = CreateOLEDBConnection(pkg, "msdb", MsdbConStr)
        Dim host As ConnectionManager = CreateOLEDBConnection(pkg, "host", HostConStr)

        ' Add Logging
        PkgLogging = pkg.LogProviders.Add("DTS.LogProviderSQLServer.1")
        PkgLogging.ConfigString = msdb.Name
        PkgLogging.Description = "Packing logging Using Host msdb"
        pkg.LoggingOptions.SelectedLogProviders.Add(PkgLogging)
        pkg.LoggingOptions.EventFilterKind = Microsoft.SqlServer.Dts.Runtime.DTSEventFilterKind.Inclusion
        pkg.LoggingOptions.EventFilter = New String() {"OnPreExecute", "OnPostExecute", "OnError"}
        pkg.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Disabled

        'Set Isolation Level
        pkg.IsolationLevel = IsolationLevel.ReadUncommitted

        'Set Sequences
        seg = pkg.Executables.Add("STOCK:Sequence")
        seg1 = CType(seg, Microsoft.SqlServer.Dts.Runtime.Sequence)
        seg2 = CType(seg, Microsoft.SqlServer.Dts.Runtime.Sequence)
        seg3 = CType(seg, Microsoft.SqlServer.Dts.Runtime.Sequence)
        seg4 = CType(seg, Microsoft.SqlServer.Dts.Runtime.Sequence)



        seg1.Name = "ArchiveTable_Sequence1"
        seg2.Name = "ArchiveTable_Sequence2"
        seg3.Name = "ArchiveId_Sequence"


        Try



            'Set Sql Task
            'Destination Previoscount 
            eDestCnt = seg1.Executables.Add("STOCK:SQLTASK")
            DestCnt = CType(eDestCnt, Microsoft.SqlServer.Dts.Runtime.TaskHost)
            DestCnt.FailPackageOnFailure = True
            DestCnt.IsolationLevel = IsolationLevel.ReadCommitted
            DestCnt.Name = "DestinationPrevious"
            DestCnt.Properties("Connection").SetValue(DestCnt, DestinationConMgr.ID)
            DestCnt.Properties("SqlStatementSource").SetValue(DestCnt, SqlCounts + " " + DestinationSchTable)
            DestCnt.Properties("BypassPrepare").SetValue(DestCnt, False)
            DestCnt.SetExpression("ResultSetType", "2") '2 = single row
            DestCnt.ExecValueVariable = DestPrevCnt
            DestCnt1 = CType(DestCnt.InnerObject, ExecuteSQLTask)
            DestRB = DestCnt1.ResultSetBindings.Add()
            DestRB.DtsVariableName = "User::int_DestPrevCnt"

            'Destination CurrentCount
            eDestCurr = seg2.Executables.Add("STOCK:SQLTASK")
            DestCurr = CType(eDestCurr, Microsoft.SqlServer.Dts.Runtime.TaskHost)
            DestCurr.FailPackageOnFailure = True
            DestCurr.IsolationLevel = IsolationLevel.ReadUncommitted
            DestCurr.Name = "DestinationCurret"
            DestCurr.Properties("Connection").SetValue(DestCurr, DestinationConMgr.ID)
            DestCurr.Properties("SqlStatementSource").SetValue(DestCurr, SqlCounts + " " + DestinationSchTable)
            DestCurr.Properties("BypassPrepare").SetValue(DestCurr, False)
            DestCurr.SetExpression("ResultSetType", "2") '2 = single row
            DestCnt.ExecValueVariable = DestCurrCnt
            DestCurr1 = CType(DestCurr.InnerObject, ExecuteSQLTask)
            DestcurrRB = DestCurr1.ResultSetBindings.Add()
            DestcurrRB.DtsVariableName = "User::int_DestCurrCnt"


            'Set Execution Query

            If IsQuery = True And IsRetention = True Then
                ExecuteSqlCmd = SourceSqlCmd
            Else
                ExecuteSqlCmd = AutoSqlCmd
            End If


            'DataFlow task
            DataFlow = seg3.Executables.Add("STOCK:PipelineTask")
            DataFlowTaskHost = CType(DataFlow, Microsoft.SqlServer.Dts.Runtime.TaskHost)
            DataFlowTaskHost.Name = "ArchiveExportDataFlow"
            DataFlowTask = CType(DataFlowTaskHost.InnerObject, MainPipe)



            'Join containers
            Dim DestToWf As Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint = seg1.PrecedenceConstraints.Add(eDestCnt, DataFlow)
            DestToWf.Value = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
            Dim WfToDest As Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint = seg2.PrecedenceConstraints.Add(DataFlow, eDestCurr)
            WfToDest.Value = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success


            'Add Source Component
            SourceComponent = DataFlowTask.ComponentMetaDataCollection.[New]
            SourceComponent.Name = "SourceTable"
            SourceComponent.ComponentClassID = "DTSAdapter.OleDbSource.1"
            'Get Source instance 
            SourceInstance = SourceComponent.Instantiate
            SourceInstance.ProvideComponentProperties()
            'Set Source Connection
            SourceComponent.RuntimeConnectionCollection(0).ConnectionManagerID = SourceConMgr.ID
            SourceComponent.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(SourceConMgr)



            If IsQuery = True Then
                'Use Query for source transfer
                SourceInstance.SetComponentProperty("AccessMode", 2) '2 - SQL Command
                SourceInstance.SetComponentProperty("SqlCommand", ExecuteSqlCmd)

            Else
                'Use straight table transfer
                SourceInstance.SetComponentProperty("AccessMode", 0) '0 TableOrView

            End If
            SourceInstance.SetComponentProperty("OpenRowset", SourceSchTable)

            'Reinitialize the metadata, Refresh Columns
            SourceInstance.AcquireConnections(Nothing)
            SourceInstance.ReinitializeMetaData()
            SourceInstance.ReleaseConnections()


            'Add Multicast 
            MultiCastComponent = DataFlowTask.ComponentMetaDataCollection.[New]
            MultiCastComponent.ComponentClassID = "DTSTransform.Multicast.1"
            'Get Mulicast Instance
            MultiCastInstace = MultiCastComponent.Instantiate
            MultiCastInstace.ProvideComponentProperties()
            MultiCastComponent.Name = "Mulitcast Source Data"

            'Add Row Count component 
            SourceRowCount = DataFlowTask.ComponentMetaDataCollection.[New]
            SourceRowCount.ComponentClassID = "DTSTransform.RowCount.1"
            rowInstance = SourceRowCount.Instantiate
            rowInstance.ProvideComponentProperties()
            rowInstance.SetComponentProperty("VariableName", "User::int_SourceCnt")

            'Connect multicast and Source
            SourceMultiCastPath = DataFlowTask.PathCollection.[New]
            SourceMultiCastPath.AttachPathAndPropagateNotifications(SourceComponent.OutputCollection(0), MultiCastComponent.InputCollection(0))

            'Connect the Multicast And Row count
            SourceCntPath = DataFlowTask.PathCollection.[New]
            SourceCntPath.AttachPathAndPropagateNotifications(MultiCastComponent.OutputCollection(0), SourceRowCount.InputCollection(0))

            'Add Table Destination

            ' Add OLE-DB destination
            DestinationComponent = DataFlowTask.ComponentMetaDataCollection.New()


            DestinationComponent.ComponentClassID = "DTSAdapter.OLEDBDestination.1"
            DestinationComponent.ValidateExternalMetadata = True



            'Get Destination Instance and initialize component
            DestinationInstance = DestinationComponent.Instantiate
            DestinationInstance.ProvideComponentProperties()
            DestinationComponent.Name = "Archive"
            DestinationComponent.Description = "Archive table"


            'Set Destination Connections
            DestinationComponent.RuntimeConnectionCollection(0).ConnectionManagerID = DestinationConMgr.ID
            DestinationComponent.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(DestinationConMgr)


            'Use straight table transfer
            If IsFastLoad = False Then
                DestinationInstance.SetComponentProperty("AccessMode", 0)

            Else
                DestinationInstance.SetComponentProperty("AccessMode", 3)
                ' DestinationArchiveInstance.SetComponentProperty("AccessMode", 3)
            End If

            DestinationInstance.SetComponentProperty("OpenRowset", DestinationSchTable)

            DestinationInstance.SetComponentProperty("FastLoadKeepIdentity", True)
            DestinationInstance.SetComponentProperty("FastLoadMaxInsertCommitSize", FastLoadMaxCommitSize)
            DestinationInstance.SetComponentProperty("FastLoadKeepNulls", False)
            DestinationInstance.SetComponentProperty("FastLoadOptions", "TABLOCK,CHECK_CONSTRAINTS,ROWS_PER_BATCH = " + RowsPerBatch)
            DestinationInstance.Validate()

            DestinationInstance.AcquireConnections(Nothing)
            DestinationInstance.ReinitializeMetaData()
            DestinationInstance.ReleaseConnections()


            'Add Rowcount and Archive Destination
            DestinationPath = DataFlowTask.PathCollection.New()
            DestinationPath.AttachPathAndPropagateNotifications(SourceRowCount.OutputCollection(0), DestinationComponent.InputCollection(0))


            DestinationInput = DestinationComponent.InputCollection(0)
            DestinationVirtualInput = DestinationInput.GetVirtualInput
            DestinationVirtualInputColumns = CType(DestinationVirtualInput.VirtualInputColumnCollection, IDTSVirtualInputColumnCollection100)


            'Mapping
            For Each vcolumn As IDTSVirtualInputColumn100 In DestinationVirtualInput.VirtualInputColumnCollection
                Dim inputColumn As IDTSInputColumn100 = DestinationInstance.SetUsageType(DestinationInput.ID, DestinationVirtualInput, vcolumn.LineageID, DTSUsageType.UT_READONLY)
                Dim externalColumn As IDTSExternalMetadataColumn100 = DestinationInput.ExternalMetadataColumnCollection(inputColumn.Name)
                DestinationInstance.MapInputColumn(DestinationInput.ID, inputColumn.ID, externalColumn.ID)
            Next


            If IsTransferData = True Then
                pkg.Execute()
                If (pkg.ExecutionResult = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure) Or (pkg.ExecutionStatus = Microsoft.SqlServer.Dts.Runtime.DTSExecStatus.Abend) Then
                    SaveFailPackage(pkg)
                    ' Dim App As Microsoft.SqlServer.Dts.Runtime.Application = New Microsoft.SqlServer.Dts.Runtime.Application
                    ' App.SaveToXml(String.Format("C:\Temp\ssis_ERROR1_" + SourceTable + "-" + DestinationTable + "_" + Format(Now(), "MMddyyyyss") + ".dtsx", pkg.Name), pkg, Nothing)
                    var("User::int_IsPkgFail").Value = CType(1, Int32)
                    var("User::int_DestCurrCnt").Value = Failure
                    var("User::int_DestPrevCnt").Value = Failure
                    var("User::int_SourceCnt").Value = Failure
                    Dts.Events.FireError(-1, "Internal Package Error", "Internal Dynamic package Error For ArchiveExportId: " + CType(ArchiveExportId, String) + " ErrorMessage:" + GetErrorMessage(pkg), "", 0)
                Else
                    ' Dim App As Microsoft.SqlServer.Dts.Runtime.Application = New Microsoft.SqlServer.Dts.Runtime.Application
                    '  App.SaveToXml(String.Format("C:\Temp\ssis_" + SourceTable + "-" + DestinationTable + "_" + Format(Now(), "MMddyyyyss") + ".dtsx", pkg.Name), pkg, Nothing)
                    var("User::int_IsPkgFail").Value = CType(0, Int32)
                    intDestPrev = CType(DestPrevCnt.Value, Int32)
                    intDestCurrCnt = CType(DestCurrCnt.Value, Int32)
                    intSourceCnt = CType(SourceCnt.Value, Int32)
                    var("User::int_DestCurrCnt").Value = intDestCurrCnt
                    var("User::int_DestPrevCnt").Value = intDestPrev
                    var("User::int_SourceCnt").Value = intSourceCnt

                End If

            End If


            var.Unlock()
            pkg = Nothing

            Dts.TaskResult = ScriptResults.Success

        Catch ex As Exception

            ' Dim App As Microsoft.SqlServer.Dts.Runtime.Application = New Microsoft.SqlServer.Dts.Runtime.Application
            ' App.SaveToXml(String.Format("C:\Temp\ssis_ERROR_" + SourceTable + "-" + DestinationTable + "_" + Format(Now(), "MMddyyyyss") + ".dtsx", pkg.Name), pkg, Nothing)
            SaveFailPackage(pkg)
            'App = Nothing
            pkg = Nothing
            var.Unlock()
            Dts.TaskResult = ScriptResults.Failure

        End Try
    End Sub

    Private Function CreateOLEDBConnection(ByVal p As Microsoft.SqlServer.Dts.Runtime.Package, _
                                           ByVal ConName As String, ByVal ConStr As String) As ConnectionManager
        Dim ConMgr As ConnectionManager = p.Connections.Add("OLEDB")
        ConMgr.ConnectionString = ConStr
        ConMgr.Name = ConName
        ConMgr.Description = "SQL OLE DB Using " + ConName + " Connection"
        Return ConMgr
    End Function
    Private Sub SaveFailPackage(ByVal pkg As Microsoft.SqlServer.Dts.Runtime.Package)
        Dim SQLCon As New SqlClient.SqlConnection
        Dim pkid As Int32 = CType(var("User::int_ArchiveExportId").Value, Int32)
        Dim SQL As String = "[dbo].[usp_SsisStorage_InsUpd]"
        Dim Host As ConnectionManager = Dts.Connections("Host")
        Dim ServerName As String = CStr(Host.Properties("ServerName").GetValue(Host))
        Dim DatabaseName As String = CStr(Host.Properties("InitialCatalog").GetValue(Host))
        Dim Connstr As String = "Server=" + ServerName + ";Database=" + DatabaseName + ";Trusted_Connection=True"

        Dim myPkgDoc As XmlDocument = New XmlDocument()

        pkg.SaveToXML(myPkgDoc, Nothing, Nothing)
        SQLCon.ConnectionString = Connstr

        SQLCon.Open()
        Dim sqlCmd As New SqlClient.SqlCommand(SQL, SQLCon)
        sqlCmd.CommandType = CommandType.StoredProcedure
        sqlCmd.Parameters.AddWithValue("ArchiveExportId", pkid)
        sqlCmd.Parameters.AddWithValue("pkg", myPkgDoc.OuterXml)

        sqlCmd.ExecuteNonQuery()
        SQLCon.Close()

        SQLCon = Nothing
        sqlCmd = Nothing

    End Sub
    Function GetErrorMessage(ByVal p As Microsoft.SqlServer.Dts.Runtime.Package) As String
        Dim ErrorMessage As StringBuilder = New StringBuilder

        For Each Message As Microsoft.SqlServer.Dts.Runtime.DtsError In p.Errors
            ErrorMessage.Append(Message.Description.ToString())
        Next
        Return ErrorMessage.ToString()

    End Function



End Class

Upgrading Old Code Part II

A few years back I got the challenge to compare a database table against an archive table, and the requirements was only to validate if  the column existed on the destination, however, with little effort this can be change to validate more properties of the source table. Here is the continuation of upgrading old code from VB .net to C# ("The original code was wrote for ssis 2005)

#region Help:  Introduction to the script task
/* The Script Task allows you to perform virtually any operation that can be accomplished in
 * a .Net application within the context of an Integration Services control flow. 
 * 
 * Expand the other regions which have "Help" prefixes for examples of specific ways to use
 * Integration Services features within this script task. */
#endregion


#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Text;
#endregion

namespace ST_2c87fabb5df34728870590800047a6fa
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
        #region Help:  Using Integration Services variables and parameters in a script
        /* To use a variable in this script, first ensure that the variable has been added to 
         * either the list contained in the ReadOnlyVariables property or the list contained in 
         * the ReadWriteVariables property of this script task, according to whether or not your
         * code needs to write to the variable.  To add the variable, save this script, close this instance of
         * Visual Studio, and update the ReadOnlyVariables and 
         * ReadWriteVariables properties in the Script Transformation Editor window.
         * To use a parameter in this script, follow the same steps. Parameters are always read-only.
         * 
         * Example of reading from a variable:
         *  DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
         * 
         * Example of writing to a variable:
         *  Dts.Variables["User::myStringVariable"].Value = "new value";
         * 
         * Example of reading from a package parameter:
         *  int batchId = (int) Dts.Variables["$Package::batchId"].Value;
         *  
         * Example of reading from a project parameter:
         *  int batchId = (int) Dts.Variables["$Project::batchId"].Value;
         * 
         * Example of reading from a sensitive project parameter:
         *  int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
         * */

        #endregion

        #region Help:  Firing Integration Services events from a script
        /* This script task can fire events for logging purposes.
         * 
         * Example of firing an error event:
         *  Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
         * 
         * Example of firing an information event:
         *  Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
         * 
         * Example of firing a warning event:
         *  Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
         * */
        #endregion

        #region Help:  Using Integration Services connection managers in a script
        /* Some types of connection managers can be used in this script task.  See the topic 
         * "Working with Connection Managers Programatically" for details.
         * 
         * Example of using an ADO.Net connection manager:
         *  object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
         *  SqlConnection myADONETConnection = (SqlConnection)rawConnection;
         *  //Use the connection in some code here, then release the connection
         *  Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
         *
         * Example of using a File connection manager
         *  object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
         *  string filePath = (string)rawConnection;
         *  //Use the connection in some code here, then release the connection
         *  Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
         * */
        #endregion


/// <summary>
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// </summary>
public void Main()
{
// TODO: Add your code here
            Variables vars = null;
        Dts.VariableDispenser.LockForRead("User::str_SourceServer");
        Dts.VariableDispenser.LockForRead("User::str_SourceDatabase");
        Dts.VariableDispenser.LockForRead("User::str_SourceSchema");
        Dts.VariableDispenser.LockForRead("User::str_SourceTable");

        //Destination
        Dts.VariableDispenser.LockForRead("User::str_DestinationServer");
        Dts.VariableDispenser.LockForRead("User::str_DestinationDatabase");
        Dts.VariableDispenser.LockForRead("User::str_DestinationSchema");
        Dts.VariableDispenser.LockForRead("User::str_DestinationTable");

        //Indicator
        Dts.VariableDispenser.LockForWrite("User::int_IsPkgFail");
        Dts.VariableDispenser.GetVariables(ref vars);
            //Source
        String SourceServerName = vars["User::str_SourceServer"].Value.ToString();
        String SourceDB = vars["User::str_SourceDatabase"].Value.ToString();
        String SourceSchema = vars["User::str_SourceSchema"].Value.ToString();
        String SourceTable = vars["User::str_SourceTable"].Value.ToString();
        String SourceTable1 = SourceTable;
       
            //Destination
       String DestServerName  = vars["User::str_DestinationServer"].Value.ToString();
       String DestDB = vars["User::str_DestinationDatabase"].Value.ToString();
       String DestSchema = vars["User::str_DestinationSchema"].Value.ToString();
       String DestTable = vars["User::str_DestinationTable"].Value.ToString();



       int SourceColId = 0;
       int Counter = 0;
       int DestColId = 0;
       Boolean IsFound = false;

            //Source objects
       Server SourceServer;
       ServerConnection SourceSrvConnection = new ServerConnection();
       Database SourceSrvDB;
       Table SourceSrvTable;
            //Destination Objects
       Server DestinationServer;
       ServerConnection DestinationSrvConnection = new ServerConnection();
       Database DestinationSrvDB;
       Table DestSrvTable;

            

            //Connection Strings
               
        String SourceConnStr  = "Server=" + SourceServerName + ";Database=" + SourceDB + ";Trusted_Connection=True;";
        String DestConnStr = "Server=" + DestServerName + ";Database=" + DestDB + ";Trusted_Connection=True;";
        try
        {
            IsFound = true;
            Counter = 0;
            //Source
            SourceSrvConnection.ConnectionString = SourceConnStr;
            SourceServer = new Server(SourceSrvConnection);
            SourceSrvDB = new Database(SourceServer, SourceDB);
            SourceSrvTable = new Table(SourceSrvDB, SourceTable);
           
            //Destination

            DestinationSrvConnection.ConnectionString = DestConnStr;
            DestinationServer = new Server (DestinationSrvConnection);
            DestinationSrvDB = new Database (DestinationServer, DestDB);
            DestSrvTable = new Table (DestinationSrvDB, DestTable, DestSchema);

            //ReInitialized object
            SourceSrvTable.Refresh();
            DestSrvTable.Refresh();

            SourceColId = SourceSrvTable.Columns.Count;
            DestColId = DestSrvTable.Columns.Count;

            if (SourceColId != DestColId)
            {
                IsFound = false;
                Counter = SourceColId;
                Dts.Events.FireError(0, "Source-Destination Compare", "Schema Column Count don't Match for " + SourceSrvTable.Name, "", 0);
            }
            else
            {
                while (SourceSrvTable.Columns.Count > Counter)
                {
                    foreach (Column SourceColumn in SourceSrvTable.Columns)
                    {
                        foreach (Column DestinationColumn in DestSrvTable.Columns)
                        {
                            IsFound = ColumnFinder(SourceColumn.Name,DestinationColumn.Name);
                            if (IsFound == true)
                            {
                                break;
                            }
                        }
                          if (IsFound == false)
                            {
                                break;
                            }
                        Counter++;
                    }
                    if (IsFound == false)
                    {
                        break;
                    }
                 
                }
               
                    vars["User::int_IsPkgFail"].Value = (int) Convert.ToInt32(IsFound);
               vars.Unlock();
               
            }
            

            Dts.TaskResult = (int)ScriptResults.Success;
        }
        catch (Exception e)
        {
            Dts.Events.FireError(0, "Source-Destination Compare", "Unknow error " + e.Message.ToString(), "", 0);
          
            Dts.TaskResult = (int)ScriptResults.Failure;
        }
         
}
        private Boolean ColumnFinder(String Sourcecolumn, String DestinationColumn)
        {
            Boolean isFound = false;
            if (Sourcecolumn.ToLower().CompareTo(DestinationColumn.ToLower()) == 0)
            {
                isFound = true;
            }
            return isFound;
        }

          #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

}
       
}