THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is a Data Philosopher at Andy Leonard Consulting, an SSIS Trainer, Consultant, and developer; a Business Intelligence Markup Language (Biml) developer; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server Integration Services Design Patterns, and author of Managing Geeks - A Journey of Leading by Doing, and the Stairway to Integration Services.

SSIS Snack: Data Flow Source Adapters


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

Published Friday, March 19, 2010 8:00 AM by andyleonard
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



jamiet said:

I'd say you need 17 data paths, not 17 data flows.

January 16, 2012 6:22 AM

Leave a Comment


This Blog



Friend of Red Gate

My Company

Blog Roll

Contact Me


Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement