jat0818
asked on
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_CANNOTACQUIRECONNECT IONFROMCON NECTIONMAN AGER. 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
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_CANNOTACQUIRECONNECT
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
ASKER
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.
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.
Can you give the user full rights to just the directory the file is contained in?
ASKER
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.
ASKER
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.
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.
ASKER
Here is the connection string that BIDS generates when I select 'Excel 97-2003' in the Excel Connection Manager.
Provider=Microsoft.Jet.OLE DB.4.0;Dat a Source=\\HHSQL\Reports\Dis tributerIn voices\Wor king\ASBTe st.xls;Ext ended Properties="EXCEL 8.0;HDR=YES";
Provider=Microsoft.Jet.OLE
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.
What does the command line execution look like for the DTExec call?
ASKER
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\d texec /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.
Command line is
EXEC xp_cmdshell 'C:\Progra~2\Micros~1\100\
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.
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 ?
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 ?
ASKER
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.
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I found the answer outside of Experts Exchange.
Glad to hear it is solved.
From your output however, it also appears that there is an issue with creating a connection from the SQL server to the Excel document.