SSIS for Beginners

We understand that the world of data can seem daunting, especially when you're a n00b. For those of you that fall in that category, as a Product Engineer, I offer this advice: "All you need is dedication and the foresight to start small and slowly scale up. With this mindset, you'll achieve great things." 

In case you missed yesterday's Intro to SSIS webinar, or would simply like a refresher of some of the main topics covered, I invite you to continue reading this blog post.

Etl process

The first concept we tackled was ETL or Extract, Transform, Load. We develop SSIS (SQL Server Integration Systems) Packages with the intent of creating an ETL Process. We begin by extracting the data from one or many sources, then transform the data (some people say ‘apply business rules’), and finally load our data into a destination. Take a look at a simple visualization of the process below.

INTEGRATING MULTIPLE DATA SOURCES

So you may wonder why we use SSIS, and the answer is that SSIS is great for integrating multiple data sources. We may have multiple file types in our server that we want to combine into a centralized database, like a data warehouse, and getting our data together is what SSIS allows us to do. It has multiple data source types built into it, so whether you need to go to SQL Server or Oracle, we can easily connect to that data and pull it into the appropriate location.

DATA CLEANSING

Another common use for SSIS is data cleansing. If we have incoming data that we expect to be incorrect or bad, we can use SSIS to cleanse that data before we pull and insert it into our data warehouse or some other staging environment. This could be especially relevant to your sales or marketing departments, as they likely have numerous problems with data quality.

A common issue we’ve seen involves state data when we get addresses from customers. You may have a state (like Florida) with different versions used to identify it, like the abbreviation FL or Fla, or even the whole word Florida. By using SSIS and transforms, we can unify our data to only show one version.

These are a few basic use cases for SSIS and a small sampling of the basic terminology of this powerful tool. The sheer scope of what SSIS can do could fill hours, or even days, of tutorials and walkthroughs. I encourage anyone who is interested in SSIS or data in general to continue attending our free webinars every Tuesday at 11AM (EST) or to check out our previous training sessions covering a variety of topics.

In this ever-changing technological world, it's important to stay curious. The more you learn, the more valuable of an asset you become. Today’s world runs on data, and Pragmatic Works encourages you all to learn all you can. Thank you, and happy learning!