SQL Server Integration Services – How to configure single Source and destination in Data flow to be used by multiple databases and flat files?

Hi Friends,

A few days back I came across a scenario where there were multiple flat files and the data were to be loaded into different databases. The good thing about the whole process was that the files were named in format


And the table structures were same in all the databases, also data structure in all the flat files was same.

Now suppose I have 3 databases viz.

  1. MyDB
  2. TestDb
  3. SchemaTest

And my file names are

  1. MyDB_data.txt
  2. TestDb_data.txt
  3. SchemaTest_data.txt

So, data from MyDB_data.txt, TestDb_data.txt, SchemaTest_data.txt would go in MyDB, TestDb, SchemaTest databases respectively.

Let’s start by creating tables

Execute the below scripts for all the three databases

CREATE TABLE [dbo].[multipledbinserts](
    [Id] [smallint] NULL,
    [Fname] [varchar](50) NULL,
    [Lname] [varchar](50) NULL

Now create all the text files with data


Once we are done with creating tables in databases and data in text files. Let’s start with configuring the Controls in SSIS. First take for each loop container and select for each file enumerator which will iterate over all the files.


Now create a variable of string type with scope of package. Set the initial value of variable as one of the flat file name. This variable will store the file names.


Now drag and drop a dataflow task inside the for each loop container and edit it.


Now add a flat file connection manager and set the path as one of the flat file.


Now, Select the flat file Conn. Manager and set its properties by pressing F4. Select expressions and select property Connection string. In the expression builder write the path concatenated with file name variable we set initially.

I am concatenating the extension also because while configuring for each loop container I checked on the option retrieve file name only.



Now, let’s add an ole db connection and configure it for multiple databases. Now select the Conn. Mngr. and press F4. In the properties select expression and then select Initial Catalog and get the database name by writing the expression as shown in image below.


So, we are done with configuration part. Now just add a flat file source and an ole db destination and set them with connection managers we just configured above.


Check the tables before executing the package.


Verify the table data after executing the package.



Again check for the data in tables.


Happy Learning!!!



Mridul Chandhok

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook


About Mridul Chandhok

MSBI Developer having 4 years of experience in Report Writing using SSRS. Writing ETL packages for both OLTP and DW databases Using SSIS with focus on package optimization. In free time I love to explore new stuff on SQL Server. Love Bicycling and Swimming. Die Hard Fan of Heavy Metal Music.

View all posts by Mridul Chandhok →

Leave a Reply

Your email address will not be published.