Getting More Done in Less Time with SSIS (MS SQL Server Integration Services)

In a blog post by Jason Brooks, he describes his love of Task Factory and its components that have helped him save time on development and lots of headaches. It's a great testimonial to Pragmatic Works' Task Factory and something we just had to share. His post is below or you can read his original blog post here.

I’ve been using Microsoft SQL Server Data Tools/BIDs for about 8 Years and a large portion of that time was spent using SSIS. I remember when I first started using SSIS, it wasn’t as part of creating a business intelligence solution. It was a tool I realised could be used to automate the handling of data. SSIS, I found, was almost the ultimate Swiss army knife for automated data manipulation.

Before I found SSIS, every time a supplier sent us a new pricelist, we would have to perform time consuming data manipulation in Microsoft Excel to get the data into a format we could then import into our company database. More often than not, the data manipulation process was exactly the same every month, time consuming and labour intensive.

SSIS changed that for me, now I would design a flow process that automatically picked up those files, performed the transformations/data cleaning automatically and would do so without any human operator mistakes, repeatedly month after month. The SSIS Jobs also allowed for error handling so that even if a supplier changed their data format, I would get an email alert to go and modify my package. Overall, I found the step change in business process time/speed and accuracy just amazing.

The only downside was the development time creating the packages in the first place. Each package could take a few days or more depending on the amount of data transformation necessary, but I would make that time back 100-fold or more.

Then after about 3 years of developing with SSIS, I found a product called Task Factory by Pragmatic Works. Task Factory is a set of additional components and connectors for SSIS, that helps speed up development of packages by alleviating a lot of the scenarios where you would have to write custom code in a script task/script component or having to do a lot of basic SQL plumbing code (update or merge scripts).

The product has around 60 components. I’m not going to go into detail about each one, but suffice to say I have regularly used about 50% of them in every day development tasks. The other 50% I have found to be just as useful, but they have more specific edge case uses, not something you would use all the time, but when that edge case arrives it’s nice to have them.

Some of my favourite components are:

• Upsert Destination: An SSIS component that helps synchronize two systems no matter the source or  destination (This means not having to write a lot of SQL Merge scripts). Dynamics CRM Source: Read data from Dynamics CRM (on-premise or cloud).
• Dynamics CRM Destination: Insert/Update/Delete/Upsert data back into Dynamics CRM (on-premise or cloud).
• Update Batch Transform: An SSIS component that allows you to easily update data in a table without writing any SQL statements.
• Delete Batch Transform: An SSIS component that allows you to easily delete data in a table without writing any SQL statements.
• Dimension Merge SCD: A component designed to handle most dimension loading scenarios (Works much faster than the native “Slowly Changing Dimension” component).
• Data Cleansing Transform: This has 16 built in algorithms that allow you to clean data without any programming or SQL scripting.
• Fact Table Destination: This component can greatly reduce development time for SSIS packages with multiple dimension lookups.

At this point I realised I have many more favourites and I don’t want to turn this into a giant list, so if you’re interested in checking them out then go here:

https://pragmaticworks.com/Products/Task-Factory

Now that I have moved further on in my career into the world of business intelligence and am now using the full Microsoft BI stack, I am finding that I am using even more of the Task Factory components more often in daily use. Not only that, but quite a few other products and services from Pragmatic Works, including their excellent training on-demand product.

So, the reason why I wrote this article? Well, I feel Pragmatic Works has helped me achieve more and do more in a lot less time and helped me avoid many headaches in the process, so this is really me saying a big thank you to them.