We are going to keep this as basic as possible, remember with all things “programming” there is more than one way to accomplish the same tasks. The data warehouses we normally build are your traditional enterprise data warehouses that use the three-tier architecture.
We identify our layers as:
- Presentation – Top-Tier: The top tier is a front-end client layer. Top tier is the tools and API that you connect and get data out from the data warehouse. It could be Query tools, reporting tools, managed query tools, Analysis tools and Data mining tools.
- Conformed – Middle-Tier: For a user, this application tier presents an abstracted view of the database. This layer also acts as a mediator between the end-user and the database.
- Primary –Bottom Tier: The database of the data warehouse servers as the bottom tier. It is usually a relational database system. Data is cleansed, transformed, and loaded into this layer using back-end tools.
Our three layers have one unique SSIS template each to extract, load, and transform the data. Each template includes the following actions.
- Connections checking
- Pre-audit
- ETL
- Post-audit
The image provided shows an example of our Conformed template. In the image, you will see the above actions. The ETL is done in the container labeled “Load Target from Staging”.
As discussed above, every SSIS template should consist of the following at a minimum.
Connections Checking
Checking every connection prior to going any further in the process help ensure that each source and target is available.
Pre-audit
In a pre-audit, we want to start the logging of the package. Within this we might capture variables such as, but not limited to PackageExecutionStartTime, ParentPackageExecutionKey, and TargetTableName. These are then written to a audit database which we can refer to when needed.
ETL
There is no doubt that this is the heart of the SSIS package. But wait, there is more standardization and auditing within. More on this later.
Post-audit
This is our final stage of the package. Here is where we will log to the audit database such items on package success and failures and target row counts. This container look very similar in structure as the "Start Audit Sequence" as seen in the image.
We Build Great SSIS Packages!
Ready To See What's Next?
Like Nothing You've Seen. Contact Us Today & Experience SSIS Standards.
Get in Touch