Post reply

Importing excel file stored in sharepoint into SQL

  • February 23, 2015 at 4:56 am

    #304568

    I have an excel file that is stored in a sharepoint document library. I am trying to use SSIS to import it into a SQL database. I use the excel connection manager with a sharepoint UNC path. When I run it from BIDS, it runs successfully. When I run it from a job, I get an error

    "It is already opened exclusively by another user, or you need permission to view and write its data"

    It is definitely not open by anyone else, and I have full permissions to the file. ALSO, the SQL Agent and Service acct which the job runs under, has full permissions to the file. I have tried running it under a proxy account with my user account, but it fails with the same message. Further, I can run a DIR command from a command prompt to list the sharepoint directory contents successfully, but when I run the same command from SSMS using xp_cmdshell, it fails with access denied. Again, the SQL Service acct has full rights to the sharepoint site.

    I notice when i browse to the sharepoint document library and try to open the folder with "Open with Windows Explorer", it always asks me to login and I'm thinking that is related to the problem I am having - that it doesn't automatically pick up the windows authentication.

    Does anyone have any ideas?

    Thanks

  • February 23, 2015 at 5:28 am

    #1779227

    can you use a script task to grab a COPY of the file to stick in %temp%, and toggle it's attributes to not be readonly? i had to do something similar with a file kept in TFS, and i just needed it for processing, i didn't truly need to open the source file, and potentially save changes.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 23, 2015 at 3:25 pm

    #1779432

    Thanks, my non-sql scripting skills aren't that great, but i was able to write a powershell script to copy the file from the sharepoint URL to a local folder.

    Still curious as to why we can't access the file directly from sharepoint, but Thanks for the help!

  • May 28, 2015 at 3:11 pm

    #1801411

    I'm trying to import a huge amount of data from files stored in Excel to SQL Server with SSIS, but I've never done it before. Could you go into more detail about your process? It would be greatly appreciated.

  • October 8, 2016 at 6:30 am

    #1905514

    Is the library setting in sharepoint enforcing check in and check out with versioning?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply