Importing data with the SSIS Object model

• Download demo project - 31.7 Kb
• Download source code - 67.8 Kb

Introduction

SQL server 2005 Integration Service ships with a rich set of APIs and in this article I am going to show you a way in which you can create and execute an SSIS package programmatically from C# code.

Background

Couple of weeks ago, I was going to import a huge size CSV (comma separated value) file into the SQL server database. Well, it is not such a difficult job that I will write an article on it. Anyone can do that using the SQL server 2005 import wizard, which is a great flexible tool to do this kind of job. But I need to do that work programmatically. And that's what made me think and search the Internet for a solution. After spending some hours I found that SQL server 2005 provides a rich set of APIs of SSIS (SQL server 2005 Integration Service) and anyone can easily create an SSIS package (called DTS Package previously) and execute that package. Unfortunately I did not find code that creates a package that imports data from the CSV file to the SQL server 2005 database. Most of the demo code that I found during my searching imported data from the SQL server to the SQL server or from the SQL server to any flat file. It would be very straightforward to implement one for my own purpose- hopefully just by following the existing demo codes. But soon I discovered that it is a subtle different then those example codes. And after two days of brainstorming I have created a code base which satisfied me. In this article I am just sharing my code and thoughts with you.

Using the code

In my code base I have created two projects for this import business. The first one (ImportLib) is a class library that implements the SSIS package creation tasks and the second one (Import) is a Windows form application which is actually a DEMO that uses the first one as a reference and initiates an Import Job. So exploring the first one is enough to get an good idea about what I actually did.

The object model that I have created is basically standing on a couple of interfaces, namely ImportLib.ISourceStorage, ImportLib.IDestinationStorage, ImportLib.IntegrationService.ISource and ImportLib.IntegrationService.IDestination. Before diving into the details I would like to express my thoughts.

After exploring the SSIS you would find that it is very easy to import data from any kind of data sources to any data destinations. The SSIS object model implemented some well defined interfaces to support this wonderful feature. As I am writing code to programmatically create packages - why shouldn't I implement my code in a way that I can extend more in the future? Currently it will be able to import data from a CSV file to SQL databases but who knows that tomorrow I don't have to import data from an Excel file to SQL server or any other kind of data destination? This idea made me write the ImportLib.ISourceStorage and ImportLib.IDestinationStorage interfaces. I guess you already got an abstract idea for what these interfaces are there – right? Yeah, For all source kind of source storage I defined the ImportLib.ISourceStorage interface. Currently I am only implementing this interface for CSV file sources but later when I will write the Excel source I will simply implement this interface again. The same idea is also applicable to the destination storage where the interface is ImportLib.IDestinationStorage.

Let's take a look on these interfaces:

/// <summary>
/// Defines a contact for the data source
/// </summary>
public interface ISourceStorage
{
    /// <summary>
    /// Initializes the data source
    /// </summary>
    /// <param name="properties">
    /// A <see cref="T:IDictionary<string,
    /// string>"/> that contains
    /// the initialization properties</param>
    void InitializeSource(IDictionary<string, string> properties);

    /// <summary>
    /// Get the schema table for the data source
    /// </summary>
    /// <returns>An instance of <
    /// see cref="T:DataTable"/> that contains the schema
    /// information of the data source</returns>
    DataTable GetSchemaTable();

    /// <summary>
    /// Get the Category of the underlying persistent storage
    /// </summary>
    StorageMedium StorageMedium { get; }

    /// <summary>
    /// Get or set the mapping manager
    /// </summary>
    ColumnMappingController MapManager { get; set; }
}

And

/// <summary>
///     Defines a contact for the Data destination
/// </summary>
public interface IDestinationStorage
{
    /// <summary>
    /// Initializes the data destination
    /// </summary>
    /// <param name="properties">
    /// A <see cref="T:IDictionary<string,
    /// string>"/> that contains
    /// the initialization properties</param>
    void InitializeDestination(IDictionary<string,
        string> properties);

    /// <summary>
    /// Get the Category of the underlying persistent storage
    /// </summary>
    StorageMedium StorageMedium { get; }

    /// <summary>
    /// Get or set the mapping manager
    /// </summary>
    ColumnMappingController MapManager { get; set; }
}

Now let's take a look at the concrete implementations. ImportLib.Delimited.DelimitedDataSource is the concrete class that implements the ImportLib.ISourceStorage. This class simply encapsulated the functionality that is defined by the interface and is specific for a CSV file. It helps to read the CSV file for the source schema using the System.Data.OleDb.OleDbConnection class. And another concrete class that I named as ImportLib.Sql.SqlStorageDestination implements the ImportLib.IDestinationStorage interface. This class provides the functionality defined by the interface and it also contains the routines that are used to create the destination datastore (database and table).

So far so good. Now let's concentrate on the SSIS specific classes and interfaces. Well here again I have defined two interfaces that I mentioned earlier. ImportLib.IntegrationService.ISource and ImportLib.IntegrationService.IDestination. Let's take a look on them.

/// <summary>
/// Defines a contact for the SQL Server
/// Integration Service source objects.
/// </summary>
/// <remarks>
/// Different SSIS source objects should satisfy this interface.
/// Like
/// Flat file data source, OleDB data source etc.
/// </remarks>
public interface ISource
{
    /// <summary>
    /// get or set the <
    /// see cref="T:ImportLib.IDataSource"/> instance
    /// that represents the physical data source.
    /// </summary>
    ISourceStorage DataSource
    {
        get;
        set;
    }

    /// <summary>
    /// Creates a connection manager instance for a given source file
    /// into the context of the given <
    /// see cref="T:ImportLib.IntegrationService
    /// .SsisPackage"/>
    /// object.
    /// </summary>
    /// <param name="package">
    /// An instance of <
    /// see
    /// cref="T:ImportLib.IntegrationService
    /// .SsisPackage"/>.</param>
    void CreateConnection(DTSPackage package);

    /// <summary>
    /// Creates the source dataflow component.
    /// </summary>
    /// <param name="package">The package
    /// instance.</param>
    /// <param name="dataflowTask">
    /// The dataflow task under which the component will be
    /// created.</param>
    /// <returns>An instance of <see
    /// cref="T:SsisDataflowComponent"/>.</returns>
    DataflowComponent CreateSourceDataFlowComponent(
        DTSPackage package, DTSExecutable dataflowTask);

    /// <summary>
    /// Initialize the source dataflow component
    /// </summary>
    /// <param name="sourceDataFlowComponent">
    /// An instance of <see cref="T:
    /// SsisDataflowComponent"/></param>
    void InitializeDataflowComponent(DataflowComponent
        sourceDataFlowComponent);
}

And another one is:

/// <summary>
/// Defines a contact for the SQL Server Integration
/// Service Destination objects.
/// </summary>
/// <remarks>
/// Different data destinations should satisfy
/// this interface. such as Flat file
/// destinations, OleDB destination etc.
/// </remarks>
public interface IDestination
{
    /// <summary>
    /// Get or set the <see cref="T:ImportLib.
    /// IDataDestination"/> instance
    /// that represents the physical data destination.
    /// </summary>
    IDestinationStorage DataDestination
    {
        get;
        set;
    }

    /// <summary>
    /// Get or set the <see cref="T:ImportLib.
    /// ISsisSource"/> instance
    /// </summary>
    ISource SsisSource
    {
        get;
        set;
    }

    /// <summary>
    /// Creates a connection manager instance for a given source file
    /// into the context of the
    /// given <see cref="T:ImportLib.IntegrationService.
    /// SsisPackage"/>
    /// object.
    /// </summary>
    /// <param name="package">
    /// An instance
    /// of <see cref="T:ImportLib.
    /// IntegrationService.SsisPackage"/>.</param>
    void CreateConnection(DTSPackage package);

    /// <summary>
    /// Creates the task that will create the
    /// destination storage (ex. database, table etc);
    /// </summary>
    /// <param name="package">The
    /// package where the task should be created</param>
    /// <returns>
    /// An instance of <see cref="T:ImportLib.IntegrationService.
    /// SsisExecutable"/></returns>
    DTSExecutable CreateStorageCreationTask(DTSPackage package);

    /// <summary>
    /// Creates the destination dataflow component.
    /// </summary>
    /// <param name="package">The package
    /// instance.</param>
    /// <param name="dataflowTask">
    /// The dataflow task under which the component will
    /// be created.</param>
    /// <returns>
    /// An instance of <see cref="T:SsisDataflowComponent
    /// "/>.</returns>
    DataflowComponent CreateDestinationDataFlowComponent
        (DTSPackage package, DTSExecutable dataflowTask);

    /// <summary>
    /// Initialize the destination dataflow component
    /// </summary>
    /// <param name="destinationDataFlowComponent">
    /// An instance of <see
    /// cref="T:SsisDataflowComponent"/></param>
    void InitializeDataflowComponent(DataflowComponent
        destinationDataFlowComponent);
}

These interfaces strictly define the SSIS specific routines such as creating connections, creating data flow components along with some initialization routines. Defining SSIS dataflow is out of scope of this article, for detailed information on these SSIS components, please read MSDN. Now we will take a look at the concrete implementations. ImportLib.IntegrationService.Specialized.FlatFileSource is the concrete implementation of the ImportLib.IntegrationService.ISource interface. Have a look at this class. Here the important thing is we have to explicitly create the source columns ( CreateSourceColumns method ) for the connection which SSIS will never create for you.

namespace ImportLib.IntegrationService.Specialized
{
    /// <summary>
    /// Contains the implementations of the
    /// <see
    /// cref="T:ImportLib.IntegrationService.
    /// ISsisSource"/>
    /// interface.
    /// </summary>
    public class FlatFileSource : ISource
    {
        // The delimited data source instance
        private DelimitedDataSource delimitedDataSource;
        // The Moniker text for the flatfile source
        public const string FlatFileMoniker = @"FLATFILE";
        // The source data flow component GUID
        public const string SourceDataFlowComponentID
          = "{90C7770B-DE7C-435E-880E-E718C92C0573}";
        // schema table
        private DataTable schemaTable = null;
        // The connection manager instance
        private ConnectionManager connectionManager;

        /// <summary>
        /// Creates a new instance
        /// </summary>
        public FlatFileSource()
        {

        }

        #region ISsisSource Members

        /// <summary>
        /// get or set the <see
        /// cref="T:ImportLib.IDataSource"/> instance
        /// that represents the physical data source.
        /// </summary>
        public ISourceStorage DataSource
        {
            get { return delimitedDataSource; }
            set
            {
                // initializing
                delimitedDataSource = value as DelimitedDataSource;
                // Get the schema table
                schemaTable = value.GetSchemaTable();
                // Assert
                Debug.Assert(delimitedDataSource != null);
            }
        }

        /// <summary>
        /// Creates a connection manager instance for a given source file
        /// into the context of the given <see
        /// cref="T:ImportLib.IntegrationService.SsisPackage"/>
        /// object.
        /// </summary>
        /// <param name="package">An instance of
        /// <see cref="T:ImportLib.IntegrationService.
        /// SsisPackage"/>.</param>
        public void CreateConnection(DTSPackage package)
        {
            #region Logging
            Logger.WriteInformation(
              "Creating connection to the source file.");
            #endregion
            // creating a connection manager
            // instance using the FLATFILE moniker
            connectionManager =
              package.InnerObject.Connections.Add(FlatFileMoniker);
            connectionManager.ConnectionString =
                     delimitedDataSource.FileName;
            connectionManager.Name =
               "SSIS Connection Manager for Files";
            connectionManager.Description =
            string.Concat("SSIS Connection Manager");
            // Setting some common properties of the connection manager object
            connectionManager.Properties
            ["ColumnNamesInFirstDataRow"].
                 SetValue(connectionManager,
              delimitedDataSource.FirstRowIsHeader);
            connectionManager.Properties["Format"].SetValue
               (connectionManager, "Delimited");
            connectionManager.Properties["HeaderRowDelimiter"].

                 SetValue(connectionManager,
              delimitedDataSource.HeaderRowDelimiter);
            if (delimitedDataSource.TextQualifier != null)
            {   // If user has been specified a text qualifier then put
               // it into the connection string property
                connectionManager.Properties["TextQualifier"]

                 .SetValue(connectionManager,
                    delimitedDataSource.TextQualifier);
            }
            // create the source columns into the connection manager
            CreateSourceColumns();

            #region Logging
            Logger.WriteInformation(
                "Creating connection to the source file.....Completed");
            #endregion
        }

        /// <summary>
        /// Creates the source dataflow component.
        /// </summary>
        /// <param name="package">The package
        /// instance.</param>
        /// <param name="dataflowTask">
        /// The dataflow task under which the component will be
        /// created.</param>
        /// <returns>An instance of <see
        /// cref="T:SsisDataflowComponent"/>.</returns>
        public DataflowComponent CreateSourceDataFlowComponent
                  (DTSPackage package, DTSExecutable dataflowTask)
        {
            // create the component
            DataflowComponent sourceDataFlowComponent =
             new DataflowComponent(dataflowTask, SourceDataFlowComponentID,
                        "Source Data Flow component");

            return sourceDataFlowComponent;
        }

        /// <summary>
        /// Initialize the source dataflow component
        /// </summary>
        /// <param name="sourceDataFlowComponent">
        /// An instance of <see
        /// cref="T:SsisDataflowComponent"/>
        /// </param>
        public void InitializeDataflowComponent(
            DataflowComponent sourceDataFlowComponent)
        {
            #region Logging
            Logger.WriteInformation(
            "Initializing the managed instance for the source file.");
            #endregion
            // load the COM for the given GUID
            CManagedComponentWrapper managedFlatFileInstance =
                  sourceDataFlowComponent.ComponentInstance;
            // get the populate the properties
            managedFlatFileInstance.ProvideComponentProperties();
            // putting the connection
            if (
              sourceDataFlowComponent.InnerObject.
            RuntimeConnectionCollection.Count > 0)
            {   // If connection is necessary
                sourceDataFlowComponent.InnerObject.
              RuntimeConnectionCollection[0].ConnectionManagerID =
                    connectionManager.ID;
                    sourceDataFlowComponent.InnerObject.
            RuntimeConnectionCollection[0].ConnectionManager =
                                DtsConvert.ToConnectionManager90
                                      (connectionManager);

            }
            // establish a connection
            managedFlatFileInstance.AcquireConnections(null);
            // Initialize the metadata
            managedFlatFileInstance.ReinitializeMetaData();
            // create the mapping now
            IDTSExternalMetadataColumn90 exOutColumn;
            foreach (IDTSOutputColumn90 outColumn in
                sourceDataFlowComponent.InnerObject.
                      OutputCollection[0].OutputColumnCollection)
            {   // create the MAP
                exOutColumn =
                    sourceDataFlowComponent.InnerObject.
                    OutputCollection[0].
                 ExternalMetadataColumnCollection[outColumn.Name];
                // map it
                managedFlatFileInstance.MapOutputColumn(
                    sourceDataFlowComponent.InnerObject.
                      OutputCollection[0].ID, outColumn.ID,
                    exOutColumn.ID, true);
            }
            // Release the connection now
            managedFlatFileInstance.ReleaseConnections();

            #region Logging
            Logger.WriteInformation
                 ("Initializing the managed instance for the source
                   file......completed");
            #endregion
        }

        #endregion

        /// <summary>
        /// Creates the source columns for the flat file connection manager
        /// instance
        /// </summary>
        private void CreateSourceColumns()
        {
            // get the actual connection manger instance
            RuntimeWrapper.IDTSConnectionManagerFlatFile90
            flatFileConnection =
                connectionManager.InnerObject
               as RuntimeWrapper.IDTSConnectionManagerFlatFile90;

            RuntimeWrapper.IDTSConnectionManagerFlatFileColumn90 column;
            RuntimeWrapper.IDTSName90 name;

            // trace the current count
            Debug.WriteLine(flatFileConnection.Columns.Count);

            DataTable schemaTable
             = DataSource.GetSchemaTable(); // get the schema table

            foreach (DataRow row in schemaTable.Rows)
            {   // iterate
                string colName
                = row["ColumnName"] as string;
                     // get the col name
                // now create a new column for the connection manager
                column
                 = flatFileConnection.Columns.Add();
                        // if this is the last row

                if (schemaTable.Rows.IndexOf(row)
                     == (schemaTable.Rows.Count - 1))
                    column.ColumnDelimiter =
                        delimitedDataSource.HeaderRowDelimiter;
                // add the row delimiter
                else
                    column.ColumnDelimiter = delimitedDataSource.Delimiter;

                column.TextQualified =
                delimitedDataSource.TextQualifier != null;
                column.ColumnType = "Delimited";
                column.DataType =
                 RuntimeWrapper.DataType.DT_WSTR;
                column.DataPrecision = 0;
                column.DataScale = 0;
                name = (RuntimeWrapper.IDTSName90)column;
                name.Name = colName;
            }
        }
    }
}

And now let's have a look at the counterpart, the destination implementation.

namespace ImportLib.IntegrationService.Specialized
{
    /// <summary>
    /// Contains the implementation of the IDataDestination interface.
    /// </summary>
    public class SqlServerDataDestination : IDestination
    {
        // The SQL server data destination
        private SqlStorageDestination sqlDataDestination;
        // The Moniker text for the Ole DB
        public const string OleDBMoniker = "OLEDB";
        // the component ID
        public const string OleDBDestinationDataFlowComponentID
                 = "{E2568105-9550-4F71-A638-B7FE42E66922}";
        // source
        private ISource ssisSource;
        // connection manager
        private ConnectionManager connectionManager;

        #region ISsisDestination Members

        /// <summary>
        /// Get or set the <see
        /// cref="T:ImportLib.IDataDestination"/> instance
        /// that represents the physical data destination.
        /// </summary>
        public IDestinationStorage DataDestination
        {
            get { return sqlDataDestination; }
            set
            {
                // initializing
                sqlDataDestination = value as SqlStorageDestination;
                // Assert
                Debug.Assert(sqlDataDestination != null);
            }
        }

        /// <summary>
        /// Get or set the <see
        /// cref="T:ImportLib.ISsisSource"/> instance
        /// </summary>
        public ISource SsisSource
        {
            get { return ssisSource; }
            set { ssisSource = value; }
        }

        /// <summary>
        /// Creates a connection manager instance for a given source file
        /// into the context of the given <see
        /// cref="T:ImportLib.IntegrationService.SsisPackage"/>
        /// object.
        /// </summary>
        /// <param name="package">An instance of
        ///  <see cref="T:ImportLib.IntegrationService.SsisPackage
        /// "/>.</param>
        public void CreateConnection(DTSPackage package)
        {
            // Creating a connection using the oledb moniker
            connectionManager =
              package.InnerObject.Connections.Add(OleDBMoniker);
            connectionManager.ConnectionString =
                    GetSsisConnectionString();
            connectionManager.Name =
               "SSIS Connection Manager for Oledb";
            connectionManager.Description =
          string.Concat(
             "SSIS Connection Manager for ",
                   sqlDataDestination.DatabaseName);
        }

        /// <summary>
        /// Creates the task that will create the destination
        /// storage (ex. database, table etc);
        /// </summary>
        /// <param name="package">The package
        /// where the task should be created</param>
        /// <returns>An instance of
        /// <see cref="T:ImportLib.
        /// IntegrationService.SsisExecutable"/></returns>
        public DTSExecutable CreateStorageCreationTask(DTSPackage package)
        {
            // get the SQL task type
            Type taskType = typeof(ExecuteSQLTask);
            // create a task of type ExecuteSQLTask
            DTSExecutable executable = new DTSExecutable(package, taskType);

            // now configuring the new task
            TaskHost taskHost = executable.InnerObject as TaskHost;
                     // get the Task host instance
            ExecuteSQLTask sqlTask = taskHost.InnerObject as ExecuteSQLTask;
                     // get the SQL task from the host
            sqlTask.Connection = connectionManager.Name;
                     // set the connection manager
            sqlTask.SqlStatementSource =
                sqlDataDestination.GetDestinationTableCreationSql
                    (ssisSource.DataSource.GetSchemaTable());
                     // set the SQL that generates the table

            return executable;
        }

        /// <summary>
        /// Creates the destination dataflow component.
        /// </summary>
        /// <param name="package">The package
        ///  instance.</param>
        /// <param name="dataflowTask">The dataflow task
        /// under which
        /// the component will be created.</param>
        /// <returns>An instance of <see
        /// cref="T:SsisDataflowComponent"/>.</returns>
        public DataflowComponent CreateDestinationDataFlowComponent
                  (DTSPackage package, DTSExecutable dataflowTask)
        {
            #region Logging
            Logger.WriteInformation(
              "Creating managed instances for the destination database");
            #endregion
            // create the component now
            DataflowComponent destinationDataFlowComponent
             = new DataflowComponent(dataflowTask,
                OleDBDestinationDataFlowComponentID,
            "Destination Oledb Component");

            // Before going thru the initialization we need
            // to create the destination table
            // because the SSIS object model will try to
            // access that table fore reading the metadata
            sqlDataDestination.CreateDataStore
                    (ssisSource.DataSource.GetSchemaTable());

            // get the COM instance
            CManagedComponentWrapper managedOleInstance =
                        destinationDataFlowComponent.ComponentInstance;
            // populate the properties
            managedOleInstance.ProvideComponentProperties();
            // setting the connection
            if (destinationDataFlowComponent.InnerObject.
                      RuntimeConnectionCollection.Count > 0)
            {   // If connection is necessary
                destinationDataFlowComponent.InnerObject.
                     RuntimeConnectionCollection[0].
                    ConnectionManagerID =
                    connectionManager.ID;
                destinationDataFlowComponent.InnerObject.
                     RuntimeConnectionCollection[0].
                        ConnectionManager =
                                DtsConvert.ToConnectionManager90
                                    (connectionManager);

            }
            // Set the custom properties.
            managedOleInstance.SetComponentProperty(
                  "AccessMode", 0);
                          // Table of View mode
            managedOleInstance.SetComponentProperty
                ("AlwaysUseDefaultCodePage", false);
                          // Default Codepage
            managedOleInstance.SetComponentProperty
                        ("DefaultCodePage", 1252);
                          // Set it
            managedOleInstance.SetComponentProperty
                          ("FastLoadKeepIdentity", false);
                          // Fast load
            managedOleInstance.SetComponentProperty
                           ("FastLoadKeepNulls", false);
            managedOleInstance.SetComponentProperty
                          ("FastLoadMaxInsertCommitSize", 0);
            managedOleInstance.SetComponentProperty
                                 ("FastLoadOptions",
                            "TABLOCK,CHECK_CONSTRAINTS");
            managedOleInstance.SetComponentProperty("OpenRowset",
                string.Format("[{0}].[dbo].[{1}]",
                   sqlDataDestination.DatabaseName,
                        sqlDataDestination.TableName));



            #region Logging
            Logger.WriteInformation(
          "Creating managed instances for the destination
            database....completed");
            #endregion
            return destinationDataFlowComponent;
        }

        /// <summary>
        /// Initialize the destination dataflow component
        /// </summary>
        /// <param name="destinationDataFlowComponent">
        /// An instance of <see
        /// cref="T:SsisDataflowComponent"/></param>
        public void InitializeDataflowComponent(DataflowComponent
                  destinationDataFlowComponent)
        {
            #region Logging
            Logger.WriteInformation(
                "Creating the destination columns and their mappings");
            #endregion
            // Get the COM instance
            CManagedComponentWrapper managedOleInstance

                     = destinationDataFlowComponent.ComponentInstance;

            // Now activate a connection and create the mappings

            // Establish a connection
            managedOleInstance.AcquireConnections(null);
            // initialize the metadata
            managedOleInstance.ReinitializeMetaData();
            // Get the destination's default input and virtual input.
            IDTSInput90 input = destinationDataFlowComponent.
                      InnerObject.InputCollection[0];
            IDTSVirtualInput90 vInput = input.GetVirtualInput();

            // Iterate through the virtual input column collection.
            foreach (IDTSVirtualInputColumn90 vColumn
                   in vInput.VirtualInputColumnCollection)
            {
                bool res = sqlDataDestination.MapManager.
              IsSuppressedSourceColumn
                  (vColumn.Name,ssisSource.DataSource.GetSchemaTable());
                if (!res)
                {
                    // Call the SetUsageType method of the destination
                    //  to add each available virtual input column as
                    // an input column.
                    managedOleInstance.SetUsageType(
                       input.ID, vInput, vColumn.LineageID,
                    DTSUsageType.UT_READONLY);
                }
            }

            IDTSExternalMetadataColumn90 exColumn;
            foreach (IDTSInputColumn90 inColumn in
                destinationDataFlowComponent.InnerObject.
                  InputCollection[0].InputColumnCollection)
            {   // create the map
                exColumn = destinationDataFlowComponent.InnerObject.
                      InputCollection[0].
                 ExternalMetadataColumnCollection[inColumn.Name];
                string destName = sqlDataDestination.MapManager.
                      GetDestinationColumn(exColumn.Name).ColumnName;
                exColumn.Name = destName;

                managedOleInstance.MapInputColumn(
                           destinationDataFlowComponent.
                   InnerObject.InputCollection[0].ID,
                           inColumn.ID, exColumn.ID);
            }
            // Now release the connection
            managedOleInstance.ReleaseConnections();

            // Now remove the table that we did create
            // for the SSIS object model
            sqlDataDestination.DeleteDataStore();

            #region Logging
            Logger.WriteInformation

              ("Creating the destination columns and their
                 mappings.....completed");
            #endregion
        }

        #endregion

        /// <summary>
        /// Get the SSIS compatible connection string.
        /// </summary>
        /// <returns>A Connection string that is compatible with
        /// SSIS</returns>
        /// <remarks>
        ///     The SSIS Oledb connections uses a provider different than
        /// usual SQL client provider.
        /// It is "SQLNCLI". Without this provider the SSIS cant
        /// create a connection. (!)
        /// On the other hand this provider (SQLNCLI) cant be used as SQL
        /// Client (.NET class) connections.
        /// So this procedure converts a SQL client connection string to SSIS
        /// compatible connection
        /// string by modifying the provider.
        /// </remarks>
        private string GetSsisConnectionString()
        {
            //connectionManager.ConnectionString =
              "Data Source=VSTS;
            Initial Catalog=TEST;Provider=SQLNCLI;Integrated
                 Security=SSPI;Auto Translate=false;";
            //ConMgr.ConnectionString = "
            // Data Source=VSTS;Initial Catalog=TEST;Integrated
            // Security=True";
            string connectionString
                  = sqlDataDestination.ConnectionString;
                 // get the SQL connection string
            Dictionary<string,
              string> connectionProperties =
                 new Dictionary<string, string>();

            foreach( string part in
            connectionString.Split(";".ToCharArray()))
            {   // Iterate thru the properties of the connection string
                string[] keyValue = part.Split("=".ToCharArray(),
                    StringSplitOptions.RemoveEmptyEntries);
                if (keyValue != null && keyValue.Length == 2)
                {
                    string propertyName = keyValue[0].Trim();
                              // the name of the property
                    string valueName = keyValue[1].Trim();
                              // the value of the property
                    // create the entry
                    connectionProperties.Add(propertyName, valueName);
                }
            }
            // Now update these followings
            connectionProperties["Provider"]
                        = "SQLNCLI";
            connectionProperties["Integrated Security"]
                      = "SSPI";
            connectionProperties["Auto Translate"]
                         = "false";

            // Now we are going to create the SSIS compatible
            // connectionstring

            StringBuilder ssisCompatibaleConnectionString
                     = new StringBuilder();
            for (Dictionary<string, string>.Enumerator iterator
               =
            connectionProperties.GetEnumerator();
                   iterator.MoveNext(); )
            {   // Iterate
                if (ssisCompatibaleConnectionString.Length > 0)
                {   // If already there is some properties added
                    ssisCompatibaleConnectionString.Append(";");
                }
                ssisCompatibaleConnectionString.Append(
                    string.Format("{0}={1}",
                     iterator.Current.Key, iterator.Current.Value));
            }

            return ssisCompatibaleConnectionString.ToString();
        }
    }
}

Along with these core classes for import business, I have also written a Log provider for the SSIS package by which I keep Log into the GUI while the package is executing. Creating a log provider means you have to extend the LogProviderBase class provided by Microsoft. Here is my Log provider class.

namespace ImportLib.IntegrationService.Logging
{
    /// <summary>
    /// A custom log provider class
    /// </summary>
    /// <author>
    ///     Moim Hossain
    /// </author>
    [DtsLogProvider(DisplayName = "LogProvider",

  Description = "
    Log provider for DTS packages.",
               LogProviderType = "Custom")]
    public class EventLogProvider : LogProviderBase
    {
        /// <summary>
        /// Configuration string
        /// </summary>
        public override string ConfigString
        {
            get
            {
                return string.Empty;
            }
            set
            {

            }
        }

        /// <summary>
        /// Open log
        /// </summary>
        public override void OpenLog()
        {
            base.OpenLog();
        }

        /// <summary>
        /// Closing log
        /// </summary>
        public override void CloseLog()
        {
            base.CloseLog();
        }

        /// <summary>
        /// Initializations
        /// </summary>
        /// <param name="connections"></param>
        /// <param name="events"></param>
        /// <param name="refTracker"></param>
        public override void InitializeLogProvider
      (Connections connections,
      IDTSInfoEvents events,
        ObjectReferenceTracker refTracker)
        {
            base.InitializeLogProvider(connections, events, refTracker);
        }

        /// <summary>
        /// Write a log
        /// </summary>
        /// <param name="logEntryName"></param>
        /// <param name="computerName"></param>
        /// <param name="operatorName"></param>
        /// <param name="sourceName"></param>
        /// <param name="sourceID"></param>
        /// <param name="executionID"></param>
        /// <param name="messageText"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="dataCode"></param>
        /// <param name="dataBytes"></param>
        public override void Log(string logEntryName,
               string computerName,
               string operatorName,
               string sourceName,
               string sourceID,
               string executionID, string messageText,
               DateTime startTime,
               DateTime endTime, int dataCode, byte[] dataBytes)
        {
            //base.Log(logEntryName, computerName, operatorName,
            // sourceName, sourceID, executionID,
            // messageText, startTime, endTime, dataCode, dataBytes);

            LogEventArgs e = new
                   LogEventArgs(logEntryName,computerName,messageText);
            //
            OnLogCreated(e);
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="e"></param>
        protected virtual void OnLogCreated(LogEventArgs e)
        {
            LogCreatedDelegate mLogCreated = this.LogCreated;

            if (mLogCreated != null)
            {
                mLogCreated(this, e);
            }
        }

        /// <summary>
        ///
        /// </summary>
        public event LogCreatedDelegate LogCreated;
    }

    /// <summary>
    ///
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    public delegate void LogCreatedDelegate ( object sender , LogEventArgs e );
}

Well, the last but not least important thing is the mapping. That means, during the importing task it is possible to import one column's data to another one. So you can provide the mapping between the source and destination column. You can even ignore some source column's data during the importing task. Although, I have not created a GUI for defining the mappings, I have created the mapping from the code. But it is simply a matter of time to create an interactive GUI where use can specify the mappings. Here is the class that encapsulates the mapping information:

namespace ImportLib.Mappings
{
    /// <summary>
    ///     Encapsulates the mapping information for a source data source
    /// and a destination data source
    /// </summary>
    [Serializable()]
    public class ColumnMappingController
    {
        /// <summary>
        /// Creates a new instance
        /// </summary>
        public ColumnMappingController()
        {
            mappings = new List<Map>();
        }

        private List<Map> mappings;

        /// <summary>
        /// Get or set the mappings
        /// </summary>
        public List<Map> Mappings
        {
            get { return mappings; }
            set { mappings = value; }
        }

        private Column[] destinationColumns;

        /// <summary>
        /// Get or set the destination columns
        /// </summary>
        public Column[] DestinationColumns
        {
            get { return destinationColumns; }
            set { destinationColumns = value; }
        }

        /// <summary>
        /// Get the destination columns array where the
        /// columns are not bind to any source columns
        /// </summary>
        public Column[] UnmappedDestinationColumns
        {
            get
            {
                List<Column> unmappedColumns = new List<Column>();
                foreach (Column destinationColumn in destinationColumns)
                {   // iterate
                    if (!ContainsInDestinationMap(destinationColumn))
                    {   // if no mapping found
                        unmappedColumns.Add(destinationColumn);
                    }
                }
                return unmappedColumns.ToArray();
            }
        }

        /// <summary>
        /// Get the source columns where the columns are not bind with any
        /// destination columns
        /// </summary>
        public Column[] SuppressedSourceColumns(DataTable srcSchemaTable)
        {
            List<Column> suppressedColumns = new List<Column>();
            foreach (DataRow row in srcSchemaTable.Rows)
            {   // iterate
                string columnName = row["columnName"] as string;
                if (!ContainsInSourceMap(columnName))
                {   // if no mapping found
                    suppressedColumns.Add(new Column(columnName));
                }
            }
            return suppressedColumns.ToArray();
        }

        /// <summary>
        /// Determine if the specified source column is in suppressed
        /// list or not
        /// </summary>
        public bool IsSuppressedSourceColumn(
               string sourceColumnName, DataTable srcSchemaTable)
        {
            return Array.IndexOf<Column>(
                SuppressedSourceColumns(srcSchemaTable),
                new Column(sourceColumnName)) > -1;
        }

        /// <summary>
        /// Get the destination column for a given source column
        /// </summary>
        /// <param name="sourceColumnName">The
        /// specified source column</param>
        /// <returns>An instance of <see
        /// cref="T:ImportLib.Mappings.Column"/>
        /// which is the destination for the given source column
        /// name.</returns>
        public Column GetDestinationColumn(string sourceColumnName)
        {
            foreach (Map map in mappings)
            {   // iterate
                if (map.SourceColumn.ColumnName.Equals(sourceColumnName))
                    return map.DestinationColumn;
            }
            throw new ApplicationException
            ("No mapping defined for the source column " +
                  sourceColumnName);
        }

        /// <summary>
        /// Determines if the specified column is contains into the
        /// mapping as a source
        /// </summary>
        /// <param name="destinationColumn">The source
        /// column</param>
        /// <returns>
        /// <c>true</c> if the source column found into
        /// the mapping, <c>false</c> otherwise.</returns>
        private bool ContainsInSourceMap(string sourceColumnName)
        {
            foreach (Map map in mappings)
            {   // iterate
                if (map.SourceColumn.ColumnName.Equals(sourceColumnName))
                    return true;
            }
            return false;
        }

        /// <summary>
        /// Determines if the specified column is contains into the
        /// mapping as a destination
        /// </summary>
        /// <param name="destinationColumn">
        /// The destination column</param>
        /// <returns><c>true</c>
        /// if the destination column found into the mapping,
        ///  <c>false</c> otherwise.</returns>
        private bool ContainsInDestinationMap(Column destinationColumn)
        {
            foreach (Map map in mappings)
            {   // iterate
                if (map.DestinationColumn.Equals(destinationColumn))
                    return true;
            }
            return false;
        }

        /// <summary>
        /// Validate the content
        /// </summary>
        private void Validate()
        {
            //if (sourceColumns == null)
             throw
              new NullReferenceException
          ("SourceColumns is not set to an instance of an object");
            if (destinationColumns == null)
               throw
            new NullReferenceException(
             "DestinationColumns is
                 not set to an instance of an object");
        }
    }
}

You will see that when I create an import job I give an instance of this class to the Import manager. So you can either create this instance from the code (like I did) or enable your user to create this from a GUI.

I hope you will enjoy this!

Points of Interest

I have not written any code that can perform any transformations during importing, say you need to perform some work on the data before it is imported into the destination data store. I hope to write some code on that part soon and then I will try to publish an article on that topic.