Faster SSIS
summary
Shows concrete SSIS fixes: moving blob temp storage path off C speeds a demo load from ~2,300 to ~2,800 rows/sec and omitting XML/blob columns jumps it to ~67,000 rows/sec (≈30x). Uses AdventureWorks and SQL Server, recommends pulling blob data separately. Uses hashbytes(sha1) with a left join to select only new/changed rows (1,993 vs 19,972 read rows) for much lower IO. Replaces slow ODBC destination with a script component poor-man bulk insert to push MySQL from ~9 to ~800–1,100 rows/sec.
AI-written summary · about
chapters (10)
- 0:00Start
- 2:30Data Wrangling
- 8:53Binary Large Objects
- 15:40Blob Temp Storage Path
- 19:55Property Override
- 21:44Incremental Loads
- 23:41Populate Blob Columns
- 43:56Replacing Functionality in a Data Flow with a Script Component
- 50:33Odbc Connection
- 57:29C Sharp Primer
related talks
46mETL Confessions
50mDeep {sky}diving into Data Factory in Microsoft Fabric
48mOptimal Data Loads
25mSpeed Wins: The Basics Of How To Push More Queries Through Each CPU Core
50mSQL 2022 and S3 – Dell’s overview of consuming object data via Microsoft SQL
25mData Demons: Unleashing Chaos or Driving Performance?
08mHow to evaluate your data migration and transfer use cases
01h 06mBest practices when accessing Big Data or any other data!