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

Public Class ScriptMain

    Dim pkg As New Package()
    Dim app As New Application()
    Dim pkgName As String = "C:\CLF_IS\CLF_IS\CLF_IS_Filho.dtsx"
    Dim pkgName2 As String = "C:\CLF_IS\CLF_IS\CLF_IS_Filho_SAVED.dtsx"
    Dim source As IDTSComponentMetaData90
    Dim destination As IDTSComponentMetaData90
    Dim e As Executable
    Dim thMainPipe As TaskHost
    Dim dataFlowTask As MainPipe
    Dim src As CManagedComponentWrapper
    Dim dst As CManagedComponentWrapper


    Public Sub Pacote()
        pkg = app.LoadPackage(pkgName, Nothing)

        e = pkg.Executables.Item(0)
        thMainPipe = CType(e, TaskHost)
        dataFlowTask = CType(thMainPipe.InnerObject, MainPipe)
        'pkg.Connections("Fonte").ConnectionString = "Data Source=MOZER;Initial Catalog=Accipiens;Provider=SQLNCLI.1" & _
        '";Integrated Security=SSPI;Auto Translate=False;"

        'pkg.Connections("Destino").ConnectionString = "Data Source=MOZER;Initial Catalog=CLF_DW;Provider=SQLNCLI.1" & _
        '";Integrated Security=SSPI;Auto Translate=False;"
    End Sub

    Public Sub Fonte()

        source = dataFlowTask.ComponentMetaDataCollection.Item(0)

        src = source.Instantiate
        src.ProvideComponentProperties()
        source.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections("Fonte"))
        source.RuntimeConnectionCollection(0).ConnectionManagerID = pkg.Connections("Fonte").ID
        src.SetComponentProperty("AccessMode", 0)
        src.SetComponentProperty("OpenRowset", "[dbo].[Agenda]")

        src.AcquireConnections(Nothing)
        src.ReinitializeMetaData()
        src.ReleaseConnections()
    End Sub
    Public Sub Destino()
        destination = dataFlowTask.ComponentMetaDataCollection.Item(1)

        dst = destination.Instantiate
        dst.ProvideComponentProperties()
        destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections("Destino"))
        destination.RuntimeConnectionCollection(0).ConnectionManagerID = pkg.Connections("Destino").ID
        dst.SetComponentProperty("AccessMode", 0)
        dst.SetComponentProperty("OpenRowset", "[dbo].[Agenda]")

        dst.AcquireConnections(Nothing)
        dst.ReinitializeMetaData()
        dst.ReleaseConnections()
    End Sub

    Public Sub Map()

        Dim Input As IDTSInput90 = destination.InputCollection(0)
        Dim vInput As IDTSVirtualInput90 = Input.GetVirtualInput()

        For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection
            Dim vCol As IDTSInputColumn90 = dst.SetUsageType(Input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE)
            dst.MapInputColumn(Input.ID, vCol.ID, Input.ExternalMetadataColumnCollection(vColumn.Name).ID)
        Next

    End Sub
    Sub Save()

        app.SaveToXml(pkgName2, pkg, Nothing)

    End Sub
    Public Sub Main()

        Pacote()
        Fonte()
        Destino()
        Map()
        'Save()

        'For Each str As Microsoft.SqlServer.Dts.Runtime.ConnectionManager In pkg.Connections
        '    MsgBox(str.Name & " : " & str.ConnectionString)
        'Next

        'For Each prop As IDTSCustomProperty90 In source.CustomPropertyCollection
        '    MsgBox(prop.Name & " :  " & prop.Value.ToString(), MsgBoxStyle.Information, "Fonte")
        'Next

        'For Each prop As IDTSCustomProperty90 In destination.CustomPropertyCollection
        '    MsgBox(prop.Name & " :  " & prop.Value.ToString(), MsgBoxStyle.Exclamation, "Destino")
        'Next

        pkg.Execute()

        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class