Import Data From Excel to SQL Server (SSIS)

Abhishek Jaiswal
10y
25.1k
0
1

Article

This article shows a simple procedure for importing data from Excel to SQL using SQL Server Integration Services (SSIS). So just use the following procedure for creating SSIS packages for your project.

Agenda

Overview
Getting Started
- Create New Project
- Operations
- Connection Establishment
- Data Access
- Data Processing
Conclusion

Overview

Sometimes developers experience problems of converting data from one format to another using several tools, or we can say standards. These conversions usually create some serious issues sometimes. So for overcoming all these problems what we need to do is use a simple SSIS procedure.

Here we go.

Getting Started

I am dividing this workflow (data conversion) from Excel to SQL Server into 5 simple steps depending on their functionality, the procedure is:

Step: 1 | Create New project

Before getting started, first of all we need a new project for applying SSIS functionality, for this simply go to your Visual Studio and do the following procedure:

Here's the demonstration.

After creating a new project a pop-up window will come to your desktop. In that pop-up window just click on the BUSINESS INTELLIGENCE template, ensure these templates must be installed in your system first in order to access their functionality.

(If you don't have the templates already installed then go to the MSDN to install them.)

Now for the main theme, do the following procedure:

Here's the demonstration.

Now just do this basic procedure of convention and proceed:

Step: 2 | Operation

Then you will get a wizard like that. That specific wizard contains several items in a list. The list contains:

Control Flow items
Data Flow Source
Data Flow Destination
General

And so on.

From all those options you need to select the Control Flow item first. For that simply click on Control Flow items. Now this list also contains several types of operation-oriented templates or items, you can select any of them depending on your development requirements.

In my case I used DATA FLOW TASK. For selecting that you can either double-click on that option or you can use drag and drop functionality.

After clicking on Data Flow Task you will get a wizard like this that contains the following items:

Control Flow
Data Flow
Event Handlers
Package Explorer
Execution

For now just click on control flow and execute the package.

On clicking you will get a item template or data flow item in your control flow tab, that will be something like the following:

Step: 3 | Connection Establishment

Now, just double-click on data flow task, it will redirect you to the Data flow tab. Now you need to do your data exchange functionality here by performing these operations manually or by simply dragging:

Mapping
Connection Manager
Connection Establishment
Connection Provider
Source (can be Excel csv and so on.)
Destination table

(Again go to the toolbox option at the left hand side and click on Data Flow Source.)

On clicking it will show a wizard like this, from here you can select your desired data source (from where you want to access data for storing it into a SQL database).

In my case I am using an Excel SOURCE. Simply drag that item into the Data Control tab.

Now just do the following procedure:

Create connection manager

(By importing source file, just right-click and browse from your system.)
Create a new connection for your required database
Mapping of data
Connection fulfillment (right-click the option)

On doing this entire step, the scenario will be something like the following:

After the source flow file you need to do the same process for DATA FLOW DESTINATION. So here we go.

(Wizard after doing all those required steps)

Now the scenario of data flow after adding both source and destination flow item will be something like the following:

Step: 4 | Data Access

In this step we need to do some flow of data access and showing the real data handling connectivity. For that do the following procedure:

Create a mapping flow between both the items
Connection establishment
Data Access
Data Flow Task

What you need to do is configure the properties to establish data access flow from one end to another. For this use the following procedure:

Click on Connection Manager
- Provide a connection string
  
  (Or create a new one depending on your data location)
- Select the required table in table or view
Click on Mappings
- Provide a mapping between source and destination columns
- If there is any conflict then you can create new columns under copy considerations
Click on Error Output
- Provide error outputs or we can say handlers for an error recovery mechanism
- Select any of the 3 options available
Click "OK"

On clicking OK you will get a screen like that. This screen shows all the connection strings made by you so far for specific operations in the same project. Select the most recent and proceed according to it.

Just click OK.

Again on clicking okay you will get another window with these required fields:

Server Name
LogIn Server
Connection

Fill in all these fields depending on your preferred requirements and proceed.

Step: 5 | Data Processing

This is the last but not the least step; if everything until went fine then you will see these skins colored box in Green.

Now you can simply use these packages in your code for converting Excel files to SQL Server, without any problem.

Conclusion

This article has represented using a bulk flow task operation but there are several other operations also for doing that same functionality. Some of these operations are as follows: