Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Cannot import Excel file into SQL08 using SSIS package

Posted on 2011-06-21
19
Medium Priority
?
1,254 Views
Last Modified: 2013-11-10
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
0
Comment
Question by:jat0818
  • 8
  • 6
  • 3
17 Comments
 
LVL 9

Expert Comment

by:DrewKjell
ID: 36011809
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
 

Author Comment

by:jat0818
ID: 36011911
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
 
LVL 9

Expert Comment

by:DrewKjell
ID: 36012100
Can you give the user full rights to just the directory the file is contained in?
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:jat0818
ID: 36012208
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
 
LVL 9

Expert Comment

by:DrewKjell
ID: 36012278
http://support.microsoft.com/kb/933835

That article discusses the error you appear to be receiving.  
0
 

Author Comment

by:jat0818
ID: 36012339
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
 
LVL 9

Expert Comment

by:DrewKjell
ID: 36012964
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
 

Author Comment

by:jat0818
ID: 36013131
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
 
LVL 9

Expert Comment

by:DrewKjell
ID: 36013471
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
 
LVL 9

Expert Comment

by:DrewKjell
ID: 36013541
What does the command line execution look like for the DTExec call?
0
 

Author Comment

by:jat0818
ID: 36013656
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 36109786
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
 

Author Comment

by:jat0818
ID: 36113056
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 36113916
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
 

Accepted Solution

by:
jat0818 earned 0 total points
ID: 36114628
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
 

Author Closing Comment

by:jat0818
ID: 36135963
I found the answer outside of Experts Exchange.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 36117226
Glad to hear it is solved.
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

782 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question