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

[db_name]_data.txt

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

1 2 3 4 5 6

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

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!!!