Export Sharepoint Excel Sheet to SQL using SSIS

Question

I need to export excel file/sheet data from Sharepoint(file is on Sharepoint site) and import to SQL using SSIS. Is this is even possible. What are the steps.

I know i can download these excel files from SharePoint site to a folder and then import to SQL, that's easy! but i need to know how to do it directly from Sharepoint site.

Thanks!!

Solution

If you file is stored in SharePoint library, you can access it as if it were on a network location.

1. Open SharePoint Library with your Excel file in IE browser.
2. Go to Library Tools -> Library tab.
3. In Connect & Export section there should be Open with Explorer button.
4. Click on it and you should get a Windows Explorer window. Copy the path from this window's header to SSIS Excel connection manager and you should be able to access your file directly from SSIS.

If the button is grayed out that means you are lacking access privileges. Talk to your SharePoint administrator.