Microsoft introduced the BI Tools for ETL and Reporting services from SQL server 2005 edition. Here SSIS will help us to load any data like its Tables, Flat Files, Comma delimited files, Spreadsheet files into Datawarehouse server.
With the help of SSIS, Shasta did the data cleaning, mapping and applied SCD type2 logic, then we push to them. While loading we tracking the number of rows added new, appended count and the deletion count in a separate table for future tracking. The Error logging mechanism did in the ways of from email notifications, another is from SMS popup and table store the logging of Packages how It the error occurred and where its. So any end user understand the error they can fix automatically in the ETL issues.
We automate all the jobs through automating scheduling this will run based on created frequency tables ETL will run 16times a day and Non NRT will run daily basis before their AM time. We supplied the ETL data into WebApps, Report builder and Portal using the SSIS tool.
SSIS Features
For more complex processes, particularly those that involve heavy transforms, slowly changing dimensions, data mining lookups, etc.
- First, it manages memory very efficiently, which can result in big performance improvements compared to T-SQL alone.
- Second, the graphical interface lets you build large, complex and reliable transforms much more easily than hand-crafted T-SQL.
- Third, SSIS lets you more easily interact with additional external sources, which can be very handy for things like data cleansing.
- Fourth, Data can be loaded in parallel to many varied destinations.
- And Fifth,Easier to maintain and package configuration.
We are SSIS components to built data warehouse in sql server using ETL Process. The data can be extracted from various external sources like Oracle,Mysql,Excel,CSV and other various files. We are maintaining the huge amount of data on the daily basis.
We used to built-in tasks and transformation to solve complex queries to handle the business requirements. The error handling and logging are maintained separately in tables inside the sql server. The sql jobs are created to invoke the SSIS package to run. The complex queries to be built for reporting purposes to shown in the excel or SSRS Reporting tool.
For mailing, we use c#.net code in script task. if ETL Fails, the Mail has been sent to all the respective users as well as via SMS. We are maintaining the historical records for each tables inside the data warehouse using SCD type2 concept.
