SSIS: Excel 2007
Loading data from Excel 2007 to SQL using SQL Server Management Studio (SSMS) is easy if you get two parameters correct. Here are the steps needed to load an Excel sheet into an SQL table from SQL Server Management Studio (SSMS).
1. Open SSMS.
2. Right-click the appropriate database and select Tasks, Import Data...
3. Click Next on the Import and Export Window Dialog box.
4. Select the Microsoft Office 12.0 Access Database Engine OLE DB Provider (it is assumed Office 2007 has been loaded so the provider is on the computer).
5. Click the Properties button after the Microsoft Office 12.0 Access Database Engine OLE DB Provider has been selected.
6. Click on the All tab.
7. Highlight Data Source and Click the Edit Value button.
8. Enter the path and excel file name (i.e. For Excel 2007 the file extension should be .xlsx) in the property value window.
9. Click OK.
8. Highlight Extended Properties, and then click the Edit Value button.
9. Enter Excel 12.0;HDR=YES for the property value.
10. Click OK to close the edit properties window.
11. Click OK to close the data link properties window.
12. Click Next.
13. If all is successful the Choose a Destination window will be next. Select the server and database that are the destination.
14. Click Next.
15. Select Copy Data from one or more views or write a query to specify a data transfer
16. Click Next (assuming Copy Data from one or more views is selected.)
14. Select the Sheet and then use the drop down to select the destination table.
15. Edit the mappings and set the source and destination mappings.
16. Click OK on the Column Mappings dialog box.
16. Click Next.
17. On the Save and Execute page, select the desired settings.
18. Click Next.
19. Click Finish.
The desired data should be loaded into the SQL table.
What if you have a server (maybe a VPC running SQL Server 2008 "Katmai") and you want to connect to an Office 12 document but you don't have Office 12 loaded on the server. The provider needed for Office 12 can be found at http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en
**Note: **This article has been ported over from http://ssis.wik.is/.
Note: If you were the owner of above article, please update it to the latest or with other appropriate information.