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

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.

Solution Walkthrough

I have three tables, having three common columns RowID and VersionNo.

1) Demo1 ( RowID , VersionNo)

2) Demo2 ( RowID , VersionNo)

3) Demo3 ( 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 destination table.

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

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 SQL task.

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

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.

]]>