Programmatically create Data Flow Task without source or destination

Question

I have a SSIS package that copies data from Microsoft Access into SQL Server. The two sets of tables are almost identical.

Background: Unsurprisingly, our table schema grows as we develop our product. Therefore we keep needing to update the SSIS package with the new columns. This is a very dull task, therefore I am trying to programmatically create the SSIS package in C#. This is going well, but I'd like to make the process even easier.

Issue: The package generation process requires the source (Access) and destination (SQL Server) to be present at the time the C# is run. This doesn't work that well with our current processes. Therefore I would like to either:

• Supply the tables and columns programmatically (the SQL and Access tables are generated from XML metadata, so we can supply the tables and columns to SSIS from this same metadata)
• At the time the package is loaded, run some C# to amend the package, i.e. ReinitializeMetaData, etc

I have a fairly strong preference for the first of the two solutions. However I don't know how to make either of them work.

More details: I've been using the following SSIS code (from Microsoft's examples). I think I want to have something that runs without having to do AcquireConnections or ReinitializeMetaData - I want to provide the metadata myself. Obviously what I provide will have to exactly match what will actually be present when the package is validated and run.

public IDTSComponentMetaData100 AddDestAdapter(IDTSPipeline100 pipeline, ConnectionManager destConnMgr, out IDTSDesigntimeComponent100 destDesignTimeComp)
{
    IDTSComponentMetaData100 destComp = pipeline.ComponentMetaDataCollection.New();
    destComp.ComponentClassID = OLEDB_DEST_GUID;
    destComp.ValidateExternalMetadata = true;
    destDesignTimeComp = destComp.Instantiate();
    destDesignTimeComp.ProvideComponentProperties();
    destComp.Name = "OleDB Destination - Sql Server";
    destDesignTimeComp.SetComponentProperty("AccessMode", 0);
    destDesignTimeComp.SetComponentProperty("OpenRowset", quotedTableName);

    // set connection
    destComp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(destConnMgr);
    destComp.RuntimeConnectionCollection[0].ConnectionManagerID = destConnMgr.ID;

    // get metadata
    destDesignTimeComp.AcquireConnections(null);
    destDesignTimeComp.ReinitializeMetaData();
    destDesignTimeComp.ReleaseConnections();

    extCols = destComp.InputCollection[0].ExternalMetadataColumnCollection;

    return destComp;
}

public void AddPathsAndConnectColumns()
{
    IDTSOutput100 srcOutput = srcComp.OutputCollection[0];
    IDTSOutputColumnCollection100 srcOutputCols = srcOutput.OutputColumnCollection;
    IDTSInput100 destInput = destComp.InputCollection[0];
    IDTSInputColumnCollection100 destInputCols = destInput.InputColumnCollection;
    IDTSExternalMetadataColumnCollection100 destExtCols = destInput.ExternalMetadataColumnCollection;

    Hashtable destColtable = new Hashtable(destExtCols.Count);
    foreach (IDTSExternalMetadataColumn100 extCol in destExtCols)
    {
        destColtable.Add(extCol.Name, extCol);
    }

    // colConvertTable stores a pair of columns which need a type conversion
    // colConnectTable stores a pair of columns which dont need a type conversion and can be connected directly.
    Hashtable colConvertTable = new Hashtable(srcOutputCols.Count);
    Hashtable colConnectTable = new Hashtable(srcOutputCols.Count);
    foreach (IDTSOutputColumn100 outputCol in srcOutputCols)
    {
        // Get the column name to look for in the destination.
        // Match column by name if match table is not used.
        String colNameToLookfor = outputCol.Name;

        IDTSExternalMetadataColumn100 extCol = (String.IsNullOrEmpty(colNameToLookfor)) ? null : (IDTSExternalMetadataColumn100)destColtable[colNameToLookfor];
        // Does the destination column exist?
        if (extCol != null)
        {
                colConnectTable.Add(outputCol.ID, extCol);
        }
    }

    // Convert transform not needed. Connect src and destination directly.
    pipeline.PathCollection.New().AttachPathAndPropagateNotifications(srcOutput, destInput);

    IDTSVirtualInput100 destVirInput = destInput.GetVirtualInput();

    foreach (object key in colConnectTable.Keys)
    {
        int colID = (int)key;
        IDTSExternalMetadataColumn100 extCol = (IDTSExternalMetadataColumn100)colConnectTable[key];
        // Create an input column from an output col of previous component.
        destVirInput.SetUsageType(colID, DTSUsageType.UT_READONLY);
        IDTSInputColumn100 inputCol = destInputCols.GetInputColumnByLineageID(colID);
        if (inputCol != null)
        {
            // map the input column with an external metadata column
            destDesignTimeComp.MapInputColumn(destInput.ID, inputCol.ID, extCol.ID);
        }
    }
}

Possible routes of exploration:

• This MSDN thread is somebody with the same question (though no concrete solution)
• From some investigation of BIML, it appears that BIML struggles with the same issues (OfflineSchema seems like it might work, although I'm a bit wary of it, seeing as I can't find a single example on the internet).

Answer 1

Just use BimlExpress, a free addin for Visual Studio. It allows for great flexibility without the hassle of boring and error-prone interaction with DTS API.

Answer 2

IDTSExternalMetadataColumn100 and IDTSExternalMetadataColumnCollection100 are your friends for the use case you are looking for. When the calls to AcquireConnections, ReinitializeMetaData, ReleaseConnections are successful, the metadata about the external columns is grabbed from the external sources and populated in a collection and each external column is mapped to a IDTSInputColumn100/IDTSOutputColumn100 column (depending on type of component). Those calls are responsible to grab the metadata and create the required IDTSExternalMetadataColumn100 and the IDTSInputColumn100/IDTSOutputColumn100 column collections.

Unfortunately, (atleast to the extent I know), we cannot override the ReinitializeMetaData method (besides, overriding might cause other issues, so it is better that we do not override them). But we can still achieve the end result, albeit in a more detailed manner. This means that we will ourselves create the required IDTSExternalMetadataColumn100 and the IDTSInputColumn100/IDTSOutputColumn100 columns (programmatically).

Take a look at the code below for an example involving a Source Component. You would do the same for a Destination component, but instead of working with IDTSOutputColumn, you will work with IDTSInputColumn in case of a Destination component.

The code below uses the SSIS Object Model library directly; and as you can see, there are quite some things that you would need to do. I wrote a library to simplify some of these things. That library contains an example of creating a destination component in a similiar use case. Look here; specifically the code towards the end of the method GenerateProjectToLoadTextFilesToSqlServerDatabase.

namespace ConsoleApplication5
{
// A struct ot represent an external column
public struct Column
{
    public String Name;
    public String SSISDataType;
    public int Length;
    public int Precision;
    public int Scale;
    public int CodePage;

    public Column(String name, String ssisDataType, int length, int precision, int scale, int codePage)
    {
        Name = name;
        SSISDataType = ssisDataType;
        Length = length;
        Precision = precision;
        Scale = scale;
        CodePage = codePage;
    }
}

public class Packager
{
    public Packager()
    {
        build();
    }

    private void build()
    {
        #region Package Related
        // Package related
        Package package = new Package();
        Executable e = package.Executables.Add("STOCK:PipelineTask");
        TaskHost thMainPipe = e as TaskHost;
        MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
        thMainPipe.Name = "MyDFT";
        thMainPipe.DelayValidation = true;
        #endregion

        #region Add Connection Manager
        // Add Connection Manager
        ConnectionManager cm = package.Connections.Add("OLEDB");
        cm.Name = "OLEDB ConnectionManager";
        cm.ConnectionString = "Data Source=(local);" +
          "Initial Catalog=AdventureWorks;Provider=SQLOLEDB.1;" +
          "Integrated Security=SSPI;";
        #endregion

        #region Add a OleDB Source and set up basic properties
        // Add an OLE DB source to the data flow.  
        IDTSComponentMetaData100 component = dataFlowTask.ComponentMetaDataCollection.New();
        component.Name = "OLEDBSource";
        component.ComponentClassID = "Microsoft.OLEDBSource"; // check for the exact component class ID on your machine

        // Get the design time instance of the component.  
        CManagedComponentWrapper instance = component.Instantiate();

        // Initialize the component  
        instance.ProvideComponentProperties();

        // Specify the connection manager.  
        if (component.RuntimeConnectionCollection.Count > 0)
        {
            component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(package.Connections[0]);
            component.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[0].ID;
        }

        // Set the custom properties.  
        instance.SetComponentProperty("AccessMode", 2);
        instance.SetComponentProperty("SqlCommand", "Select * from Production.Product");
        #endregion

        #region Core example showcasing use of IDTSExternalMetadataColumn when external data source is not available.

        // Typically here we call acquireconnection, reinitmetadata etc to get the metadata from a data source that exists.
        // Instead we will populate the metadata ourselves

        #region Get External Columns Metadata
        // Get the collection of external columns
        List<Column> externalColumns = new List<Column>();

        // Hard Coding Here. But grab them from your metadata source programmatically.
        Column columnA = new Column("col_a", "DT_STR", 24, 0, 0, 1252);
        Column columnB = new Column("col_b", "DT_STR", 36, 0, 0, 1252);
        Column columnC = new Column("col_c", "DT_STR", 48, 0, 0, 1252);
        externalColumns.Add(columnA);
        externalColumns.Add(columnB);
        externalColumns.Add(columnC);
        #endregion

        #region Add External Columns to our required IDTSOutput100
        // Grab the appropriate output as needed. We will be adding ExternalColumns to this Output
        IDTSOutput100 output = component.OutputCollection[0];

        // Add each external column to the above IDTSOutPut
        foreach (Column extCol in externalColumns)
        {
            IDTSExternalMetadataColumn100 col = output.ExternalMetadataColumnCollection.New();
            col.Name = extCol.Name;
            col.Scale = extCol.Scale;
            col.Precision = extCol.Precision;
            col.Length = extCol.Length;
            col.CodePage = extCol.CodePage;
            col.DataType = (Wrapper.DataType)Enum.Parse(typeof(Wrapper.DataType), extCol.SSISDataType);
        }
        #endregion

        #region Create OutputColumn if it does not exist/or grab the output column if it Exists. Then associate it to the External Column

        // Now associate the External Column to an Output Column.
        // Here, we will simply associate the external column to an output column if the name matches (because of our use case)
        foreach (IDTSExternalMetadataColumn100 extCol in output.ExternalMetadataColumnCollection)
        {
            bool outputColExists = false;

            // Set DataTypes and Associate with external column if output col exists
            foreach (IDTSOutputColumn100 outputCol in output.OutputColumnCollection)
            {
                if (outputCol.Name == extCol.Name) // is map based on name
                {
                    // Set the data type properties
                    outputCol.SetDataTypeProperties(extCol.DataType, extCol.Length, extCol.Precision, extCol.Scale, extCol.CodePage);

                    // Associate the external column and the output column
                    outputCol.ExternalMetadataColumnID = extCol.ID;

                    outputColExists = true;
                    break;
                }
            }

            // Create an IDTSOutputColumn if not exists. 
            if (!(outputColExists))
            {
                IDTSOutputColumn100 outputCol = output.OutputColumnCollection.New();
                outputCol.Name = extCol.Name;  // map is based on name

                // Set the data type properties
                outputCol.SetDataTypeProperties(extCol.DataType, extCol.Length, extCol.Precision, extCol.Scale, extCol.CodePage);

                // Associate the external column and the output column
                outputCol.ExternalMetadataColumnID = extCol.ID;
            }
        }
        #endregion

        #endregion

        #region Save the Package to disk

        new Application().SaveToXml(@"C:\Temp\Pkg.dtsx", package, null);

        #endregion
    }
}

}