Dynamic OLE DB Source and Destination

Hi,

I am building SSIS for 3 different files that have identical
schema and mapping logic.

In my OLE DB Source (object name - "OLEDBSource_SourceTable")
Data Access mode is "Variable name".
As soon as I swithced to this Data Acces mode
it started to give me an error:

[OLEDBSource_SourceTable [1]] Warning: The external metadata column collection is out of synchronization
with the data source columns.

The column "DEAL_NUM" needs to be updated in the external metadata column collection.
The "external metadata column "DEAL_NUM_Flag" (34529)" needs to be removed
from the external metadata column collection.
The "external metadata column "recordID" (33740)"
needs to be removed from the external metadata column collection.Meta data CANNOT change. You cannot setup an OLE DB source and have columns mapped and then just change the underlying source table. How can you imagine that working?

Search this forum for dynamic source/destinations and you'll get plenty of conversations on this topic.

Bottom line, SSIS relies on meta data. If you change it, how do you expect it to operate?|||

Hi Riga,

To make the data source dynamic you need to make the ConnectionString dynamic.

Hope this helps,

Andy

|||Everything turned out to be much easier, gentlemen!
As soon as I set in my OLE DB Source Properties
"ValidateExternalData" to False
it starts working.
I guess when your source is a variable it should be this way?

Robert|||

Robert,

Could you please provide more details about your package? what is exactly variable on it? is the name of the table?

Phil is right, the number and data type of the columns cannot change in a dataflow. So, I guess that is not your case; but I am curious about your specific scenario.

|||I have a variable [SourceTable] that keeps a table name.
I am using it in my OLE DB Source Data Access Mode.
But with ValidateExternalMetadata=True it didn't work.
so I changed it to ValidateExternalMetadata=False
and it works now.

I don't know why Phil says it can not be done.
Even if I hold a table name in a variable
the bottom line is that all the tables have identical schema.

So as long as you set a default value of {SourceTable}
let's say to "Table1" it will work when
{SourceTable} changes to "Table2","Table3" and so on.

Robert