locked
SSIS Dynamic Foreach Loop problem... RRS feed

  • Question

  • Afternoon all,

    I am currently writing a dynamic data load process using SSIS, whereby I store the names of the tables I want to load into an object (Execute SQL Task), and then using a Data Flow Task within a Foreach Loop, connect to the Source Server and load the Data into the Target Server. I am currently having trouble bypassing the metadata differences on each pass of the loop, as the column mappings change for each table it is trying to process. I have tried ignoring the meta data validation settings within the Advance Properties of the source and target connection within the Data Flow Task. I have also tried to delay the validation between each pass to no avail.

    Firstly, is it possible to do this using different table structures with different metadata out of the box? (SQL Server 2012) I have setup configurations previously using tables with the same schema at multiple sources into one single target, but not where on each iteration of the loop, the table metadata changes at source and destination. I have found conflicting information on Google, where some say it's possible (but are using the same method I am using), and others who say it isn't possible.

    Advice much appreciated in advance!

    Cheers!

    Edit:

    The external columns for <Target> are out of synchronization with the data source columns.

    • Edited by GhostfaceKill4h Tuesday, January 5, 2016 2:35 PM Added exact error message
    Tuesday, January 5, 2016 2:29 PM

Answers

  • Hi

    Unfortunately this is not possible using the data flow component as the metadata for this component is determined at build time and not at run time. 

    If you have a limited number of file formats to deal with then it may be easiest to build a loop for each file type. 

    Wednesday, January 6, 2016 7:41 AM
  • As your metadata is changing your best bet in this case would be to use script task inside the loop and generate data flow dynamically based on your table metadata.

    See the logic explained here

    http://www.selectsifiso.net/programmatically-create-data-flow-task-in-ssis-package-using-c/

    This will ensure metadata gets refreshed based on your table source for each data flow and you can choose the corresponding destination to transfer the data across.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, January 6, 2016 8:01 AM

All replies

  • Hi

    Unfortunately this is not possible using the data flow component as the metadata for this component is determined at build time and not at run time. 

    If you have a limited number of file formats to deal with then it may be easiest to build a loop for each file type. 

    Wednesday, January 6, 2016 7:41 AM
  • As your metadata is changing your best bet in this case would be to use script task inside the loop and generate data flow dynamically based on your table metadata.

    See the logic explained here

    http://www.selectsifiso.net/programmatically-create-data-flow-task-in-ssis-package-using-c/

    This will ensure metadata gets refreshed based on your table source for each data flow and you can choose the corresponding destination to transfer the data across.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, January 6, 2016 8:01 AM