Import Data From Excel to SQL Server (SSIS)

Introduction

This article shows a simple procedure for importing data from Excel to SQL using SQL Server Integration Services (SSIS). So 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 converting data from one format to another using several tools or standards. These conversions usually create some severe issues sometimes. So we need a simple SSIS procedure.

H to overcome all these problems, here we go.

Getting Started

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

Step 1. Create a New project

Before getting started, first of all, we need a new project for applying SSIS functionality, for this 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, click on the BUSINESS INTELLIGENCE template; ensure these templates must be installed in your system first to access their functionality.

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

Now for the main theme, do the following procedure:

Here's the demonstration.

Now 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 includes the following:

• Control Flow items
• Data Flow Source
• Data Flow Destination
• General

And so on.

You must select the Control Flow item from all those options first. For that, click on Control Flow items. This list also contains several types of operation-oriented templates or items; you can select any depending on your development requirements.

In my case, I used DATA FLOW TASK. You can either double-click on that option or utilize the drag-and-drop functionality to select that.

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, click on control flow and execute the package.

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

Step 3. Connection Establishment

Double-click the data flows task, redirecting 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 on the left 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 in a SQL database).

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

Now do the following procedure.

• Create connection manager

  (Right-click and browse from your system by importing the source file.)

• 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 must do the same process for the 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 items will be something like the following:

Step 4. Data Access

In this step, we need to do some data access flow and show the actual data handling connectivity. For that, do the following procedure:

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

You need to 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 the 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 three 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. Please 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 minor step; if everything goes fine, you will see these skins colored box in Green.

Now you can use these packages in your code to convert Excel files to SQL Server without any problem.

Conclusion

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

• SQL Insert Task based on SSIS
• SQL bulk query operations
• SQL Server configuration manager
• Import & Export option
• Using conversion codes on the client side

And so on.

But this is the most commonly used method and simple too. I hope you will like that. Keep reviewing.