Freelance SSIS Developer
Thank you for your interest in the work I do with SSIS. For my non-technical clients, it helps for me to explain why you might need this kind of work. There are three types of SSIS projects that I do repeatedly. Here they are, but you can skip ahead if you already know this.
- integrate applications
- data prep for reporting (also known as ETL, which stands for “extract transform and load”)
- data warehouse builds
Already Know About SSIS?
Before I lose you to the details about my SSIS project work (yawn), please just send me an email or give me a call and we can talk about your specific needs for SSIS.
Zoom: meeting ID upon request
I realize that most people don’t share my enthusiasm for these things. Here is a simple explanation with examples of those three approaches to using SSIS. I don’t expect this will answer all the questions you have; it’s likely to bring on more, even. If there is something you would like explained so you can make decisions about your data I’m happy to help, and the initial consultation is free.
SSIS Integration Projects
I once had a sales associate who used the word “integration” to speak for any I.T. service work that he didn’t know much about, “This has got integration written all over it,” he said, over and over. He didn’t realize it, but it was his catch phrase. A decade later, I can say, “This has got integration written all over it,” to one of the guys, and we’ll still laugh.
It’s fun to recall that, but there is actually an accurate part of his “understanding” of the technical details. Here is what I mean.
Integration is a process that happens behind the scenes to make different applications work together like one big system without needing a manual, user-driven process.
As a business intelligence developer, integration projects comprise much of the demand for my work. I use Microsoft Visual Studio to create SQL Server Integration Services (SSIS) projects that move data from one source to another, and sometimes combine it with other sources. When my work is complete, I deploy the project in a way that it will run in the background repeatedly on a schedule.
Example SSIS Integration
It will help if I provide an example. I recently did an integration for a company that was adopting a new, web-based time keeping software, Kronos Time and Attendance. The big picture of this integration was the following:
- Employee data is kept in their HR app, UltiPro.
- Contractor data is in another HR app, SOS.
- Hourly workers had to charge time to active contracts maintained by an ERP application, Syspro.
- Data needed to move from the original sources to Kronos. If it didn’t work, employees would not get paid.
This integration was developed as an SSIS project. The overall solution also included administrative reports, and they were developed in SSRS and used data-driven subscriptions.
The building blocks of integrations are called packages. It’s often helpful to break large processes down into sub-processes, and given them each their own package. Then each package can be scheduled according to the requirements. For example, the time clock software needed frequent updates receiving each new contract that time could be charged against. That happened every fifteen minutes. On the other hand, employee census data was only needed twice per day.
The described example was a full scale integration, but SSIS is also useful for automating small data flows. I often create mini-integrations like automating the download of a file from the internet and importing it into the back-end of an application.
Reporting Data Prep with SSIS
The ultimate data prep for reporting is the building of a data warehouse. However, there are a number of smaller use cases for SSIS to enhance your reporting capabilities.
Tableau and SSRS are capable of connecting to a multitude of data sources. For example, I can develop a custom report that interfaces with an application such as PeopleSoft (or anything, really). However, as the reporting requirements become more complex, or analytical in nature, then the ability to pull that off right inside the reporting tool is a challenge (or impossible, maybe).
The way to fix that problem is by creating small integrations in SSIS that prepare the data for reporting. Just like the full scale integration described above, an SSIS package that does data prep can run automatically on a schedule (or run manually, if that is what’s desired).
In summary, I should say that I earn a lot of my new client work with the purpose of developing reports, and as my clients discover more opportunities for reporting, the need to use SSIS for data prep is inevitable. That’s why it is good to work with me. I’m a B.I. developer who can do both the front-end and back-end work, reporting and integration.
Using SSIS to Build a Data Warehouse
SSIS is a great tool for building data warehouse (DW). This website has a page dedicated to Data Warehouse Development which covers specifics around data warehousing as opposed to SSIS, itself. An SSIS solution to build a data warehouse has some aspects of the previous two types of work described above.
- Like an integration solution, the DW sources data from existing applications and refreshes it on a schedule
- Like a report data prep, the DW requires a data model meets the needs of reports and dashboards.
Example SSIS DW Build
The main function for SSIS in the DW build is to cover the two aspects just described: get the data and prepare it for reporting. When I develop an SSIS solution for data warehousing I also like to include the steps to create the database objects. You can see in the image shown here, there are six packages that create tables. Those are for the use of the developer(s), and they do not get deployed nor will they run on a schedule.
The top-level flow of a DW refresh has three parts:
- Staging: get all the data from the original sources
- Build dimensions
- Build facts (relating them to the dimensions)
Those three steps cover the build of the SQL DW, but a fully modern solution might extend the SSIS processes to bring the SQL data into a Tabular Model for more robust analytical capabilities. I have written a simple article explaining what SSAS Tabular does, if you’re interested in it.
SQL Server Import and Export Wizard
I have just reviewed some of the three common uses for SSIS in a business intelligence context. However I didn’t mention he most familiar part of SSIS: the Import and Export Wizard that’s included with SQL Server. The wizard is normally pretty easy to pickup and use, but there is a big gap between using the wizard and developing an SSIS project from scratch. I think that creates some mystery and frustration around SSIS.
If that describes your situation — one of frustration — then I can probably help you out. I really enjoy working in SSIS, so much so that I have a few hobby projects that I work on from time-to-time. Here they are:
- Major League Baseball data, every game, pitch, play, player, and team, etc.
- COVID Tracking Project data
- Voter registration data
Tell Me About Your Need for SSIS
SSIS projects can serve so many aspects of data flow. You can extend the standard SSIS components, too. Personally, I like to use the ZappySys SSIS PowerPack because it covers some common requirements better than a standard installation of Visual Studio. Considering the infinite variety of SSIS projects, my business intelligence experience might not be the right match for your requirements. Therefore, if I’m not the right guy, I’ll tell you and help you get on the right path (no charge).
Additionally, hiring the right developer means considering more than the technological skills. Please read more about me and how I fit the role of a BI developer perfectly. Thank you.
Zoom: meeting ID upon request