Import Data From Excel to SQL Server (SSIS)

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.