createssis package.vb

SQL2005开发指南

Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports SSISRuntime = Microsoft.SqlServer.Dts.Runtime.Wrapper


Module CreateSSISPackage

    Sub Main()
        ' Create the Package
        Console.WriteLine("Creating the MySSIS Package")
        Dim myPackage As New Package()
        myPackage.PackageType = DTSPackageType.DTSDesigner90
        myPackage.Name = "MySSISPackage"
        myPackage.Description = "Created using the SSIS API"
        myPackage.CreatorComputerName = System.Environment.MachineName
        myPackage.CreatorName = System.Environment.UserName

        'Add the OLE DB and Flat File Connection Managers
        Console.WriteLine("Creating the MyOLEDBConnection")
        Dim cnOLEDB As ConnectionManager = myPackage.Connections.Add("OLEDB")
        cnOLEDB.Name = "MyOLEDBConnection"
        cnOLEDB.ConnectionString = _
          "Provider=SQLNCLI;Integrated Security=SSPI;" _
          & "Initial Catalog=AdventureWorks;Data Source=SQL2005-2;"

        Console.WriteLine("Creating the MyFlatFileConnection")
        Dim cnFile As ConnectionManager = _
          myPackage.Connections.Add("FLATFILE")
        cnFile.Properties("Name").SetValue(cnFile, "MyFlatFileConnection")
        cnFile.Properties("ConnectionString").SetValue _
          (cnFile, "c:\temp\MySSISFileExport.csv")
        cnFile.Properties("Format").SetValue(cnFile, "Delimited")
        cnFile.Properties("ColumnNamesInFirstDataRow").SetValue(cnFile, False)
        cnFile.Properties("DataRowsToSkip").SetValue(cnFile, 0)
        cnFile.Properties("RowDelimiter").SetValue(cnFile, vbCrLf)
        cnFile.Properties("TextQualifier").SetValue(cnFile, """")

        'Add a Data Flow Task
        Console.WriteLine("Adding a Data Flow Task")
        Dim taskDF As TaskHost = _
          TryCast(myPackage.Executables.Add("DTS.Pipeline"), TaskHost)
        taskDF.Name = "DataFlow"

        Dim DTP As MainPipe
        DTP = TryCast(taskDF.InnerObject, MainPipe)

        ' Add the OLE DB Source
        Console.WriteLine("Adding an OLEDB Source")
        Dim DFSource As IDTSComponentMetaData90
        DFSource = DTP.ComponentMetaDataCollection.New()
        DFSource.ComponentClassID = "DTSAdapter.OLEDBSource"
        DFSource.Name = "OLEDBSource"

        Dim SourceInst As CManagedComponentWrapper = _
          DFSource.Instantiate()
        SourceInst.ProvideComponentProperties()
        DFSource.RuntimeConnectionCollection(0).ConnectionManagerID _
            = myPackage.Connections("MyOLEDBConnection").ID
        DFSource.RuntimeConnectionCollection(0).ConnectionManager _
            = DtsConvert.ToConnectionManager90 _
             (myPackage.Connections("MyOLEDBConnection"))
        SourceInst.SetComponentProperty("OpenRowset", "[Sales].[Customer]")
        SourceInst.SetComponentProperty("AccessMode", 0)
        SourceInst.AcquireConnections(Nothing)
        SourceInst.ReinitializeMetaData()
        SourceInst.ReleaseConnections()

        ' Add the Flat File Destination
        Console.WriteLine("Adding a Flat File Destination")
        Dim DFDestination As IDTSComponentMetaData90
        DFDestination = DTP.ComponentMetaDataCollection.New()
        DFDestination.ComponentClassID = "DTSAdapter.FlatFileDestination"
        DFDestination.Name = "FlatFileDestination"

        ' Create an instance of the component
        Dim DestInst As CManagedComponentWrapper = _
          DFDestination.Instantiate()
        DestInst.ProvideComponentProperties()
        DFDestination.RuntimeConnectionCollection(0).ConnectionManagerID _
            = myPackage.Connections("MyFlatFileConnection").ID
        DFDestination.RuntimeConnectionCollection(0).ConnectionManager _
            = DtsConvert.ToConnectionManager90( _
            myPackage.Connections("MyFlatFileConnection"))

        ' Map a connection between the source and destination
        DTP.PathCollection.New().AttachPathAndPropagateNotifications( _
            DFSource.OutputCollection(0), DFDestination.InputCollection(0))

        ' Add columns to the FlatFileConnectionManager
        Dim MyFlatFilecn As _
          SSISRuntime.IDTSConnectionManagerFlatFile90 = Nothing
        For Each cm As ConnectionManager In myPackage.Connections
            If cm.Name = "MyFlatFileConnection" Then
                MyFlatFilecn = TryCast(cm.InnerObject, _
                  SSISRuntime.IDTSConnectionManagerFlatFile90)
                DtsConvert.ToConnectionManager90(cm)
            End If
        Next

        ' Get the columns from the source
        Dim InColumns As IDTSVirtualInputColumnCollection90 _
            = DFDestination.InputCollection(0).GetVirtualInput() _
        .VirtualInputColumnCollection()

        Dim col As SSISRuntime.IDTSConnectionManagerFlatFileColumn90
        Dim name As SSISRuntime.IDTSName90
        For cols As Integer = 0 To InColumns.Count - 1 Step 1
            col = MyFlatFilecn.Columns.Add()
            ' Set the last column delimiter to CRLF
            If cols = InColumns.Count - 1 Then
                col.ColumnDelimiter = vbCrLf
            Else
                col.ColumnDelimiter = ","
            End If
            col.ColumnType = "Delimited"
            col.DataType = InColumns(cols).DataType
            col.DataPrecision = InColumns(cols).Precision
            col.DataScale = InColumns(cols).Scale
            name = TryCast(col, SSISRuntime.IDTSName90)
            name.Name = InColumns(cols).Name
        Next

        ' map the columns
        DestInst.AcquireConnections(Nothing)
        DestInst.ReinitializeMetaData()

        Dim wrapper As CManagedComponentWrapper = _
          DFDestination.Instantiate()
        Dim vInput As IDTSVirtualInput90 = _
          DFDestination.InputCollection(0).GetVirtualInput()
        For Each vColumn As IDTSVirtualInputColumn90 In _
          vInput.VirtualInputColumnCollection
            wrapper.SetUsageType(DFDestination _
              .InputCollection(0).ID, vInput, vColumn.LineageID, _
                DTSUsageType.UT_READONLY)
        Next

        ' Match the input and output columns
        Dim exCol As IDTSExternalMetadataColumn90
        For Each InCol As IDTSInputColumn90 In _
          DFDestination.InputCollection(0).InputColumnCollection
            exCol = DFDestination.InputCollection(0) _
              .ExternalMetadataColumnCollection(InCol.Name)
            wrapper.MapInputColumn(DFDestination _
              .InputCollection(0).ID, InCol.ID, exCol.ID)
        Next
        DestInst.ReleaseConnections()

        ' Validate the package
        Console.WriteLine("Validating the MySSISPackage")
        Dim pkgStatus As DTSExecResult = myPackage.Validate(Nothing, Nothing, Nothing, Nothing)
        System.Console.WriteLine("Validation result: " & pkgStatus.ToString())

        ' Save the package
        Console.WriteLine("Saving the MySSISPackage")
        Dim SSISExe As New Application()
        SSISExe.SaveToXml("c:\temp\MySSISPAckage.dtsx", myPackage, Nothing)

        ' Execute the Package
        If pkgStatus = DTSExecResult.Success Then
            Console.WriteLine("Executing the MySSISPackage")
            Dim pkgResult As DTSExecResult = myPackage.Execute()
            Console.WriteLine("MySSISPackage results: " & pkgResult.ToString)
        Else
            Console.WriteLine("Package validation failed")
        End If
        Console.ReadKey()

    End Sub

End Module