A SQL Server Integrated Services SSIS Exercise

By: James Miller

September 13, 2021

SQL Server Integration Services is a platform for building enterprise-level data integration and data transformations solutions. You can use Integration Services to perform a variety of tasks such as maintaining data hubs that serve Planning Analytics or other enterprise applications.

The following outlines the process of creating and deploying a working SSIS package that takes care of loading a staging table from a submitted CSV file as well as using data in an existing fact table to calculate a value and update a gross sales table.

Prerequisites – getting set up

If you are lucky enough to have an environment preconfigured, wonderful! If not, you’ll need to verify that you have working, and properly configured versions of the tools needed. For this example, I am using Visual Studio 2017, MS SQL Server 2019, and SQL Server Management Studio (version 18.9.1). In addition to these tools, you will also need to make sure that the Integration Services (SSIS) feature is installed as well (SSIS is not installed by default so you may need to run the SQL Server installation again to add it).

Creating a Project

Once you have your environment set, you need to create a “special kind” of visual studio project, that is, a “ Integration Services – Business Intelligence” type project. To create a SSIS project, open Visual Studio and select Business Intelligence – Integration Services Project.

Next, name the project (I called mine Cycle Sales):

The Connection Manager

Once Visual Studio has created a template project for you, the next step is to create a Microsoft SQL Server Integration Services package connection manager in the project. SQL Server Integration Services provides different types of connection managers that enable packages to connect to a variety of data sources and servers. For this project, we want to create a connection to a SQL Server. To do that, within the Visual Studio Solution Explorer, right click on Connection Managers and select New Connection Manager:

On the Add SSIS Connection Manager dialog, you can select the type of connection you want to create. In this project I selected OLEDB and then followed the “wizard” to find and select the SQL Server instance that I want to target. Once you save the connection, you should see it in solution explorer:

I am using the sample AdventureWorks2018 SQL Database and it is running in the SQL Server named SQLSERVER_2019 so I named my connection to reflect that.

SSIS Toolbox

Now that we have a valid connection to use, we can proceed to create a flow. To do that, you’ll need to have access to the SISS Toolbox. If you do not see it in Visual Studio, find the SISS Menu on the toolbar and click on SSIS Toolbox:

In this project we are only going to use two tasks: The Bulk Insert Task which provides an efficient way to copy large amounts of data into a SQL Server table or view and the Execute SQL Task which runs SQL statements or stored procedures from a package.

Importing Data to SQL Server

In this scenario, we have 2 CSV files. The first is a pricing file (cycle_prices.csv) and the other is a sales file (cycle_actual_volume.csv). In SQL Server, we have 3 target tables. They are Cycle_Price, Cycle_Sales and Cycle_Sales_Gross.

The Cycle_Price table will hold the price for each product, the Cycle_Sales table will be loaded with sales volumes for each product (by month and year) and the Cycle_Sales_Gross table will ultimately hold the calculated gross sales for each product (again by month and year).

I like to keep things “clean” and straight forward so I will create a separate SSIS task for each of the following:

  1. Clear the Cycle_Price table
  2. Bulk load the Cycle_Price table from the Cycle_Price.csv file
  3. Clear the Cycle_Sales table
  4. Bulk load the Cycle_Sales table from the Cycle_Sales.csv file
  5. Clear the Cycle_Sales_Gross table
  6. Bulk insert into the Cycle_Sales_Gross table
  7. Update the Cycle_Sales_Gross table with calculated Gross Sales

Creating the Flow

To create these tasks, you can select and then drag-and-drop the tasks from the SSIS Toolbox to the designer window, starting with a Execute SQL Task. Once in the designer window, you can right click it and select EDIT. In the Execute SQL Task Editor, you can fill in the General configuration properties.

  Name – provide a descriptive Name

ResultSet – should be set to None

ConnectionType – should be OLE DB

Connection – select the connection we set up earlier.

SQLSourceType – Direct Input

SQLStatement – you can use the Build Query feature, but since the example is really simple, you can just type in:

 

DELETE FROM Cycle_Price

Click OK.

Next, we can add a Bulk Insert Task in the same manner. Edit it, and change configurations under 3 areas, General, Connection and Options. Under General, just provide a descriptive name:

under Connection, we need to again select same connection as we did for the Execute SQL Task and also provide the target table name (Cycle_Price) and finally the source price file (cycle_pricess.csv):

Lastly, under Options, we need to tell SSIS that the data begins in the second row/record I the file:

After the above steps have been completed, in the designer window, we can use the mouse to connect the tasks with the green flow arrow to indicate the sequence in which the SSIS package should execute our tasks (first clear the price table then load the latest product prices into the table):

Since we now understand how “clear and load” tasks work, we can just repeat the above steps to clear the cycle_sales and cycle_sales_gross tables and then (bulk) load the cycle_sales table from the Cycle_Sales.csv file and then add those tasks to our flow:

The last 2 steps will populate the cycle_sales_gross table and then calculate the Gross_Sales value. Both of these tasks will be accomplished using Execute SQL Tasks. To populate the cycle_gross_sales table, add an Execute SQL Task and again edit the General configuration properties as follows:

Name – a descriptive Name

ResultSet – None

ConnectionType – OLE DB

Connection – select the connection we set up earlier.

SQLSourceType – Direct Input

SQLStatement – you can use the Build Query feature, but again since the example is really simple, you can just type the SQL statement. We are going to use the Cycle_Sales to populate the Cycle_Sales_Gross table so our SQL would look like this:

The complete project now looks like this:

Make sure you save the project. You can test it within in Visual Studio by clicking Start. Visual Studio will alert you of any errors in the package. In addition, you can always use Management Studio to query the tables and verify the results.

Time to Build it

Once you are satisfied that they project successfully meets your requirements, you need to create a file that can be deployed and run in SQL Server. To do that, you click on Build then Build Cycle Sales:

This will perform a sort of compilation of the project and create an Integration Services Project Deployment File: Cycle Sales.isapc which can then be deployed and executed in SQL Server. Once the file is created you can use Management Studio to log into the database and perform the following steps:

1. Locate the Integration Services Catalogs

2. Under the Integration Services Catalogs node, locate the folder “Deployed Projects” and expand it (if it doesn’t exist, you can create one).

3. Right click on the folder “Projects

4. Select Deploy Project…

5. Follow the instructions on the Integration Services Deployment Wizard

Select the project deployment file option and then browse to the .isapc file we just created.

Select the SSIS in SQL Server option.

Select the target server name and location (path) where the package will reside and run from.

The wizard will validate the package and once successfully validated, you can then click Deploy. Our package is now deployed and ready to run:

To execute the package, expand Cycle Sales and right click on Package.dtsx and select Execute…

On the Execute Package dialog, click on OK.

The following popup should appear:

Click Yes. The package will run, and the following Execution Information will be displayed:

We now have created our very own working SSIS package!

Subscribe to QueBIT Notifications

Can’t find what you’re looking for? →

    Contact Help Desk

    This form collects your contact information so that we can correspond with you. For more information on how we are committed to protecting and respecting your privacy, please review our Privacy Policy.

    I consent to QueBIT collecting my contact information.