SSIS动态列映射实现数据流任务连接与转换

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper 
Public Class ScriptMain
    Public Sub Main()
        Try
            ' Create a package and add a Data Flow task.
            Dim package As Microsoft.SqlServer.Dts.Runtime.Package = New Microsoft.SqlServer.Dts.Runtime.Package()
            package.Name = "From" & _
                        Replace(Replace(Replace(Dts.Variables("SourceTable").Value.ToString, ".", ""), "]", ""), "[", "") & "To" & _
                        Replace(Replace(Replace(Dts.Variables("DestinationTable").Value.ToString, ".", ""), "]", ""), "[", "")
            package.ProtectionLevel = DTSProtectionLevel.DontSaveSensitive
            Dim e As Executable = package.Executables.Add("DTS.Pipeline.1")
            Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)
            thMainPipe.Name = "DFTExtractData"
            Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)
            ' Add an OLE DB connection manager to the package to manage the Source database connection.
            Dim conMgrSource As ConnectionManager = package.Connections.Add("OLEDB")
            conMgrSource.ConnectionString = "Provider=SQLOLEDB.1;" & _
              "Data Source=" & Dts.Variables("SourceServer").Value.ToString & ";Initial Catalog=" & Dts.Variables("SourceDB").Value.ToString & ";Integrated Security=SSPI;"
            conMgrSource.Name = "OLESource"
            conMgrSource.Description = "OLE DB connection to the " & Dts.Variables("SourceDB").Value.ToString & " database."
            ' Create and configure an OLE DB source component.  
            Dim source As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New
            source.ComponentClassID = "DTSAdapter.OleDbSource.1"
            ' Create the design-time instance of the source.
            Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate()