locked
How to make dynamic metadata columns in foreach loop for different table structure? RRS feed

  • Question

  • Right now, i had added different data flow task for different table structure. Is there anyways i can make dynamic column mapping in ole db source and ole db destination column???
    Friday, September 20, 2013 2:24 PM

Answers

All replies

  • You must use programmatic approach: either generate packages in pure .net code or use an API as EzAPI (not for SSIS 2012).

    Arthur My Blog

    • Proposed as answer by Mike Yin Tuesday, September 24, 2013 1:15 PM
    • Marked as answer by Mike Yin Friday, October 4, 2013 2:17 AM
    Friday, September 20, 2013 3:02 PM
  • If you can use third-party solutions, check the commercial COZYROC Data Flow Task Plus. It is an extension of the standard Data Flow Task and supports dynamic metadata at runtime. You can process all different table structures with only one data flow. No programming skills are required.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Saturday, September 21, 2013 2:59 PM
  • Have you considered using a variable to hold an INSERT SQL Query and running it in an Execute SQL task?  You can do that dynamically in SSIS.  Here is a post on a similar situation where the tables were the exact same structure. Modify this method to suit your purposes, using an Execute SQL task instead of a Data Flow task.  You would want to pull the multiple SELECT statements into an object variable and then loop through them one at a time, executing each statement.

    http://dataqueen.unlimitedviz.com/2013/09/loop-through-dynamic-sql-statements-in-ssis/

    I hope this helps you.


    Martina White

    • Proposed as answer by Mike Yin Tuesday, September 24, 2013 1:15 PM
    • Marked as answer by Mike Yin Friday, October 4, 2013 2:17 AM
    Sunday, September 22, 2013 11:08 PM
  • If metadata is dynamic, cosider using pure programing using C#/Java instead of using SSIS.
    • Proposed as answer by Mike Yin Tuesday, September 24, 2013 1:15 PM
    • Marked as answer by Mike Yin Friday, October 4, 2013 2:17 AM
    Monday, September 23, 2013 5:27 AM
  • In such cases you need to create dataflow task dynamically, so that you can change your source and destination metadata dynamically.

    See below link to understand how to create the dataflow task programatically.

    http://technet.microsoft.com/en-us/library/ms135997.aspx

    http://technet.microsoft.com/en-us/library/ms135932.aspx

    Regards

    Naveen

    • Proposed as answer by Mike Yin Tuesday, September 24, 2013 1:15 PM
    • Marked as answer by Mike Yin Friday, October 4, 2013 2:17 AM
    Monday, September 23, 2013 7:10 AM