Dynamically Add Tables in SSIS Source Queries
I recently encountered a situation where I was using a
source SQL Command inside an OLE DB Source in SSIS DFT and a simple query using
three tables. The query was relatively simple – just calling the common columns from the
tables using ‘UNION ALL’. The package was set and in the production
Subsequently, there was an enhancement in the module and one new
extra table was added with the same common columns. I needed to
refer this new table in the same way as the three previous tables, using the
common columns with ‘UNION ALL’. Due to this, I needed to go and change the Package
source query in the sql command again.
This procedure worked perfectly well but for additional enhancements I wanted a more generalized solution. After much deliberation and testing I finally ended up with a solution using variables.
I have three tables, having three common columns RowID and
( RowID , VersionNo)
( RowID , VersionNo)
( RowID , VersionNo)
The below script will create these tables :
CREATE TABLE demo1 (RowID INT, Versionno INT, demo1comments VARCHAR(50)) CREATE TABLE demo2 (RowID INT, Versionno INT, demo2comments VARCHAR(50)) CREATE TABLE demo3 (RowID INT, Versionno INT, demo3comments VARCHAR(50))
As shown in the script, all the three tables have two common
columns(RowId and Versionno) and one column for the table. I have to get all
the common columns Rowid and VersionNo from these table and insert into the
Now, we can insert some data into these tables :
INSERT INTO demo1 SELECT 1,1,'demo1' UNION ALL SELECT 1,3,'demo1' UNION ALL SELECT 1,8,'demo1' INSERT INTO demo2 SELECT 1,2,'demo2' UNION ALL SELECT 1,5,'demo2' UNION ALL SELECT 1,9,'demo2' INSERT INTO demo3 SELECT 1,4,'demo3' UNION ALL SELECT 1,6,'demo3' UNION ALL SELECT 1,7,'demo3'
The aim behind the column RowId and VersionNo, is THAT RowID
representS a complete row or a batch which is further divided into pieces with
VersionNo. I want to combine and consolidate everything into a single table
using SSIS which can represent a complete row with all the versions.
As per the demo data inserted, the three tables have one
RowID and 9 versions split across all the tables.
Now, begins the main solution. My first step was to create a
single table having all the table names used in the source query listed.
I will call this table ‘AllDemoTables’. The below script will create this table :
CREATE TABLE AllDemoTables ( id INT identity(1,1),demotablenames VARCHAR(50)) INSERT INTO AllDemoTables SELECT 'demo1' UNION ALL SELECT 'demo2' UNION ALL SELECT 'demo3'
The next step is to use an Execute SQL Task in the package,
which will execute a query that returns a SingleRow resultset using the table
Use the query below in the SQL Statement of Execute SQL
Task. This query is a generic query which will select the common columns from
all the tables inside the ‘AllDemoTables’ and joining them with ‘UNION ALL’.
DECLARE @qry VARCHAR(max) SELECT @qry=COALESCE(@qry,'') +CASE WHEN ID = (SELECT MAX(id) FROM demo) THEN ' '+CHAR(13)+'SELECT Rowid, Versionno FROM '+demotablenames ELSE ' '+CHAR(13)+'SELECT Rowid, Versionno FROM '+demotablenames+' UNION ALL' END FROM AllDemoTables SELECT @QRY
The output of this query is shown below:
When this query is passed in Execute SQL Task, set the
Result set as Single row and capture it in a String variable :
Once this is completed add a Data Flow task below the Execute
In the OLEDB source refer to the resultset variable (qry) from
Execute SQL task. For the first time, hardcode the two columns and connect the
OLEDB destination. After all the settings, execute the package.
After the package execution is successful, you can see that
9 versions for a single row from all the three tables are successfully
inserted in the destination table and the table data is shown below.
Now, let us add a new table in the query.
CREATE TABLE demo4 (RowID INT, Versionno INT, demo4comments VARCHAR(50)) INSERT INTO demo4 SELECT 1,10,'demo4' UNION ALL SELECT 1,11,'demo4' INSERT INTO AllDemoTables SELECT 'demo4'
I have added a new table demo4, which needs to be referred
in the query. Repeating the above steps, first add
the new table name in our ‘AllDemoTables’ table. The changes will be
automatically taken by the package, as it is referring to AllDemoTables and
building the query from the table names contained in that table.
Now, we can execute the package again. This time instead of
nine rows it should transfer 11 rows. Last two Versionno are from table demo4.
Finally, we can check the data in the destination table
All the 11 versions have been successfully inserted into the
destination table without changing the package.
There can be numerous solutions to this problem but this method was easy to implement and proved very useful in adding continuous enhancements to the
product which required adding the new tables.