CreationName for SSIS 2008 and adding components programmatically

by Darren Green 2 Feb 2009 16:14

If you are building SSIS 2008 packages programmatically and adding data flow components, you will probably need to know the creation name of the component to add. I can never find a handy reference when I need one, hence this rather mundane post. See also CreationName for SSS 2005.

We start with a very simple snippet for adding a component:

// 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 - ** This is where we need the creation name **
IDTSComponentMetaData90 componentSource = dataFlowTask.ComponentMetaDataCollection.New();
componentSource.Name = "OLEDBSource";
componentSource.ComponentClassID = "DTSAdapter.OLEDBSource.2"; 

So as you can see the creation name for a OLE-DB Source is DTSAdapter.OLEDBSource.2.

CreationName Reference 

ADO NET Destination Microsoft.SqlServer.Dts.Pipeline.ADONETDestination, Microsoft.SqlServer.ADONETDest, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
ADO NET Source Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter, Microsoft.SqlServer.ADONETSrc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Aggregate DTSTransform.Aggregate.2
Audit DTSTransform.Lineage.2
Cache Transform DTSTransform.Cache.1
Character Map DTSTransform.CharacterMap.2
Checksum Konesans.Dts.Pipeline.ChecksumTransform.ChecksumTransform, Konesans.Dts.Pipeline.ChecksumTransform, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b2ab4a111192992b
Conditional Split DTSTransform.ConditionalSplit.2
Copy Column DTSTransform.CopyMap.2
Data Conversion DTSTransform.DataConvert.2
Data Mining Model Training MSMDPP.PXPipelineProcessDM.2
Data Mining Query MSMDPP.PXPipelineDMQuery.2
DataReader Destination Microsoft.SqlServer.Dts.Pipeline.DataReaderDestinationAdapter, Microsoft.SqlServer.DataReaderDest, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Derived Column DTSTransform.DerivedColumn.2
Dimension Processing MSMDPP.PXPipelineProcessDimension.2
Excel Destination DTSAdapter.ExcelDestination.2
Excel Source DTSAdapter.ExcelSource.2
Export Column TxFileExtractor.Extractor.2
Flat File Destination DTSAdapter.FlatFileDestination.2
Flat File Source DTSAdapter.FlatFileSource.2
Fuzzy Grouping DTSTransform.GroupDups.2
Fuzzy Lookup DTSTransform.BestMatch.2
Import Column TxFileInserter.Inserter.2
Lookup DTSTransform.Lookup.2
Merge DTSTransform.Merge.2
Merge Join DTSTransform.MergeJoin.2
Multicast DTSTransform.Multicast.2
OLE DB Command DTSTransform.OLEDBCommand.2
OLE DB Destination DTSAdapter.OLEDBDestination.2
OLE DB Source DTSAdapter.OLEDBSource.2
Partition Processing MSMDPP.PXPipelineProcessPartition.2
Percentage Sampling DTSTransform.PctSampling.2
Performance Counters Source DataCollectorTransform.TxPerfCounters.1
Pivot DTSTransform.Pivot.2
Raw File Destination DTSAdapter.RawDestination.2
Raw File Source DTSAdapter.RawSource.2
Recordset Destination DTSAdapter.RecordsetDestination.2
RegexClean Konesans.Dts.Pipeline.RegexClean.RegexClean, Konesans.Dts.Pipeline.RegexClean, Version=2.0.0.0, Culture=neutral, PublicKeyToken=d1abe77e8a21353e
Row Count DTSTransform.RowCount.2
Row Count Plus Konesans.Dts.Pipeline.RowCountPlusTransform.RowCountPlusTransform, Konesans.Dts.Pipeline.RowCountPlusTransform, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b2ab4a111192992b
Row Number Konesans.Dts.Pipeline.RowNumberTransform.RowNumberTransform, Konesans.Dts.Pipeline.RowNumberTransform, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b2ab4a111192992b
Row Sampling DTSTransform.RowSampling.2
Script Component Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost, Microsoft.SqlServer.TxScript, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Slowly Changing Dimension DTSTransform.SCD.2
Sort DTSTransform.Sort.2
SQL Server Compact Destination Microsoft.SqlServer.Dts.Pipeline.SqlCEDestinationAdapter, Microsoft.SqlServer.SqlCEDest, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
SQL Server Destination DTSAdapter.SQLServerDestination.2
Term Extraction DTSTransform.TermExtraction.2
Term Lookup DTSTransform.TermLookup.2
Trash Destination Konesans.Dts.Pipeline.TrashDestination.Trash, Konesans.Dts.Pipeline.TrashDestination, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b8351fe7752642cc
TxTopQueries DataCollectorTransform.TxTopQueries.1
Union All DTSTransform.UnionAll.2
Unpivot DTSTransform.UnPivot.2
XML Source Microsoft.SqlServer.Dts.Pipeline.XmlSourceAdapter, Microsoft.SqlServer.XmlSrc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

Here is a simple console program that can be used to enumerate the pipeline components installed on your machine, and dumps out a list of all components like that above. You will need to add a reference to the Microsoft.SQLServer.ManagedDTS assembly.

using System;
using System.Diagnostics;
using Microsoft.SqlServer.Dts.Runtime;

public class Program
{
    static void Main(string[] args)
    {
        Application application = new Application();
        PipelineComponentInfos componentInfos = application.PipelineComponentInfos;
        foreach (PipelineComponentInfo componentInfo in componentInfos)
        {
            Debug.WriteLine(componentInfo.Name + "\t" + componentInfo.CreationName);
        }
        Console.Read();
    }
}

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

RecentComments

Comment RSS