Avatar of Juan Velasquez
Juan VelasquezFlag for United States of America asked on

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

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.".
Microsoft SQL ServerSSISSQL

Avatar of undefined
Last Comment
Juan Velasquez

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Jose Torres

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Juan Velasquez

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.
ASKER
Juan Velasquez

I just heard from the SQL server team and the package is being run  in 32 bit mode.
ASKER
Juan Velasquez

I have some more information.  I've been doing some testing and have found that the problem occurs when importing Excel and text files
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
Juan Velasquez

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