CreationName for SSIS 2005 and adding components programmatically

by Darren Green 29 Jan 2009 09:07

If you are building SSIS 2005 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 2008.

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.1"; 

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

CreationName Reference 

Aggregate DTSTransform.Aggregate.1
Audit DTSTransform.Lineage.1
Character Map DTSTransform.CharacterMap.1
Checksum Konesans.Dts.Pipeline.ChecksumTransform.ChecksumTransform, Konesans.Dts.Pipeline.ChecksumTransform, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b2ab4a111192992b
Conditional Split DTSTransform.ConditionalSplit.1
Copy Column DTSTransform.CopyMap.1
Data Conversion DTSTransform.DataConvert.1
Data Mining Model Training MSMDPP.PXPipelineProcessDM.1
Data Mining Query MSMDPP.PXPipelineDMQuery.1
DataReader Destination Microsoft.SqlServer.Dts.Pipeline.DataReaderDestinationAdapter, Microsoft.SqlServer.DataReaderDest, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
DataReader Source Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter, Microsoft.SqlServer.ADONETSrc, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Derived Column DTSTransform.DerivedColumn.1
DeUnicode DeUnicode.DeUnicode, DeUnicode, Version=1.0.0.0, Culture=neutral, PublicKeyToken=231268b283856821
Dimension Processing MSMDPP.PXPipelineProcessDimension.1
Excel Destination DTSAdapter.ExcelDestination.1
Excel Source DTSAdapter.ExcelSource.1
Export Column TxFileExtractor.Extractor.1
Flat File Destination DTSAdapter.FlatFileDestination.1
Flat File Source DTSAdapter.FlatFileSource.1
Fuzzy Grouping DTSTransform.GroupDups.1
Fuzzy Lookup DTSTransform.BestMatch.1
Import Column TxFileInserter.Inserter.1
Lookup DTSTransform.Lookup.1
Merge DTSTransform.Merge.1
Merge Join DTSTransform.MergeJoin.1
Multicast DTSTransform.Multicast.1
OLE DB Command DTSTransform.OLEDBCommand.1
OLE DB Destination DTSAdapter.OLEDBDestination.1
OLE DB Source DTSAdapter.OLEDBSource.1
Partition Processing MSMDPP.PXPipelineProcessPartition.1
Percentage Sampling DTSTransform.PctSampling.1
Pivot DTSTransform.Pivot.1
Raw File Destination DTSAdapter.RawDestination.1
Raw File Source DTSAdapter.RawSource.1
Recordset Destination DTSAdapter.RecordsetDestination.1
RegexClean Konesans.Dts.Pipeline.RegexClean.RegexClean, Konesans.Dts.Pipeline.RegexClean, Version=1.0.0.0, Culture=neutral, PublicKeyToken=d1abe77e8a21353e
Row Count DTSTransform.RowCount.1
Row Count Plus Konesans.Dts.Pipeline.RowCountPlusTransform.RowCountPlusTransform, Konesans.Dts.Pipeline.RowCountPlusTransform, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b2ab4a111192992b
Row Number Konesans.Dts.Pipeline.RowNumberTransform.RowNumberTransform, Konesans.Dts.Pipeline.RowNumberTransform, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b2ab4a111192992b
Row Sampling DTSTransform.RowSampling.1
Script Component Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost, Microsoft.SqlServer.TxScript, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Slowly Changing Dimension DTSTransform.SCD.1
Sort DTSTransform.Sort.1
SQL Server Compact Edition Destination Microsoft.SqlServer.Dts.Pipeline.SqlCEDestinationAdapter, Microsoft.SqlServer.SqlCEDest, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
SQL Server Destination DTSAdapter.SQLServerDestination.1
Term Extraction DTSTransform.TermExtraction.1
Term Lookup DTSTransform.TermLookup.1
Trash Destination Konesans.Dts.Pipeline.TrashDestination.Trash, Konesans.Dts.Pipeline.TrashDestination, Version=1.0.1.0, Culture=neutral, PublicKeyToken=b8351fe7752642cc
Union All DTSTransform.UnionAll.1
Unpivot DTSTransform.UnPivot.1
XML Source Microsoft.SqlServer.Dts.Pipeline.XmlSourceAdapter, Microsoft.SqlServer.XmlSrc, Version=9.0.242.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();
    }
}

Comments (1) -

6/19/2009 10:42:33 PM #

Narayan

Very helpful post - exactly what I was looking for, thank you!

Narayan United States

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

RecentComments

Comment RSS