Nowadays, one of the greatest challenges enterprises face is to derive the greatest possible business benefits from increasing data. Database management systems (DBMS) play a crucial role in these areas, allowing the storage and retrieval of user data and data security management. In this article, I will focus on presenting the basic capabilities of SQL Server Integration Services (SSIS). I will show this tool is not only for database administrators and analytics, but other IT professionals might also find it helpful in their everyday work.

What is Microsoft SQL Server?

Microsoft SQL Server is a relational database management system (RDBMS) and one of the most market-leading technologies for managing multiple databases. It helps organize and manage huge business databases and supports Business Intelligence and data analysis applications in the company IT environment.

Microsoft SQL Server is built on top of SQL (Structured Query Language) – a standardized programming language for database administrators and IT professionals. SQL Server comes with its own implementation of the SQL language (Structured Query Language) – T-SQL (Transact-SQL) that allows managing databases and queries the data they contain.

WhAT Is SSIS AND Why IT iS USED?

SSIS (SQL Server Integration Services) is Microsoft’s graphical ETL tool, which has been included in Microsoft SQL Server since version 2005.
We can write a lot about the capabilities of SSIS. This tool is not just a graphical “flip” of data or a T-SQL code. Using the SSIS tool can bring many benefits, among which we can mention:

• Possibility to merge data from various data stores
• Coordinating data maintenance, processing, or analysis
• Identifying, capturing, and processing data changes
• Automation of Administrative Functions and Data Loading
• Possibility to clean and standardize data
• Populating data marts & data warehouses
• Building BI into a data transformation process
• It can load millions of rows from one data source to another in very few minutes

What’s essential is that SSIS eliminates the need for hardcore programmers, as it contains a GUI that helps users transform data quickly rather than writing large programs.

Thanks to SSIS, we can connect to various data sources, not only MS SQL databases but also Oracle databases, ERP systems, or various types of flat files, and load data into different tables, saving time significantly. What’s more, we can control each flow, for example, by adding an email notification when the data is not loaded entirely or with problematic records, which will not prevent further steps of the flow.

Download, Validate and Load Data – ETL Process

In this article, I will focus on presenting the beginning of the road with SSIS, i.e., downloading, validating, and loading data into the database—a process called ETL (Extract-Transform-Load).

The tool’s installation is simple, and step-by-step instructions are available on the official Microsoft website.

After the installation, we can see a clear view when opening the Visual Studio tool. At first glance, our attention is drawn to Control Flow and Data Flow that are the most important functions at the beginning of our adventure with SSIS.

Now, let’s try to load the data from the Excel file into our newly created database. If we have many sheets in one Excel file, we don’t have to build separate flows. We can use the Foreach Loop Container option and loop our loading cycle sheet by sheet.

For simplicity, let’s imagine the task: We have an Excel file provided with the data format Name, Surname, Birthday (Day-Month-Year). We have three columns, but the client expects this data in a Year / Month / Day format.

There are many ways to cope with it. Below you can see the most simple use of SSIS.

See recording:

1. Create a table

CREATE TABLE [dbo].[test](

                           [name] [varchar](30) NULL,

                           [surname] [varchar](30) NULL,

                           [year] [int] NULL,

                           [month] [int] NULL,

                           [day] [int] NULL)
ON [PRIMARY]

2. In Visual Studio set Data Flow

We start by indicating the source and destination where the raw data are Excel records, and the final table is our newly created dbo.test

It gives us an outline of the data import.
We can see information about errors or steps that we have skipped. Let’s check what these errors mean and fix them.  

3. Use the option ‘Derived Column”
As we can see the mapping cannot be complete because we have different input and target columns. In this case, we can use the ‘Derived Column’ option.

We were able to reformat the data from 1 source column to 3 columns – but we still see an error message about a different data type for the Name / Last Name column.
Should we change the data type? No – if the target data type is [varchar], select Data Conversion.

After data conversion and code page change, the process is completed.

This simple exercise demonstrates a small fraction of what you can do with SSIS packages. The possibilities are enormous when we delve into the types of transformation, aggregation, the possibility of using other coding languages starting with C # or PowerShell and ending with the capabilities of SCD (Slowly Changing Dimension) or Package Protection Level.

If you are interested in SSIS, I encourage you to delve into the available materials—it’s worth starting with the e-book Microsoft SQL Server 2012 Integration Services.

Passion And Execution

Who We Are

At Holisticon Connect, our core values of Passion and Execution drive us toward a Promising Future. We are a hands-on tech company that places people at the centre of everything we do. Specializing in Custom Software Development, Cloud and Operations, Bespoke Data Visualisations, Engineering & Embedded services, we build trust through our promise to deliver and a no-drama approach. We are committed to delivering reliable and effective solutions, ensuring our clients can count on us to meet their needs with integrity and excellence. 

more about Holisticon Connect