I creted this presentation for collegues after implementing partitioning in an ETL Project, taking execution time down from eight hours to 20 minutes. It later became a SQL Saturday presentation for SQL Saturday #433 in Gothenburg. It’s been tweaked on and off ever since. First upload of presentation material to this site is from GroupBy Conference October 9th 2019.
Download the presentation and demos from here.
Author
Magnus Ahlkvist, Transmokopter SQL AB.
magnus@tsql.nu
twitter: @transmokopter
www.tsql.nu
magnus@tsql.nu
twitter: @transmokopter
www.tsql.nu
Prerequisites
Adventureworks 2014 installed to run setup scripts
Installation and execution
First run Setup-script. Set the SQLCMD-variable to “1” for initial setup. values “2” and “3” are used to create a couple of indexes (one with “2” and more with “3”).
Then open and tweak connection managers in SSIS-project.
Deploy SSIS-project to SSISDB.
Run the “Create data”-package, either from VS or from SSISDB. Easiest is to run the relevant code in “Execute SSIS package.sql” (after some tweaking of paths).
Now you’re ready. There’s some back and forth between setup-script and executing the different versions of the packages. SSIS packages should be completely dynamic,
so that creating new indexes on the Sales-table should be picked up automagically by the Execute SQL-tasks.
Deploy SSIS-project to SSISDB.
Run the “Create data”-package, either from VS or from SSISDB. Easiest is to run the relevant code in “Execute SSIS package.sql” (after some tweaking of paths).
Now you’re ready. There’s some back and forth between setup-script and executing the different versions of the packages. SSIS packages should be completely dynamic,
so that creating new indexes on the Sales-table should be picked up automagically by the Execute SQL-tasks.