SQL2005--SSIS脚本开发--脚本生成数据流
由于SQL2005的SSIS无法在运行阶段自动实现列映射,无法实现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()
' The ProvideComponentProperties method creates a default output.
srcDesignTime.ProvideComponentProperties()
' Specify the connection manager.
If source.RuntimeConnectionCollection.Count > 0 Then
source.RuntimeConnectionCollection(0).ConnectionManagerID = package.Connections("OLESource").ID()
source.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections("OLESource"))
End If
' Set the custom properties of the source.
srcDesignTime.SetComponentProperty("OpenRowset", Dts.Variables("SourceTable").Value.ToString)
srcDesignTime.SetComponentProperty("AccessMode", 0)
' Connect to the OLESource data source,
' and then update the metadata for the source.
srcDesignTime.AcquireConnections(Nothing)
srcDesignTime.ReinitializeMetaData()
srcDesignTime.ReleaseConnections()
' Add an OLE DB connection manager to the package to manage the Destination database connection.
Dim conMgrDest As ConnectionManager = package.Connections.Add("OLEDB")
conMgrDest.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Data Source=" & Dts.Variables("DestinationServer").Value.ToString & ";Initial Catalog=" & Dts.Variables("DestinationDB").Value.ToString & ";Integrated Security=SSPI;"
conMgrDest.Name = "OLEDestination"
conMgrDest.Description = "OLE DB connection to the " & Dts.Variables("DestinationDB").Value.ToString & " database."
' Create and configure an OLE DB destination.
Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New
destination.ComponentClassID = "DTSAdapter.OleDbDestination.1"
' Create the design-time instance of the destination.
Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate
' The ProvideComponentProperties method creates a default input.
destDesignTime.ProvideComponentProperties()
' Specify the connection manager.
If destination.RuntimeConnectionCollection.Count > 0 Then
destination.RuntimeConnectionCollection(0).ConnectionManagerID = package.Connections("OLEDestination").ID()
destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections("OLEDestination"))
End If
' MsgBox(Dts.Variables("DestinationTable").Value.ToString)
' Set the custom properties of the source.
destDesignTime.SetComponentProperty("AccessMode", 0)
destDesignTime.SetComponentProperty("OpenRowset", Dts.Variables("DestinationTable").Value.ToString)
'reinitialize the component
destDesignTime.AcquireConnections(Nothing)
destDesignTime.ReinitializeMetaData()
destDesignTime.ReleaseConnections()
'map the columns
Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()
path.AttachPathAndPropagateNotifications(source.OutputCollection(0), destination.InputCollection(0))
Dim input As IDTSInput90 = destination.InputCollection(0)
Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()
Dim vColumn As IDTSVirtualInputColumn90
For Each vColumn In vInput.VirtualInputColumnCollection
Dim vCol As IDTSInputColumn90 = destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
Dim exCol As IDTSExternalMetadataColumn90 = input.ExternalMetadataColumnCollection(vColumn.Name)
destDesignTime.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection(vColumn.Name).ID)
Next
Dim app As Application = New Application()
Dim strPath As System.String
strPath = Dts.Variables("ChildPackagePath").Value.ToString & package.Name.ToString & ".dtsx"
app.SaveToXml(strPath, package, Nothing)
'Set the value for the return variable to the name of the new package
Dts.Variables("ChildPackageName").Value = package.Name.ToString & ".dtsx"
'Run package
package.Execute()
'Send success result if no exceptions are raised
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
'Send failure result if exceptions are raised
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub
End Class
