Microsoft Integration: Exporting Excel into SQL server database tables

Aim: To transform an Excel file to SQL database table.

Tools required:

• SQL management studio

• SQL Integration service tool

• Excel data file (which you want to convert)

Steps to transform excel file into SQL database file:

Step 1:

Open up SQL management studio and SSIS tool, make a project and package in SSIS. 

Now make a SQL Server database table in the desired database and keep appropriate data types in SQL table according to Excel file data types.

Step 2:

Make a connection for the data source and data destination just like before (Flat file to SQL file transformation project), this time choose Excel data source and set connection.

Note: Convert all data type properly by

Right-click on Excel source> advance editor properties> input and output properties> Output columns> change data type with proper format and size according to your SQL database table.

Step 3:

Now try to execute this package, if everything runs fine then ok. 

If you get some error then check it in process tab of package design architecture.

If an error is like this below

[Excel file data source [2]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Then it is caused by your system because your system maybe 64 bit and you MSW office excel may be 32bit.

To fix this error you have to change debug configuration by

Right click on Project name>properties>debug>run64bitruntime to false.

And then click ok and run you package again. It should run properly.

Result in SQL server management studio table.

Now if everything is correct you will have your file will get converted in SQL database file just like mine.