This Anchor Modeling SSIS example is brought to you by:
The first version of the demo was created by Bas and shared on AnchorModeling.com.
Tutorial Video by Bas
Advice you to watch it first 🙂
New version of the SSIS package
The fast performing package (final step in his tutorial) was rebuild by Clint and is described below:
To download the SSIS package and T-SQL scripts, click here.
The Anchor Model (AM)
The AM is about telephone calls, that includes two Anchors (Call and Phone), two Ties and three Attributes:
Anchor Model with mnemonics
Anchor Model without mnemonics
How to install
1) Create a new database called ‘AM_SSIS_Example‘
2) Create all AM-objects by executing the script: ‘Generated SQL Script by AM Online Modeler.sql‘
3) Generate source data by executing the script: ‘Create dbo_Calls table (source).sql‘
Give it a few minutes, it will generate 691.200 rows
The table dbo.Calls is already filled with demo data:
4) Create a Batch-table for metadata by executing the script: ‘Create BA_Batch table.sql‘
The database should now have a couple of tables (also Views, SPs and UDFs):
5) Open the SSIS package in BIDS/SSDT and execute the package
The SSIS package demos two scenarios:
#1 – Insert records by using the INSERT-trigger on view dbo.lPhone
#2 – Insert records directly into the Anchor and Attributes
(by using the fast load / bulk insert and parallel processing of the Attributes)
A Raw File Source is used to temporary store and quickly re-load the full dataset, located in ‘C:\Temp\AM_SSIS_Example_RAW_FILE‘. Important: make sure to manually create/verify this Raw File. You’ll be able to find it here:
Open the ‘Raw File Destination Editor‘-window (right-click and ‘Edit’) and verify the Raw File’s location:
6) Watch the show
7) Verify the results by executing the script: ‘Verify the results.sql‘
8) Increase the volume!
Would you like to re-process all data and perhaps with a lot more records? 😀
Truncate all tables by executing the script: ‘Truncate and delete all data.sql’
Now, drop the table dbo.Calls.
Execute the script ‘Create dbo_Calls table (source).sql‘ again, but this time increase the variable @EndDateTime with just a couple of days.