Cannot import Excel file into SQL08 using SSIS package

I have created an SSIS package which imports an Excel file from another server.  When I execute is as myself, a sysadmin, it works correctly.  I created a user which will be used to run this and other SSIS packages.  I also have a domain account for that user.  When log in as that user and try to execute the package, I get a series of errors. The output is below.
The server is Win2008 64 bit.  I am using the 32 bit version of DTExec.  The windows domain account has full control on the Excel file that I am trying to import.

So my question is how do I fix it?  I know that it will work if I bump the SQL user up to an admin but absolutely do not want to do that.  I also know that it works if the domain account is an admin, but I do not want to give it that level of authority either.  If it is a permissions issue, I really want to find the right level of authority that user needs.

Thanks in advnace

[Output Here]
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 32-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
NULL
Started:  9:27:33 AM
Progress: 2011-06-21 09:27:34.68
   Source: Load Table
   Validating: 0% complete
End Progress
Error: 2011-06-21 09:27:34.69
   Code: 0xC0202009
   Source: BKASBInvoices Connection manager "Excel Connection Manager"
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft JET Database Engine"  Hresult: 0x80004005  Description: "Unspecified error".
End Error
Error: 2011-06-21 09:27:34.70
   Code: 0xC020801C
   Source: Load Table Excel Source [1]
   Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.  There may be error messages posted before this
with more information on why the AcquireConnection method call failed.
End Error
Error: 2011-06-21 09:27:34.70
   Code: 0xC0047017
   Source: Load Table SSIS.Pipeline
   Description: component "Excel Source" (1) failed validation and returned error code 0xC020801C.
End Error
Progress: 2011-06-21 09:27:34.70
   Source: Load Table
   Validating: 50% complete
End Progress
Error: 2011-06-21 09:27:34.70
   Code: 0xC004700C
   Source: Load Table SSIS.Pipeline
   Description: One or more component failed validation.
End Error
Error: 2011-06-21 09:27:34.70
   Code: 0xC0024107
   Source: Load Table
   Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  9:27:33 AM
Finished: 9:27:34 AM
Elapsed:  0.718 seconds
NULL
jat0818Asked:
Who is Participating?
 
jat0818Connect With a Mentor Author Commented:
A co-worker and I resolved this issue.  It turns out that the low-level user needed read/write access on a temp folder under the profile of the account that SQL Server runs under.
This is the path to the folder it needed access to:
C:\users\<sql server account name>\appdata\local\temp

0
 
DrewKjellCommented:
Under the login set up there is a bulkadmin server role that can be selected.  This may be the setting you are looking for.  I know that you need to be an admin/bulkadmin to run a BULK INSERT statement, so it wouldn't surprise me to need similar permissions to use SSIS to load a file.

From your output however, it also appears that there is an issue with creating a connection from the SQL server to the Excel document.
0
 
jat0818Author Commented:
Giving that user the bulkadmin server role made no difference.

I agree that it is probably an issue with creating the connection, but am not sure what to do about it.  If I make the user an admin, it works fine.  Which makes me believe it is a permissions issue.  I do not want to just bump this user to admin though.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
DrewKjellCommented:
Can you give the user full rights to just the directory the file is contained in?
0
 
jat0818Author Commented:
He actually does have full control on the directory.  As a side note, if i set up a similar SSIS that uses a .txt file rather than an .xls, it works ok.
0
 
DrewKjellCommented:
http://support.microsoft.com/kb/933835

That article discusses the error you appear to be receiving.  
0
 
jat0818Author Commented:
That article is about executing an SSIS package via a SQl Agent job.  I am not using a job.  I am running the 32 bit version of DTExec in a query window in Management Studio.  The code will eventually be part of a proc which will be kicked off by another process.
0
 
DrewKjellCommented:
What version of the JET driver are you using.  The error reads as not a permissions issue but an OLE DB error which would be how you have configured the connection the the Excel file.
0
 
jat0818Author Commented:
Here is the connection string that BIDS generates when I select 'Excel 97-2003' in the Excel Connection Manager.
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\HHSQL\Reports\DistributerInvoices\Working\ASBTest.xls;Extended Properties="EXCEL 8.0;HDR=YES";

0
 
DrewKjellCommented:
There is a property in your SSIS package, Run64bitsRunTime, make sure that is set to false.  If it isn't try setting it to false and retrying.  Even though you are calling the package from the 32bit DTEXEC application, the package itself may have a property that is interfering.
0
 
DrewKjellCommented:
What does the command line execution look like for the DTExec call?
0
 
jat0818Author Commented:
Run64BitRunTime is set to false.  I do not think this is necessary when using the 32 bit DTExec, but I set it when I first started testing this package.

Command line is
EXEC xp_cmdshell 'C:\Progra~2\Micros~1\100\DTS\Binn\dtexec /dts "\File System\BKASBInvoices"'

I also copied the 32 bit DTExec to a new folder and used
EXEC xp_cmdshell 'C:\DTS32bit\dtexec /dts "\File System\BKASBInvoices"'
I did that so I did not have to deal with the spaces in the path.  Both give the same results as shown in my output above.

Please bear in mind that if I execute as an admin, this all works. So I do not see how it could be a problem with how I call it.  It also works as a standard user if it is a text file rather than an excel file.
0
 
Mark WillsTopic AdvisorCommented:
How have you defined the location for the Excel file ? By chance are you using any "user" properties in your SSIS package?
 
Otherwise it sounds like a permissions issue on your Jet provider (althought that type of error basically says "jet cannot do this for a variety or reasons")

As a temporary measure, can you try bumping that user to Admins group and see if that does solve the permissions problem ?

0
 
jat0818Author Commented:
The file location is in the format \\remote server\path\file.xls.  The user has read and write access to the file on that remote server.

If I bump the user up to a local admin on the SQL Server box, it does work successfully.  While that is a temporary work around I am not really sure that is a permanent fix.

0
 
Mark WillsTopic AdvisorCommented:
OK, so it seems it does have something to do with permissions... Or possibly "\\remote server\" name resolution which happens for the admin group but not "mere mortal" group.

And I agree, it is not a permanent fix, just testing to see what does work, and might buy some breathing space for the interim.

Will think about it a bit more.
0
 
jat0818Author Commented:
I found the answer outside of Experts Exchange.
0
 
Mark WillsTopic AdvisorCommented:
Glad to hear it is solved.
0
All Courses

From novice to tech pro — start learning today.