We help IT Professionals succeed at work.

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

183 Views
Last Modified: 2018-05-23
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

Certified Database Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

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
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.