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/
No comments:
Post a Comment