ELT process

What’s ETL again?

When designing the ETL process it’s good to think about the three fundamental things it needs to do:

• Extract data from the external data sources such as line-of-business systems, CRM systems, relational databases, web services, and SharePoint lists.
• Transform the data. This includes cleansing the data and converting it to a OLAP-friendly data model. The OLAP-friendly data model traditionally consists of dimension and fact tables in a star or snowflake schema and closely maps SSAS’s dimensional model (SSAS stands for SQL Server Analyses Services ).
• Load the data so that it can be quickly accessed by querying tools such as reports. In practice this implies processing SSAS cubes.

Staging :

There’s a quick and easy way to create staging tables from within SSIS. When you add a destination data source to a data flow, connect it to incoming data and then edit the destination’s properties, you will be asked to select a destination table. At that point you also have the option of creating a new table. Doing so will create a database table with all the right columns.