none
Dynamically modifying the data flow task RRS feed

  • Question



  • In my SSIS package, I have a data flow task
    I am loading a CSV file into a SQL table (OLE DB destination)

    I have a couple of CSV files to be loaded
    Instead of creating a separate task for each file , can I combine them together into a single task

    I was thinking about using a ForEach container

    This approach works if the number of columns in all the CSV files is same
    But in my case it is not

    So what I want is a script task that dynamically modifies the mappings

    Can I do this?

    I was browsing the net and I found certain code which uses IDTSExternalMetadataColumn90, MapOutputColumn etc.
    But the code was creating a new package for each mapping

    I couldn't understand the code

    So can you please help me with this?

    My script task should modify the mappings in my data flow task
    For e.g.
    If  I have 3 columns in my CSV and 3 columns in DB, they should be mapped in the same order


    P.S. I do not want to use configurations
    Tuesday, March 11, 2008 4:29 AM

Answers

  • If the file formats differ then there is no way to get one Data Flow to load them. The file format I considered part of the structure of a SSIS Data Flow task/package and that cannot be changed at run-time. The ForEach loop is great but requires the files o be the same. As an aside there is also the multi- flat file source adapter that could b used in a similar scenario, but either way you are stuck.

     

    A Script Task cannot help as you just cannot modify the structure of the package

     

    The code you mention was probably building a new package, although it may have been or modifying an existing one, but it will have been external. You can write code that builds a package, and that is what you would have to do.

     

     

    1 – Build packages by hand, easy enough to do, but labour intensive.

     

    2 – Write a package generator utility, but you need an easy way of inferring the source columns and their relationship to a destination. This is often much harder than it sounds, from an implementation perspective, human vs machine intelligence. This can be in VB.Net or another .Net language, and could actually be hosted within a Script task in another package, but that is a bit of a pain to develop code in compared to Visual Studio proper.

     

    3 – Use another tool. BCP can be quite useful for example.

    Tuesday, March 11, 2008 8:47 AM
    Moderator

All replies

  • If the file formats differ then there is no way to get one Data Flow to load them. The file format I considered part of the structure of a SSIS Data Flow task/package and that cannot be changed at run-time. The ForEach loop is great but requires the files o be the same. As an aside there is also the multi- flat file source adapter that could b used in a similar scenario, but either way you are stuck.

     

    A Script Task cannot help as you just cannot modify the structure of the package

     

    The code you mention was probably building a new package, although it may have been or modifying an existing one, but it will have been external. You can write code that builds a package, and that is what you would have to do.

     

     

    1 – Build packages by hand, easy enough to do, but labour intensive.

     

    2 – Write a package generator utility, but you need an easy way of inferring the source columns and their relationship to a destination. This is often much harder than it sounds, from an implementation perspective, human vs machine intelligence. This can be in VB.Net or another .Net language, and could actually be hosted within a Script task in another package, but that is a bit of a pain to develop code in compared to Visual Studio proper.

     

    3 – Use another tool. BCP can be quite useful for example.

    Tuesday, March 11, 2008 8:47 AM
    Moderator
  •  Vijay Malhotra wrote:

    So what I want is a script task that dynamically modifies the mappings

    Can I do this?

     

    No, you cannot.

     

    The SSIS platform was built with stability as a major design goal. Because of this, the object model does not allow any package component to modify any other package component.

     

    You can use the object model (and the classes and interfaces you mentioned) to build or modify the data flow mappings from an external program that modifies a non-running package (a package preprocessor, if you will) but you cannot do the same thing from within a running package.

    Tuesday, March 11, 2008 12:12 PM
    Moderator
  •  DarrenSQLIS wrote:

    2 – Write a package generator utility, but you need an easy way of inferring the source columns and their relationship to a destination. This is often much harder than it sounds, from an implementation perspective, human vs machine intelligence. This can be in VB.Net or another .Net language, and could actually be hosted within a Script task in another package, but that is a bit of a pain to develop code in compared to Visual Studio proper.

     

    Now that is quite the understatement, Darren!

     

    Have you ever seen a well-implemented package generator with source code available online? If you have, I would love it if you could post the link on these forums.

    Tuesday, March 11, 2008 12:15 PM
    Moderator
  • If taking option one, you can start by having a package template with common logic, tasks, standards (logging, error handling, etc); that would save you some time and adds consistency to the solution.

     

    Tuesday, March 11, 2008 12:22 PM
    Moderator