Introduction
Configuring a Source Adapter in a Data Flow Task couples (binds) the Data Flow to an external schema. This has implications for dynamic data loads.
“Why Can’t I…?”
I’m often asked a question similar to the following: “I have 17 flat files with different schemas that I want to load to the same destination database – how many Data Flow Tasks do I need?” I reply “17 different schemas? That’s easy, you need 17 Data Flow Tasks.”
In his book Microsoft SQL Server 2005 Integration Services, Kirk Haselden (Blog) includes a blurb about “long discussions” regarding binding the data flow pipeline to the source metadata in this manner (Note, p. 539). The decision to tightly couple the source schema and the Data Flow was largely driven by the “Move Large Amounts of Data” use case.
“What Can I Do?”
You can load data from 17 files, as long as they have the same schema. There are advanced techniquues for loading data dynamically, but they are beyond the scope of this post.
:{> Andy
I’d say you need 17 data paths, not 17 data flows.