locked
To get names of Data Flow Components present in a Data Flow Task RRS feed

  • Question

  • Please help me with a vb.net code which will help me extract names of Data Flow Components present in a Data Flow Task.

    thanks.

    Friday, February 19, 2010 6:28 AM

Answers

All replies

  • Friday, February 19, 2010 1:45 PM
  • Hello,
    There is some C# code here that might help if you want to convert it to VB.Net:

    SSIS: Iterate over a package programatically

    -Jamie
    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Friday, February 19, 2010 3:06 PM
  • Thanks a lot Jamie and SQLUSA.... I converted the code to vb.net and it worked perfectly fine.
    here is the code I have used...

    For

    Each oExecutable In oPackage.Executables

     

    If TypeOf oExecutable Is TaskHost Then

    oTaskHost =

    CType(oExecutable, TaskHost)

     

    If TypeOf oTaskHost.InnerObject Is Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe Then

     

    Dim pipe As MainPipe = CType(oTaskHost.InnerObject, MainPipe)

     

    Dim comp As IDTSComponentMetaData90

     

    For Each comp In pipe.ComponentMetaDataCollection

    Debug.Print(

    "Component Name : " & comp.Name)

     

    Next

     

    End If


    Thank you once again....

    Monday, February 22, 2010 8:27 AM
  • Also, I would like to know which option would give the type of the Data Flow Component.

    Further,

    Please help me with a vb.net code which will help me extract names of Components present in a Sequence Container.
    Monday, February 22, 2010 9:07 AM
  • Also, I would like to know which option would give the type of the Data Flow Component.
    The property that uniquely identifies the type of a component is IDTSComponentMetaData100.ComponentClassID Property

    Further,

    Please help me with a vb.net code which will help me extract names of Components present in a Sequence Container.
    Not sure what you mean. Components reside inside of a dataflow, not inside a sequence container per se.

    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Monday, February 22, 2010 10:37 AM
  • Hi Jamie,

    Thank you for your answer. IDTSComponentMetaData100.ComponentClassID Property gives the code for the type. Could you please give me the property which will give me the name of the type for ex: "Lookup Transformation , OLE DB Destination " etc.

    Also, by components of a Sequence Container, I meant the contents inside it. For ex. if we add a Data Flow task or Execute SQL task to a Sequence, I need to get the name and type of the tasks. Please help me on this.

    Thanks once again.
    Tuesday, February 23, 2010 4:00 AM
  • Hi Jamie,

    Thank you for your answer. IDTSComponentMetaData100.ComponentClassID Property gives the code for the type. Could you please give me the property which will give me the name of the type for ex: "Lookup Transformation , OLE DB Destination " etc.
    I thought there wasn't such a thing but I think I've just found something which might help you:
    Discovering Data Flow Components Programmatically

    It uses a class called PipelineComponentInfo which, in turn, has a PipelineComponentInfo.CreationName Property .
    Also, by components of a Sequence Container, I meant the contents inside it. For ex. if we add a Data Flow task or Execute SQL task to a Sequence, I need to get the name and type of the tasks. Please help me on this.

    Thanks once again.
    Ah OK. You need to make sure you're using the correct nomencalture. "Component" generally refers to something inside the dataflow.
    Check this out:

    SSIS: Iterate over a package programatically

    Actually, looking at it that might just give you everything that you need, components included :)

    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    • Proposed as answer by Jamie Thomson Tuesday, February 23, 2010 5:10 AM
    • Marked as answer by Zongqing Li Thursday, February 25, 2010 7:23 AM
    Tuesday, February 23, 2010 5:10 AM
  • Hi Jamie,

    I had gone through PipelineComponentInfo.CreationName Property earlier and what it says is
    "You can programmatically discover the data flow sources, transformations, and destinations that are installed and available on the local computer. " i.e. it gives the list of names all data flow components available for use and not specific to the data flow task under consideration. How can PipelineComponentInfo be related to a specific Data Flow task?

    Further, the code provided by you for my second question --- how can this be extended to get the names of contents of the Sequence Container?

    thanks.
    Tuesday, February 23, 2010 5:21 AM
  • Hi Jamie,

    I had gone through PipelineComponentInfo.CreationName Property earlier and what it says is
    "You can programmatically discover the data flow sources, transformations, and destinations that are installed and available on the local computer. " i.e. it gives the list of names all data flow components available for use and not specific to the data flow task under consideration. How can PipelineComponentInfo be related to a specific Data Flow task?
    I don't know, I was hoping that you might be able to join the dots together. I suspect that there is a way of combining IDtsComponentMetadata100 & PipelineComponentInfo - hopefully you can figure it out.

    Further, the code provided by you for my second question --- how can this be extended to get the names of contents of the Sequence Container?

    thanks.
    A Sequence container is, in itself, an Executable. Hence, adapt the code I provided to be recursive whenever a Sequence/ForEach/ForEach Loop container is encountered.

    regards
    Jamie

    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Tuesday, February 23, 2010 5:30 AM