When you consider processes that are absolutely essential to your IT department, data transformation is at the top of the list. Since everything else relies on the data undergirding your decisions, the right applications can make the difference in terms of speed and quality.
Since everything else relies on the data undergirding your decisions, the right applications can make the difference in terms of speed and quality.
Microsoft’s SQL Server Integration Services (SSIS) were designed to handle the data integration process at the enterprise level, using built-in tasks and a wide swath of data types. Here’s how.
What is SSIS?
SSIS developed out of Microsoft’s Data Transformation Services (DTS) objects and utilities. The company rebranded and updated the product in 2005 as SSIS, with further updates as recently as 2017.
Much of SSIS’ broad utility relies on its many connectors to other data types, including XML, Excel, and Test files, as well as SQL and other relational databases. Built-in and custom transformations allow developers to mine data across large sets of data, including exact and near-exact rows and columns.
These customizable features were a much-needed improvement over the more limited means that existed with DTS. More than 15 years onward, however, where does SSIS—and its array of imitators and clones—stand when it comes to enterprise data integration?
SSIS & ETL
SSIS and its competitors are ETL tools—that is, they handle the Extract, Transform, Load process of taking data from differing sources and placing them in a data warehouse.To learn more about how this process works at each step, read our blog post on ETL in data warehouses.
While SSIS was and is not the only tool to handle ETL, it remains among the most well-known, partly due to its key advantages.
Advantages of SSIS
There’s no question that SSIS had the standard benefits you’d expect from a Microsoft application. Detailed documentation and extensive support helped developers and database administrators (DBA) get up to speed with the ETL process via SSIS and its related Microsoft products.
One of the more powerful use cases for SSIS is with OLAP cubes—data containers that allowed predefined queries to run on massive amounts of content, allowing for faster reuse and less coding-from-scratch compared to other queries.
To an extent.
PRO TIP:
Think of Olap cubes as the pathways on a college campus. When you know where the buildings are and the paths pedestrians use to travel between them, it’s easy to set down preformatted sidewalks for the students to use. OLAP cubes, as preformatted queries, work much the same way.
Downsides of SSIS
Use cases in data transformation have expanded greatly since the initial 2005 release of SSIS. AI, data visualization, and non-analyst data use are all far more prominent today, meaning we’re not asking the same questions as frequently as before. SSIS, and the Olap Cubes the application relies on, don’t offer speed and flexibility when the questions are brand new.
Use cases in data transformation have expanded greatly since the initial 2005 release of SSIS. AI, data visualization, and non-analyst data use are all far more prominent today, meaning we’re not asking the same questions as frequently as before.
What really hampered SSIS as a business tool was its fragility. Developer experience and coding are an advantage in any ETL application, yet for SSIS these skills are crucial. Limited third-party support requires extensive developer workarounds and quick-fixes that add up over time, particularly when working with certain file types such as SFTP.
The more teams use SSIS, the more time they spend debugging data packages. Database administrators quickly become a scarce resource, as their time and energy to maintain SSIS systems falls in relation to the size of the data and scope of the queries.
Other consistent issues include:
- Limited usability for non-developers
- Error-prone handling of special characters
- Burdensome set-up and deployment in new environments
- On-premises limitations—needs porting for cloud or hybrid set-ups
- Functions well in 100% Microsoft environments—less so with other SaaS apps
The Right Tools For ETL
Despite these drawbacks, SSIS remains a tool of choice for developers using Visual Studio. In particular, database administrators and large teams writing complex code use SSIS for their ETL needs.
As enterprises increasingly utilize modern tools like cloud servers and data lakes, the need for data analysis outside of IT has increased. At the same time, DBAs have more to do than ever, limiting the time they have to focus entirely on requests using SSIS.
K3 ETL offers low-code data integration solutions that allow analysts and IT staff besides DBAs to handle data transformation tasks in minutes, not hours or days. What’s more, K3 works with dozens of adaptors to ensure all the right sources of data are integrated into your data warehouse or data lake.
To see K3 ETL tools in action for yourself, request a demo today and start leveraging the power of low-code in your business intelligence.
