CreationName for SSIS 2008 and adding components programmatically
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();
}
}
