Looking for ways to increase speed and performance of SSIS? Aren’t we all. That’s where Task Factory from Pragmatic Works comes in. In a new series, called Fine Tuning Task Factory, we’ll take a look at some of the more popular components and show you ways to integrate them into your SSIS environment.
In this first video/blog, we’ll look at the Upsert Destination component. The Upsert Destination is one of the most popular tools in Task Factory because it is an excellent replacement to the OLE DB Command, which is clunky and slow, and the other alternative, writing long complicated queries, isn’t time efficient.
The Upsert Destination allows us to perform bulk inserts and updates and all it takes is a simple configuration and a few check boxes.
Again, while OLE DB Command performs inserts and updates natively in SSIS, as your business grows and you’re looking to scale out to millions, possibly billions, of rows in tables instead of thousands, OLE DB is not going to work. Our Upsert component can do batches of rows 10,000 at a time. Task Factory is made to improve on the simple drag and drop methodology of SSIS by increasing ETL speed and lessening the amount of time developing.
Let’s look at how to deal with hanging in our Upsert Destination:
- When we have multiple users going through and sending information to a table inside of an enterprise environment, locking can occur. When locking happens on a table, our component will hang, causing the package to never complete executing!
- Here’s where we can highlight the ease of use of Task Factory.
- All we need to do is open the Upsert component and on the Advanced tab, check the option for ‘Turn Off Interval Transaction.’
- By checking this we circumvent that table lock scenario, so that Upsert can continue and never sit there and spin without ever completing. It’s that simple.
Task Factory has over 60 components that will simplify SSIS and eliminate the need for programming and increase productivity, improve performance and increase your ROI. If you need help with anything within Task Factory, you can visit help.pragmaticworks.com, where you’ll find lots of online documentation. To learn more about Task Factory, or to download a free trial, visit us at www.pragmaticworks.com.