Creating Your First SQL Server Integration Services (SSIS) Project

With this article I’m going to start a series on topic called SQL Server Integration Services (SSIS). This is our first article in this series where we’ll create our first SSIS project. Before proceeding further, let’s understand what SSIS is and what its benefits are.

What SSIS is

SQL Server Integration Services abbreviated as SSIS is a component of SQL Server, which is used to perform varieties of data migration task.

It’s a powerful platform for building enterprise level data integration transformation.

With this, we can retrieve data from any source, we can perform various kinds of transformation and then load the data to any destination. And this process is simply known as Extract, Transformation, Load i.e. ETL operation.

Benefits of using SSIS

After doing some research, below are listed benefits I found using SSIS.

• SSIS can perform parallel execution of data.
• With SSIS we can see the visual representation of the data flow.
• SSIS provides transform functionality which cannot be done via T-SQL queries.
• SSIS provides advanced level of structured error handling.
• SSIS can temporarily persist data to a native raw file format using the file system. Whereas, T-SQL requires temporary tables which can impact server performance.

Now let’s begin with our example.

Either you can use “SQL Server Data Tool (SSDT)” or “SQL Server Business Intelligence Development Studio (BIDS)” IDE for your project.

These IDEs get installed automatically when you install SQL Server. SQL Server 2008 comes with BIDS and SQL Server 2012 ships with SSDT.

I’m going to use SSDT for this series.

To begin, Open SSDT or BIDS IDE and you’ll get something like below which exactly looks like a Visual Studio interface.



Click on New Project and you’ll get a window as shown below.

1. Select “Integration Service” template under Business Intelligence.
2. Select Integration Services Project.
3. Name your project.
4. Select your project location.
5. Click OK.

Once you’ll click on OK, you’ll get your project area with one default created package named ‘Package.dtsx’ as shown below.



In Solution Explorer tab, you’ll find some directories like Connection Manager, SSIS Packages, and Miscellaneous. These are the directories available with SSDT.

Now if you open the location of the project, you’ll see below files and your package.



When you open that package in Notepad, you’ll see that the package is simply a XML file.



If you’ll create any project in BIDS you’ll get different interface with different directories.



If you see, above project ‘Project1_BIDS’ is created under BIDS i.e. SSIS 2008 version.

Now one interesting part is, when you open this older version project in some higher version, you’ll see some changes in Deployment model. For this I’ve added ‘Project1_BIDS’ project (which was created under SSIS 2008) into SSDT. Below is the screenshot after adding the project.



As we can see, the imported project of SSIS 2008 has ‘Package Deployed Model’ and our current project has Project Deployment Model.

There are 2 types of Deployment model on which we’ll discuss more in detail in our next article.

1. Package Deployment Model.
2. Project Deployment Model.

For now, just understand that if we want our older version package to use the feature of new SSIS 2012, it needs to be converted into new Deployment model i.e. Project Deployment Model. This is because if you won’t upgrade your package, it’ll work as same way in SSIS 2008.

As we can see, we’ve imported our package but we’re unable to view Shared Connection manager or any project parameters.



This is because these features were not present in earlier versions and hence you’ll need to upgrade this to the latest version to view those things.

We’ll discuss more about these Model and Model conversion in our next article.

Moving further, we’ll create a simple package which will pop up a message box saying “Hello World!!!” After all our simple project starts with Hello World.

So, let’s begin.

From “SSIS Toolbox” drag Script Task to Control Flow Tab as shown below:



Name your script task and double click on it. You’ll get a “Script Task Editor” as shown below:



Now we need to write our script to display the message. We’ve selected “Script Language as C#”. You can also choose VB as scripting language, click on “Edit Script” button and you’ll get a new window with some script in it as shown below:



On this window, you’ll find the same main() method as in C#.



We’ll write C# code inside this main() to display message window.



Our script is ready, save your code and close the window.

Click on OK button to save the script task. After all above steps, we’re ready to run or execute our package.



Press F5 or “Start Debugging” button to start the execution and you’ll see the following output showing ‘Hello World!!!!!’ message box.



If you notice on display the message box window, there’s a yellow circle shown on the top right of the Script Task. So this is nothing but an indication that the script is still running or it’s in executing state. As soon as you click on OK button, the indication changes from executing to successfully complete state and you’ll see a green tick mark as shown below.



But till here only your package completed successfully, your project is still under Debug mode, you’ll find message at the bottom indicating the same.



This simply says that your Package execution completed with success. Now either you can click on this link or from stop debugging button.

So, we’ve successfully completed our first SSIS project.

Conclusion

This was the article based on a simple SSIS project where we learned about SSIS and some benefits. We also created a projected where we displayed message with help of C# language.

In our next article, we’ll learn about Deployment model and we’ll also run SSIS 2008 project from SSDT. We’ll also see how we can change deployment model of the project. Till then keep learning and sharing.