none
How can I import an Excel spreadsheet that lives on a SharePoint site?

    Question

  • I have an Excel spreadsheet that lives on a SharePoint site (http://spgdc.gdcan.com/sites/mysite/Programs/ABC/Shared%20Documents/Forms/Build3 - Defect Arrival Prediction.xlsx) - The file can be xls or xlsx, we're flexible at this stage.

    I want to use SSIS to extract the data from the spreadsheet. I have no problem with the Excel Source Data Flow Source for files that live on Network shares, but everyone here is going SharePoint-crazy and so I need to get the file from the Shared Documents folder that lives on our MOSS Server (See URL above)

    I’ve tried using OLE DB Source, I’ve tried using Excel source but I just can’t connect to the file.

    OLE DB Source says “TITLE: System.Data No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21).”

    Excel Source, well, it just won’t let me navigate to it when I click the browse button.

    Any ideas?

    Thanks in advance for the help.


    Thanks Ron...
    Friday, October 16, 2009 5:57 PM

Answers

  • Try something like this: \\spgdc.gdcan.com\DavWWWRoot\sites\mysite\Programs\ABC\Shared Documents\Forms\Build3 - Defect Arrival Prediction.xlsx

    You can get this by going to the document library, choosing Actions..Open In Windows Explorer, and copying the path to the file from Explorer (hold shift, right-click on the file, and choose "Copy as Path"). Then use that as the source path. I think that this requires you to have WebDAV enabled on the server, so it may not work in your setup.

    John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
    • Marked as answer by RonInOttawa Monday, October 19, 2009 11:35 AM
    Friday, October 16, 2009 6:14 PM

All replies

  • Try something like this: \\spgdc.gdcan.com\DavWWWRoot\sites\mysite\Programs\ABC\Shared Documents\Forms\Build3 - Defect Arrival Prediction.xlsx

    You can get this by going to the document library, choosing Actions..Open In Windows Explorer, and copying the path to the file from Explorer (hold shift, right-click on the file, and choose "Copy as Path"). Then use that as the source path. I think that this requires you to have WebDAV enabled on the server, so it may not work in your setup.

    John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
    • Marked as answer by RonInOttawa Monday, October 19, 2009 11:35 AM
    Friday, October 16, 2009 6:14 PM
  • Hello,

    Maybe this will help

    http://sqlblogcasts.com/blogs/drjohn/

    Good luck!!!
    Friday, October 16, 2009 9:22 PM