Passing dynamic table to ADO.NET Destination in SSIS

0
votes

I'm having a problem with my dynamic table name in ADO.NET Destination. The control flow is:

  1. SQL Query that bring me all the tables I need from an OLE DB connection.
  2. Foreach loop that iterates those tables.
  3. Inside the loop I have data flow that move all the data from the OLE DB to ADO.NET connection.

In the data flow properties I have declared the ADO NET Destination TableOrViewName expression to match the table I need on every iterate.

It works for the first iterate but for the second it fails because the destination table is not "updated" it still looking for the previous table's columns.

Any suggestions? I have already debugged it and the second iterate table name is correct.

1
sqlssisado.netdataflowflow
Do all the table share the same "shape" same columns, same data types? Otherwise, this approach does not work with SSIS – billinkc
Nope. every table has its own columns. But the source table changes too so it always should match. anyway, is there any other way to transfer the data without do it manually for every table? – Roee Cohen

1 Answers

0
votes

That's now how the SSIS dataflow task works.

The SSIS Dataflow works on the principal that it's an in-memory ETL engine. To keep things in memory, it forms a design-time contract between the source and destination that specifies this many columns, these data types.

You can dynamically swap out the source and destination connection attributes (read from server2 database B, write to Server 12, database Z) but changing the shape of the data (6 columns instead of 7, all ints instead of strings, etc) can't happen at run time. Trying to do it will cause a run-time violation with the ever populate error VS_NEEDSNEWMETADATA

If you have a relatively static list of tables, your best bet is to create custom data flows for each. My personal preference is to automate that task using Biml. SO Question -> ssis best practice to load N tables from Source to Target Server

And a slightly better implementation of that pattern is on my blog as Biml - Replicate-o-matic The only difference between the two linked solutions is that I use OLE DB connection and components so it's a small matter to swap out the ADO.NET equivalent.