Getting import error when attempting to import files into sql server via deployed ssis package

Juan Velasquez
Juan Velasquez used Ask the Experts™
on
I have created an SSIS package that import text and excel file from a central network location into tables on an SQL Server 2016 .  It execute flawlessly using the sql Data tools 2015. I then deployed the the project to the sql server.  The deployment went fine.  However, when I try to execute the import package I get the following error.  I've also attached the error report. I've checked the files and they are not open.  The Sql Server has been given the permissions to the file folder in question.  Just to cross my t's and dot my i, what types of permission does the sql server need in order to import files from a network location

RPS Import:Error: SSIS Error Code
DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft Access
Database Engine" Hresult: 0x80004005 Description: "The
Microsoft Access database engine cannot open or write to
the file '\\Bfs\departments\CORPORATE\DQM\ONLINE
RPS TEST DATA\Online BCM Tool Files\6. Change
Report Type\Change Report Type.xlsx'. It is already
opened exclusively by another user, or you need
permission to view and write its data.".
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Certified Database Administrator
Commented:
Are you running the package on the server as 32 bit, there is no 64 excel driver
How are you running the deployed package

Author

Commented:
Hello Jose,
I tried running it in 32 bit .  I went to the project menu, selected properties\Configuration Properties\debugging\ and set 64 bit runtime to false. I still getting the same results.  I'm new at ssis.

Author

Commented:
I just heard from the SQL server team and the package is being run  in 32 bit mode.

Author

Commented:
I have some more information.  I've been doing some testing and have found that the problem occurs when importing Excel and text files

Author

Commented:
Basically I found that the service account under which the ssis project was running did not have sufficient permissions

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial