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

[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.

MyDB
TestDb
SchemaTest

And my file names are

MyDB_data.txt
TestDb_data.txt
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
)
GO

Now create all the text files with data

1_SQL_Server_How_to_configure_single_Source_and_destination_in_Data_flow_to_be_used

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.

2_SQL_Server_How_to_configure_single_Source_and_destination_in_Data_flow_to_be_used

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.

3_SQL_Server_How_to_configure_single_Source_and_destination_in_Data_flow_to_be_used

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

4_SQL_Server_How_to_configure_single_Source_and_destination_in_Data_flow_to_be_used

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

5_SQL_Server_How_to_configure_single_Source_and_destination_in_Data_flow_to_be_used

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.

6_SQL_Server_How_to_configure_single_Source_and_destination_in_Data_flow_to_be_used

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.

7_SQL_Server_How_to_configure_single_Source_and_destination_in_Data_flow_to_be_used

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.

8_SQL_Server_How_to_configure_single_Source_and_destination_in_Data_flow_to_be_used