Creating packages in code – OLE-DB Source to Flat File File Destination

This code sample programmatically creates a package with an OLE-DB Source and a Flat File Destination, and the resulting package exports data from SQL Server to a CSV file.

The finished package just has the one Data Flow Task shown below.

 

The code creates the package, configures the task, and components, then saves the package to disk, useful for checking the package and testing, before finally executing.

namespace Konesans.Dts.Samples
{
    using System;
    using Microsoft.SqlServer.Dts.Runtime;
    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
    using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;

    internal class SqlToFlatFile
    {
        public void CreatePackage()
        {
            Package package = new Package();
            package.Name = "SqlToFlatFile";

            // Add the SQL OLE-DB connection
            ConnectionManager connectionManagerOleDb = package.Connections.Add("OLEDB");
            connectionManagerOleDb.Name = "OLEDB";
            connectionManagerOleDb.ConnectionString =
                "Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=master;Integrated Security=SSPI;";

            // Add the Flat File DB connection, basic info only, will define add columns later
            ConnectionManager connectionManagerFlatFile = package.Connections.Add("FLATFILE");
            connectionManagerFlatFile.ConnectionString = @"C:\Temp\FlatFile.txt";
            connectionManagerFlatFile.Name = "FlatFile";
            connectionManagerFlatFile.Properties["Format"].SetValue(connectionManagerFlatFile, "Delimited");  
            connectionManagerFlatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(connectionManagerFlatFile, true);
            
            // Add the Data Flow Task 
            package.Executables.Add("STOCK:PipelineTask");

            // Get the task host wrapper, and the Data Flow task
            TaskHost taskHost = package.Executables[0] as TaskHost;
            MainPipe dataFlowTask = (MainPipe)taskHost.InnerObject;

            // Add OLE-DB source component
            IDTSComponentMetaData90 componentSource = dataFlowTask.ComponentMetaDataCollection.New();
            componentSource.Name = "OLEDBSource";
            componentSource.ComponentClassID = "DTSAdapter.OleDbSource.1";

            // Get OLE-DB source design-time instance, and initialise component
            CManagedComponentWrapper instanceSource = componentSource.Instantiate();
            instanceSource.ProvideComponentProperties();

            // Set source connection
            componentSource.RuntimeConnectionCollection[0].ConnectionManagerID = connectionManagerOleDb.ID;
            componentSource.RuntimeConnectionCollection[0].ConnectionManager = 
                DtsConvert.ToConnectionManager90(connectionManagerOleDb);

            // Set the source properties
            instanceSource.SetComponentProperty("AccessMode", 2);
            instanceSource.SetComponentProperty("SqlCommand", "SELECT * FROM sysobjects");

            // Reinitialize the metadata, refresh columns
            instanceSource.AcquireConnections(null);
            instanceSource.ReinitializeMetaData();
            instanceSource.ReleaseConnections();


            // Add Flat File destination
            IDTSComponentMetaData90 componentDestination = dataFlowTask.ComponentMetaDataCollection.New();
            componentDestination.Name = "FlatFileDestination";
            componentDestination.ComponentClassID = "DTSAdapter.FlatFileDestination.1";

            // Get Flat File destination design-time instance, and initialise component
            CManagedComponentWrapper instanceDestination = componentDestination.Instantiate();
            instanceDestination.ProvideComponentProperties();

            // Set destination connection
            componentDestination.RuntimeConnectionCollection[0].ConnectionManagerID = connectionManagerFlatFile.ID;
            componentDestination.RuntimeConnectionCollection[0].ConnectionManager = 
                DtsConvert.ToConnectionManager90(connectionManagerFlatFile);

            IDTSPath90 path = dataFlowTask.PathCollection.New();
            path.AttachPathAndPropagateNotifications(componentSource.OutputCollection[0], 
                componentDestination.InputCollection[0]);


            // Get input and virtual input for destination to select and map columns
            IDTSInput90 destinationInput = componentDestination.InputCollection[0];
            IDTSVirtualInput90 destinationVirtualInput = destinationInput.GetVirtualInput();
            IDTSVirtualInputColumnCollection90 destinationVirtualInputColumns = 
                destinationVirtualInput.VirtualInputColumnCollection;

            // Get native flat file connection 
            RuntimeWrapper.IDTSConnectionManagerFlatFile90 connectionFlatFile = 
                connectionManagerFlatFile.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile90;
            
            // Create flat file connection columns to match pipeline
            int indexMax = destinationVirtualInputColumns.Count - 1;
            for (int index = 0; index <= indexMax; index++)
            {
                // Get input column to replicate in flat file
                IDTSVirtualInputColumn90 virtualInputColumn = destinationVirtualInputColumns[index];

                // Add column to Flat File connection manager
                RuntimeWrapper.IDTSConnectionManagerFlatFileColumn90 flatFileColumn = 
                    connectionFlatFile.Columns.Add() as RuntimeWrapper.IDTSConnectionManagerFlatFileColumn90;
                flatFileColumn.ColumnType = "Delimited";
                flatFileColumn.ColumnWidth = virtualInputColumn.Length;
                flatFileColumn.DataPrecision = virtualInputColumn.Precision;
                flatFileColumn.DataScale = virtualInputColumn.Scale;
                flatFileColumn.DataType = virtualInputColumn.DataType;
                RuntimeWrapper.IDTSName90 columnName = flatFileColumn as RuntimeWrapper.IDTSName90;
                columnName.Name = virtualInputColumn.Name;

                if (index < indexMax)
                    flatFileColumn.ColumnDelimiter = ",";
                else
                    flatFileColumn.ColumnDelimiter = Environment.NewLine;
            }

            // Reinitialize the metadata, generating external columns from flat file columns
            instanceDestination.AcquireConnections(null);
            instanceDestination.ReinitializeMetaData();
            instanceDestination.ReleaseConnections();

            // Select and map destination columns
            foreach (IDTSVirtualInputColumn90 virtualInputColumn in destinationVirtualInputColumns)
            {
                // Select column, and retain new input column
                IDTSInputColumn90 inputColumn = instanceDestination.SetUsageType(destinationInput.ID, 
                    destinationVirtualInput, virtualInputColumn.LineageID, DTSUsageType.UT_READONLY);
                // Find external column by name
                IDTSExternalMetadataColumn90 externalColumn =
                    destinationInput.ExternalMetadataColumnCollection[inputColumn.Name];
                // Map input column to external column
                instanceDestination.MapInputColumn(destinationInput.ID, inputColumn.ID, externalColumn.ID);
            }

            
            #if DEBUG
            // Save package to disk, DEBUG only
            new Application().SaveToXml(String.Format(@"C:\Temp\{0}.dtsx", package.Name), package, null);
            Console.WriteLine(@"C:\Temp\{0}.dtsx", package.Name);
            #endif

            package.Execute();

            foreach (DtsError error in package.Errors)
            {
                Console.WriteLine("ErrorCode       : {0}", error.ErrorCode);
                Console.WriteLine("  SubComponent  : {0}", error.SubComponent);
                Console.WriteLine("  Description   : {0}", error.Description);
            }

            package.Dispose();
        }
    }
}

Sample code file and example package produced by the code.

SqlToFlatFile.cs

SqlToFlatFile.dtsx