Multi-database (source) loop - Parameterized load from multiple databases

This design pattern is an example of parameterized loop through and data load from multiple databases with the same base structure. The example is based on Dynamics 365 BC (NAV) databases as this includes parameterizing both databases and table names (prefixes).

Each code block has comments, and the complete example ETL script can be downloaded using the link the the ETL flow paragraph.

To get the correct code to script the creation of any source, the easiest way is to create a connection manually with Code Generation = Active, then copy the source connection code from the detailed script window and then finally deleting the manually created connection.

ETL Flow

Download: Create and load from multiple connections.improj

Script #1 - Create example table - list of databases

/*
The [TMP_Datbases] table contains information about each database to connect to.
The table is can be populated manually like in this example or by connecting to any other data source containing a list of database to read from.
*/

create table
[TMP_Databases]
(
DatabaseId string,
DatabaseName string,
Tableprefix string
)

insert into [TMP_Databases] (DatabaseId,DatabaseName,TablePrefix) values (1,'Dynamics NAV 2013 - Extended Demo Data - Denmark','CRONUS Danmark A_S$')

insert into [TMP_Databases] (DatabaseId,DatabaseName,TablePrefix) values (2,'Dynamics NAV 2013 - Extended Demo Data - Worldwide','CRONUS EXT International Ltd_$')

Script #2 - Create variables and temporaries

/*
Variables are created to hold temporary information to be used for each loop of the list of databases table ([TMP_Databases]).
*/

declare @DatabaseName as string
declare @TablePrefix as string
declare @DatabaseId as string
declare @TableName as string

Script #3 - While table loop

The while script element loops all records in the database list table and executes a script (Script #4) for each record.

WHILE [TMP_Databases].EOF=false

<Script #4>

MOVENEXT [TMP_Databases]
LOOP

Script #4 - Create connection to current database

Note! Server name, user name and password have been anonymized in the example code. If necessary, these properties can naturally be either hardcoded or parameterized as with database name in the example.

/*
For each row in the table containing the list of databases ([TMP_Databases]), variables are populated and used for connecting to the current database using a while table loop. If other parameterized information is needed, like table prefix for this example, this is also read to a variable. To ensure new login for each loop, the connection must be closed at the end of the while loop.
*/

set @DatabaseName = [TMP_Databases].[DatabaseName]
set @TablePrefix = [TMP_Databases].[TablePrefix]
set @DatabaseId = [TMP_Databases].[DatabaseId]

DATASOURCE [SQL Server] = SQLSERVER 'Persist Security Info=False;Data source=<servername>;Initial Catalog='+@DatabaseName+';User ID=<username>;Password=<password>'

Script #5 - Read all tables for current database

/*
Tables are loaded for the current database. Columns should be specifically named to ensure the same order and names of columns to be succesfully added to the destination table. It is possible to add an additional loop using a table containing column and tables names to parameterize this instead of "hardcoding" each table to load.
*/

set @TableName = @TablePrefix + 'Customer'
import [STAGE_Customer] = [SQL Server].{select '@@DatabaseId' as DatabaseId,[No_],[Name] from [@@TableName]}

set @TableName = @TablePrefix + 'Item'
import [STAGE_Item] = [SQL Server].{select '@@DatabaseId' as DatabaseId,[No_],[Description] from [@@TableName]}

Script #6 - Close current database

close [SQL Server]