0

I am working on moving prod data to dev. I have a table on the dev side that has a list of tables and optionally a column name that is a date column to restrict the data tranferred to a specific date range. I set up a for each loop to iterate through each table and move data from prod to dev. There are two dataflow tasks, one executed if there is a date column, and one for when there isn't. If there is a date column the source component of the dataflow task uses a query variable, if not the source uses a table from a variable. The delay validation property of each dataflow is set to true. The dataflow source component is OLEDB with the validate metadata property set to false. The dataflow destination is table from variable and again the validate meta data property is set to false. In the first iteration of the dataflow task when there is a date column it transfers the data properly. On the second iteration it updates the metadata for the source correctly but not the for the destination and fails. In the first iteration of the dataflow task when there is no date column (source is set to table variable) itbehaves in the same manner as the second iteration of the date column case. How do force the destination component to dynamically update the metadata for each iteration?

Thursty
  • 31
  • 3
  • Why is meta data being updated? At the beginning of the question you said there were two different dataflows. Why would you need to update meta data in a dataflow on a second iteration? – Tab Alleman Jun 04 '18 at 17:57
  • 1
    Possible duplicate of [Meta Data refresh while looping through tables in SSIS](https://stackoverflow.com/questions/45081957/meta-data-refresh-while-looping-through-tables-in-ssis) – Tab Alleman Jun 04 '18 at 18:01
  • It's a different table on the second iteration. If I read your reply on the thread you linked correctly, the table variable access method in the data flow is only useful if the metadata is identical for every table that the variable could point to. – Thursty Jun 04 '18 at 18:11
  • 1
    That's correct, you can't loop through tables that have different metadata and use the same dataflow. – Tab Alleman Jun 04 '18 at 18:52
  • @Thursty consider that the metadata can be updated, how you will do columns mapping in real-time??!! I agree with TabAlleman you cannot do this if metadata is not identical. – Hadi Jun 05 '18 at 01:01

0 Answers0