Top 5 SSIS Tips

In a previous blog post, Pragmatic Works Data Analyst, Achara McNair-Tarfa, told us why she hates SSIS. She revisited with Brian Knight, our founder and CEO, to learn his top five SSIS tips and about how Pragmatic Works’ products can help make your day to day life with SSIS easier.

1.  Use Project Deployment Model

People did not like the project deployment model in the SSIS 2012 version because they had to deploy the entire project at once. The 2016 version solves this by allowing deployment package by package. The 2016 version is much more advanced with more built-in components. Also, there are lots of new investments happening in the project deployment model, so we can expect some great new features in the future.

2.  Do Not Update in the Data Flow

The data flow is meant for inserts, not deletes and updates. Doing an update in the data flow using SSIS, will do the update row by row and will also open and close a connection for each row, making it a slow process. Luckily, we have a component in Task Factory, Upsert Transform, which will update in large batches (10,000 row increments) and keeps the connection open throughout.

3.  Don’t Use the Built-In Warehouse Loading Components

The built-in components in SSIS have not changed since 2005, and they are terrible! For instance, if you customize a more complex example using the built-in wizard and you run that wizard again, it will drop all your customizations and start over again. It also will do a row by row update; see Tip #2.

4.  Automate Data Testing in Production

When you open a report each day, you not only want to ensure that the package ran, but also feel confident with data quality. LegiTest automates data testing to ensure data quality. We created LegiTest with the mission to stop bad data.

5.  Find Business Nuggets Elsewhere

Many of us who use SSIS often think only of databases as a data source. There are so many other good data sources, such as Salesforce.com, Litmos, GoToWebinar and even Survey Monkey. Task Factory’s REST Source lets you easily pull data from any RESTful source and bring that data into a central reporting system. This allows everyone on your team to get the data they need and you can integrate all your data to make better informed decisions.

We hope these tips will help make your life with SSIS easier. We can assure you that the over 60 components within Task Factory can increase productivity, improve performance and help with all your SSIS tasks. And adding LegiTest will help ensure that all your data, no matter where it’s pulled from, will be validated for data quality. Visit our website, or click the link below to learn more about these amazing products, or sign up for a free trial.