How to Load Excel 2007 file in SQL Server 2005 (SSIS 2005)

It is very difficult in a situation that we need to load Excel 2007 file into the database by the use of SQL Server 2005 version (SSIS).

So when the situation comes how to do it. It is very easy, follow the steps below,

I have an excel 2007 file with the some data as shown below,

I have created a package, where i have dragged a data flow in the control flow pane,
In the control pane create a variable with name as filename and give the file names,
D:\SAMPLE_EMP.XLSX

On the data flow pane , i have dragged a OLE DB Source and configure as shown below,

  

When you click new connection a window will be opened, select the provider as shown in the above picture, this provider is available if MS office is installed on the machine, if the option not available , download the provider from internet and install it.

On the server name give the file with path as shown above in the picture.

 In the left pane , On the ALL tab, write Excel 12.0 as shown in the picture.

Next, Test the connection and if the connection is succeed, we almost succeeded.

Now click okay.  Select the sheet which we need to load as shown in the picture below,

Now, Open the connection manager Properties right clicking on it ,

On the properties click the expression and give the variable on the SERVERNAME for the file name

as shown below,

 

Click okay. why the variable is given on the expression?  To load dynamically.

Now can add the destination and run the job, Success.

Hope you like it :)