dataflowconstruction.cs

< SQL Server2005程序设计>

using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

namespace Sample
{
    class DataFlowConstruction
    {
        static void Main(string[] args)
        {
            // Create a new package
            Package package = new Package();
            package.Name = "OLE DB Transfer";

            // Add a Data Flow task
            TaskHost taskHost = package.Executables.Add("DTS.Pipeline") as TaskHost;
            taskHost.Name = "Transfer Table";
            IDTSPipeline90 pipeline = taskHost.InnerObject as MainPipe;

            // Get the pipeline's component metadata collection
            IDTSComponentMetaDataCollection90 componentMetadataCollection = pipeline.ComponentMetaDataCollection;

            // Add a new component metadata object to the data flow
            IDTSComponentMetaData90 oledbSourceMetadata = componentMetadataCollection.New();

            // Associate the component metadata object with the OLE DB Source Adapter
            oledbSourceMetadata.ComponentClassID = "DTSAdapter.OLEDBSource";

            // Instantiate the OLE DB Source adapter
            IDTSDesigntimeComponent90 oledbSourceComponent = oledbSourceMetadata.Instantiate();

            // Ask the component to set up its component metadata object
            oledbSourceComponent.ProvideComponentProperties();

            // Add an OLE DB connection manager
            ConnectionManager connectionManagerSource = package.Connections.Add("OLEDB");
            connectionManagerSource.Name = "OLEDBSource";

            // Set the connection string
            connectionManagerSource.ConnectionString = "provider=sqlncli;server=.;integrated security=sspi;database=AdventureWorks";

            // Set the connection manager as the OLE DB Source adapter's runtime connection
            IDTSRuntimeConnection90 runtimeConnectionSource = oledbSourceMetadata.RuntimeConnectionCollection["OleDbConnection"];
            runtimeConnectionSource.ConnectionManagerID = connectionManagerSource.ID;

            // Tell the OLE DB Source adapter to use the SQL Command access mode.
            oledbSourceComponent.SetComponentProperty("AccessMode", 2);

            // Set up the SQL command
            oledbSourceComponent.SetComponentProperty("SqlCommand", "select EmployeeID, Title from HumanResources.Employee");

            // Set up the connection manager object
            runtimeConnectionSource.ConnectionManager = DtsConvert.ToConnectionManager90(connectionManagerSource);

            // Establish the database connection
            oledbSourceComponent.AcquireConnections(null);

            // Set up the column metadata
            oledbSourceComponent.ReinitializeMetaData();

            // Release the database connection
            oledbSourceComponent.ReleaseConnections();

            // Release the connection manager
            runtimeConnectionSource.ReleaseConnectionManager();

            // Add a new component metadata object to the data flow
            IDTSComponentMetaData90 oledbDestinationMetadata = componentMetadataCollection.New();

            // Associate the component metadata object with the OLE DB Destination Adapter
            oledbDestinationMetadata.ComponentClassID = "DTSAdapter.OLEDBDestination";

            // Instantiate the OLE DB Destination adapter
            IDTSDesigntimeComponent90 oledbDestinationComponent = oledbDestinationMetadata.Instantiate();

            // Ask the component to set up its component metadata object
            oledbDestinationComponent.ProvideComponentProperties();

            // Add an OLE DB connection manager
            ConnectionManager connectionManagerDestination = package.Connections.Add("OLEDB");
            connectionManagerDestination.Name = "OLEDBDestination";

            // Set the connection string
            connectionManagerDestination.ConnectionString = "provider=sqlncli;server=.;integrated security=sspi;database=tempdb";

            // Set the connection manager as the OLE DB Destination adapter's runtime connection
            IDTSRuntimeConnection90 runtimeConnectionDestination = oledbDestinationMetadata.RuntimeConnectionCollection["OleDbConnection"];
            runtimeConnectionDestination.ConnectionManagerID = connectionManagerDestination.ID;

            // Tell the OLE DB Destination adapter to use the SQL Command access mode.
            oledbDestinationComponent.SetComponentProperty("AccessMode", 2);

            // Set up the SQL command
            oledbDestinationComponent.SetComponentProperty("SqlCommand", "select EmployeeID, Title from Employee");

            // Set up the connection manager object
            runtimeConnectionDestination.ConnectionManager = DtsConvert.ToConnectionManager90(connectionManagerDestination);

            // Establish the database connection
            oledbDestinationComponent.AcquireConnections(null);

            // Set up the external metadata column
            oledbDestinationComponent.ReinitializeMetaData();

            // Release the database connection
            oledbDestinationComponent.ReleaseConnections();

            // Release the connection manager
            runtimeConnectionDestination.ReleaseConnectionManager();

            // Get the standard output of the OLE DB Source adapter
            IDTSOutput90 oledbSourceOutput = oledbSourceMetadata.OutputCollection["OLE DB Source Output"];

            // Get the input of the OLE DB Destination adapter
            IDTSInput90 oledbDestinationInput = oledbDestinationMetadata.InputCollection["OLE DB Destination Input"];

            // Create a new path object
            IDTSPath90 path = pipeline.PathCollection.New();

            // Connect the source and destination adapters
            path.AttachPathAndPropagateNotifications(oledbSourceOutput, oledbDestinationInput);

            // Get the output column collection for the OLE DB Source adapter
            IDTSOutputColumnCollection90 oledbSourceOutputColumns = oledbSourceOutput.OutputColumnCollection;

            // Get the external metadata column collection for the OLE DB Destination adapter
            IDTSExternalMetadataColumnCollection90 externalMetadataColumns = oledbDestinationInput.ExternalMetadataColumnCollection;

            // Get the virtual input for the OLE DB Destination adapter.
            IDTSVirtualInput90 virtualInput = oledbDestinationInput.GetVirtualInput();

            // Loop through our output columns
            foreach (IDTSOutputColumn90 outputColumn in oledbSourceOutputColumns)
            {
                // Add a new input column
                IDTSInputColumn90 inputColumn = oledbDestinationComponent.SetUsageType(oledbDestinationInput.ID, virtualInput, outputColumn.LineageID, DTSUsageType.UT_READONLY);

                // Get the external metadata column from the OLE DB Destination 
                // using the output column's name
                IDTSExternalMetadataColumn90 externalMetadataColumn = externalMetadataColumns[outputColumn.Name];

                // Map the new input column to its corresponding external metadata column.
                oledbDestinationComponent.MapInputColumn(oledbDestinationInput.ID, inputColumn.ID, externalMetadataColumn.ID);
            }

            // Save the package
            Application application = new Application();
            application.SaveToXml(@"c:\OLEDBTransfer.dtsx", package, null);
        }
    }
}