Aren’t we all looking for ways to increase speed and efficiency with SSIS? Especially when it comes to our ETL projects. The Dimension Merge SCD and UPSERT Destination components within Task Factory from Pragmatic Works already do the heavy lifting for inserting and updating data, bringing an unparalleled speed increase to ETL executions and allowing us to tackle millions, if not billions, of items at a fraction of the speed of their native SSIS counterparts.
However, if we go ‘under the hood’ of these components and add some customization, we can ramp up the speed even more. We can use a memory optimized mode inside of the Dimension Merge SCD which uses less memory pressure and increases performance. And for the UPSERT we can opt for MERGE statements instead of JOINS (if we’re using 2008 or higher of course!) to achieve lightning fast executions!
In this demo, I will show you how quick and easy it is to customize these 2 popular components:
- To begin, I go into Dimension Merge Slowly Changing Dimension and I go into my performance tab. I have checked off the Use Memory Optimized mode.
- To get the speed increase using this mode, I must make sure that my sources are both sorted.
- To do this, I go into my Existing Database and open the Advanced Editor.
- Here, I go to the Input and Output Properties and check that my ‘Source Output Is Sorted’ is set to True in Common Properties.
- I also needed to set up a SQL statement in order to sort. In my SQL statement, I select the appropriate columns from the table, order them by the business keys and collate them.
- Now I go into Advanced Editor to look at my Input and Output Properties to confirm that it’s properly sorted using the 2 business keys I selected.
- This will increase the speed of the Dimension Merge Slowly Changing Dimension.
- Now for the Upsert. One of the many advances in SQL Server is the usage of MERGE Statements rather than standard JOIN statements. Using MERGE statements on my Upsert Destinations is a huge speed increase.
- And using Task Factory’s Upsert tool compared to row by row updates can increase the speed of insert and updates by 3,000%! Check out the blog on that here.
So, there you have it, an easy (and super-fast) way to optimize the Dimension Merge Slowly Changing Dimension and the Upsert Destination components. Task Factory has over 65 time-saving components to achieve that increase in speed and efficiency that we’re all looking for. To learn more, or to download a free trial, visit our website at pragmaticworks.com.