Create a SSIS Project

SSIS Projects are used to extract data from a wide variety of sources, transform data, and finally load data into the destination. In this article, we will show you the step by step approach to create a SSIS project with an example.

How to Create a SSIS Project

In this example, we will show you, the steps involved in creating a New Project in SSRS (SQL Server Reporting Services). To do so, First Double click on SQL Server Data Tools or Business Intelligence Development Studio (shortly called as BIDS)

First, select the File option from Menu item. Next, select New and then select the New Project… option to create a new SSIS Project

Once you click on the New Project… option, a new window called New Project will be opened. Use this window to select the required project from available project templates.

Let me select the Integration Services Project under the Business Intelligence Templates. Next, we changed the project name as SQL Integration Services Project.

Click Ok to finish creating new SQL Server Integration Services project.

Let me explain about individual window

1. Solution Explorer: Use this window to create project level connection managers, and packages.
2. Properties: Use this window to change the properties of each and every task.
3. Toolbox: Drag and Drop the tasks, containers, transformations, sources, destinations to design your package.
4. Information: Click on the toolbox items to see the information in this window
5. Connection Managers: This window is to create a package level connection managers
6. Package: Design your package

Best Way to Learn SSIS in 28 Days

SQL Server Integration Services, shortly called as SSIS is the most emerging ETL tool in the current market. Although, it is the most powerful tool you can easily learn SSIS in 28days (Maximum).

SSIS is all about Sources, Transformations, and Destinations. You need Connection Managers to connect with sources and destinations. So, if you know the connection Manager then you do not have to think about the Sources and Destinations.

Prerequisite: You cannot learn SSIS by simply reading this blog, or watching YouTube videos. You have to practice every scenario by yourself. If you practice multiple time, you can master this tool. So first, Download and install BIDS, or SQL Data Tools. Next, Download and attach Adventure Works, and Adventure Works DW database.

Learn SSIS step by step in 28 Days

Learn everything about Connection Managers in First 2 days. Generally, it takes one day to cover this topic. By seeing the links, you might wonder how you gone make it. Don’t worry, most of the steps in those links are same. I think maximum 2 to 3 steps may differ.

Introduction to Conn Manger

OLE DB

ADO

ADO.NET

Cache

EXCEL

File

FTP

SMO

Learn SSIS Sources and Destinations

Next two days concentrate to learn SSIS Source & Destinations. As I said before, if you know the connection managers, you are good with Destinations and the Sources.

Install SQL Server Data Tools

The Microsoft’s Business Intelligence Development Studio, shortly called as BIDS does not come with latest SQL Server like it did in SQL Server previous versions. Furthermore, Microsoft has renamed the BIDS as Microsoft SQL Server Data Tools Business Intelligence.

This article will show you the step by step approach to install Business Intelligence Development Tools or Microsoft SQL Server Data Tools. We can also say, install SSRS, or install SSIS etc.

In order to download the application, please click on this link: Microsoft Business Intelligence for Visual Studio and select the language (by default English is selected), and click the download button.

Once the download is completed, click on the SETUP will open the following windows.

How to Install SQL Server Data Tools

If you are upgrading from SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 or SQL Server 2012 then select the second option.

In this example, and or we don’t have any previous installation. So, we are selecting the first option. I suggest you select the first option: New SQL Server stand-alone installation or add features to an existing installation

In this page, we have to accept the licence terms and click the Next button

Next, the application will look for Global rules. If you find any errors, try to Re-run.

Next, it will automatically check for the product updates (if any)

In the next screen we have two options:

Perform a new installation of SQL Server 2014: If you installed the 64-bit SQL Server 2014 then you must select this option. Because Microsoft released only 32-bit SQL Server Data Tools.

It means, if you select the second option you are adding 32-bit components to 64-bit SQL Server, which will raise architectural problems error.

In the next screen, select SQL Server Data Tools – Business Intelligence for Visual Studio 2013 and click the Next button

Once you click on the Next button, the installation process will start

Once the installation process is completed, Your SQL Server 2014 installation completed successfully with product updates window will be displayed. Please click on the close button and start working with SQL Server Data Tools.

Let me open the application by double clicking the application

What is SSIS

SSIS Introduction

Microsoft SQL Server Integration Services also called as either SQL Integration Service or SSIS. The SSIS is a business intelligence tool that provides data transformation solutions for various organisations.

SQL Server Integration Services is an ETL (Extract, Transform and Load) tool. It means SSIS can be used to extract data from a wide variety of sources such as Excel Files, Flat Files, XML Files, Relational databases, and transform (slice and dice) them as per your requirements and finally load the data into the destination.

What is SSIS Work environment

Below screenshot shows you, What is SSIS work environment, and how this is used to create SQL Integration Services projects.

As you can see from the above screenshot, we divided the window into different parts

1. Solution Explorer: This is a combination of project level connection managers, actual packages, and project parameters.
2. Properties: Use this window to change the properties of each and every task.
3. Toolbox: SSIS Toolbox provides a lot of built-in tasks, containers, transformations, sources, destinations, and administrative tasks to solve complex business problems. You can use these graphical SSIS tools by drag and drop those tasks in the work environment. It means you do not have to write a single line of code to perform most of the operations.
4. Information: You can see the information of each and every item in a toolbox by clicking the toolbox items
5. Connection Managers: This window is to create a package level connection managers
6. Package: Design your package

SSIS Project Development

SQL Server Integration Services project is a combination of Connections Managers, Packages, and project parameters (optional).

Connection Managers

SSIS is all about extract data from different sources transform data and load it in a completely different destination. We need connection manager to establish the connection between the SSIS package and source, and package and destination.

In SQL Server Integration Services, there are two types of connection managers and they are Package Level (created in a 5th window) and Project Level (created in Solution Explorer window). I suggest you to refer Introduction to Connection Managers article to understand them.

Package

This is where we design the complete data flow. Here is the place where we perform all the transformations.

Deployment

After the completion of the package development, you can deploy the package into the production environment.