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.




Array

One Response to “Dynamically Add Tables in SSIS Source Queries”

  1. Excellent Topic and articulated in a very easy manner, Maam.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |