Attached blog indicates how one can convert highly complex sql queries used for loading the Data warehouse into SSIS code.

 End client requirement:- the end client desired that the sql queries used to load the datawarehouse be converted into SSIS code for better auditing purposes and better error logging purposes. The graphical and intuitive visuals from SSIS shall better help manage the complex sql code.

The input sql code was converted to cte(or common table expressions). These common table expressions formed the procedure that needed to be converted into SSIS jobs.

The data flow that was developed in SSIS was a procedure with multiple common table expressions.

     Step 1:

·         Understand the joins between the tables and the required columns for developing the required dataflow.

·         Develop the code in SSIS for various joins as below:

·         Pick up the relevant columns from ‘merge join’ after each join transformation.

           

SSIS input:

·         Identify any filter parameters: I have used the conditional split for the filter parameters.

SSIS package:-

·         Identify any calculated columns.

        SSIS package implementation: derived column.

·         Aggregation with required group by parameters:

      SQL implementation:

Finally insertion into the target table.

In this manner the ETL team converted complex sql code into SSIS code for easy maintainability.

Website: www.mnnbi.com

Blog links: https://mndatasolutionsindia.blogspot.com/

Linked in profile: https://www.linkedin.com/in/mn-business-intelligence-53ba285b/

Email: resources@mnnbi.com;apoorv@mnnbi.com