How To Create A SSIS Packages?

Introduction

Imagine you run a business and you have information about your customers in different places - some in Excel, some in emails, some in flat files, and some in your website's database. SSIS can help you combine all of that into one place, clean it up so it's accurate and organized, and then use it to make smart decisions about your business.

What is SSIS?

SSIS stands for SQL Server Integration Services. It is a part of Microsoft SQL Server, a powerful database management system. It is highly used for handling data. But SSIS is not just about storing data; it's about making sure data flows smoothly from one place to another. It is a robust component within Microsoft SQL Server, designed specifically to tackle complex data integration and transformation tasks. At its core, SSIS is a platform for building enterprise-level solutions related to data movement, cleansing, and loading. It is an upgraded version of DTS (Data Transformation Services), which was an old data transformation solution included with SQL Server. 

To read more check my previous article- What is SSIS? 

How to Create an SQL Server Integration Services Package?

To create an SQL Server Integration Services package, follow the below steps -

Step 1. Install SQL Server Data Tools (SSDT)

You may already have SQL Server Data Tools installed in your Visual Studio. If you are not sure whether you have it already installed or not, follow the below steps to check-

1. Open Visual Studio ( I am using Visual Studio 2022) and click 'Extensions'.
2. Select 'Manage Extensions'. A popup window will open.
3. In this window, click 'Installed Extensions', all the installed extensions will appear here.
4. Check if SQL Server Data Tools is present in this list. If it is present, then, your installation step is already done, else, you can search for 'SQL Server Data Tools' and install this extension.

Step 2. Create a new SQL Server Integration Services Project

Once the installation part is done, follow the below steps to create a new SQL Server Integration Services project-

1. In Visual Studio, go to the "File" menu, then "New", and select "Project".
2. In the "New Project" dialog, search "Integration Services Project".
3. Select "Integration Services Project" and Click "Next".
4. In the "Configure your new project" window, name your project, and click "Create".
5. Your project will be created. Below is the image of how your solution explorer will look like.

You can see here we have a connection manager, packages, control flow, and all, which I have already explained in my previous article which is What Is In SSIS ToolBox. You can read here- What Is In SSIS Toolbox?

Now, for this article, I will be creating a package to move data from a flat file to the database.

Step 3. Create a flat file

If you already have a flat file where you have some data stored, then you may skip this. Otherwise, create a text file and add some data to it. Here, I have created a file named DemoDataFile.txt and added some data to it.

Step 4. Add Connection for flat file.

To access this flat file and move its data to the database, we will need to establish a connection for this flat file. To do this follow these steps-

In the bottom left side, you will see Connection Managers. Right-click in this area. A list will be shown.

Select New Flat File Connection from the list of options.

A File Connection Manager Editor window will open. In this window, in usage type, you have options whether you want to create a file or choose an existing file. In my case, I have an existing file, so, I choose the Existing file option, you may choose according to your requirements. In File, if you choose Existing file, specify the location of the file and click OK.

Now, the connection is created and you will see the connection in the Connection Managers like below.

Step 5. Add Connection for database

For this demo project, I have created a separate Database named SSISDemoDb, which currently has no tables.

In the Connection Managers. Right-click, a list will be shown. 

Select New OLE DB Connection.

A new Configure OLE DB Connection Manager Window will open. If you have already created the connection, skip this, else Create the 'New' button.

A new Connection Manager window will open. Here, enter your Server name, Choose authentication type, enter credentials like username and password, and select the database name to which you want to migrate your flat file data. Click OK.

Now, in the Connection Managers, you have both the connections ready.

Step 6. Create Task

Now, from the toolbox, drag and drop a Data Flow Task and you can rename it if you want.

Double-click the dragged Data Flow Task, and add a flat file source and OLE DB Destination. Ensure the connection string is proper.

 

Double-click on OLE DB Destination and an editor window will open. Here, if you already have a table created, then in the name of the table, select the table, else click the 'New' button.

If you click New, then a window will open with Create table script, if can modify it, it if you want to, else keep it as it is and click OK, review everything, and click OK.

Your table will be created. You can verify this in your database.

Step 7. Execute task

Now, go to control flow, and run the task.

Check your database, you will see that the data has been migrated successfully.

And we have successfully created an SSIS package that migrates data from flat file to database.

Conclusion

In this article, we learned how to create an SSIS project using different control flows and data flows present in the SSIS editor. We used flat file as source and database as destination, but, you can do this as per your requirements. And this won't take much time. Using the same concept, you can even create complex SSIS projects too.