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

This code sample programmatically creates a package with an OLE-DB Source and a Raw File Destination, and the resulting package exports data from SQL Server to a raw 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;

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

            // 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 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, including source SQL query
            instanceSource.SetComponentProperty("AccessMode", 2); // 2 - SQL Command
            instanceSource.SetComponentProperty("SqlCommand", "SELECT * FROM sysobjects");

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


            // Add Raw File destination
            IDTSComponentMetaData90 componentDestination = dataFlowTask.ComponentMetaDataCollection.New();
            componentDestination.Name = "RawFileDestination";
            componentDestination.ComponentClassID = "DTSAdapter.RawDestination.1";

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

            // Set detsination properties
            instanceDestination.SetComponentProperty("AccessMode", 0); // 0 - File name
            instanceDestination.SetComponentProperty("FileName", @"C:\Temp\RawFile.raw");
            instanceDestination.SetComponentProperty("WriteOption", 0); // 0 - Create Always

            // Connect path form source to destination 
            IDTSPath90 path = dataFlowTask.PathCollection.New();
            path.AttachPathAndPropagateNotifications(componentSource.OutputCollection[0], 
                componentDestination.InputCollection[0]);

            // Get input and virtual input for destination to select columns
            IDTSInput90 destinationInput = componentDestination.InputCollection[0];
            IDTSVirtualInput90 destinationVirtualInput = destinationInput.GetVirtualInput();
            IDTSVirtualInputColumnCollection90 destinationVirtualInputColumns = 
                destinationVirtualInput.VirtualInputColumnCollection;
            foreach (IDTSVirtualInputColumn90 virtualInputColumn in destinationVirtualInputColumns)
            {
                IDTSInputColumn90 inputColumn = instanceDestination.SetUsageType(destinationInput.ID, 
                    destinationVirtualInput, virtualInputColumn.LineageID, DTSUsageType.UT_READONLY);
            }

            #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

            // Execute package
            package.Execute();

            // Basic check for errors
            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 file and package.

SqlToRawFile.dtsx

SqlToRawFile.cs