question

wk4997 avatar image
wk4997 asked

Error with Excel file in SSIS Execution for SQL Job

I cannot get an excel file on a mapped network drive to be used as a connection manager for a SSIS job I have on our SQL Server. I have debugged the SSIS Package fully in VS and everything works as intended. Now, I get an error message when attempting to run the job on our SQL 2005 Server: Executed as user: {domain admin acct} ...ersion 9.00.5000.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:41:42 PM Error: 2012-10-17 13:41:42.93 Code: 0xC0202009 Source: Package 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: "'P:\\Recert List\\Recert List 2013-03 WJK.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.". End Error Error: 2012-10-17 13:41:42.93 Code: 0xC020801C Source: Data Flow Task Import from Recert List file on Public Share [260] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connect... The package execution fa... The step failed. Also, the SQL server is installed on a Win2003 Server OS. I have made a SSIS Proxy with the credential account linked to the Domain Admin account and still I get the error above. What am I doing wrong????
sqlssisexcel
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
Just to clarify, is the P: drive available from the SQL Server? Can you change that to use the UNC path instead?
0 Likes 0 ·
wk4997 avatar image wk4997 commented ·
Yes, it is available from the OS and mapped properly. Do I have to make a special link within the server configuration? Also, I have tried to use a UNC path, but that bombs debugging.
0 Likes 0 ·
wk4997 avatar image wk4997 commented ·
I am new to SSIS and have been playing with it to see what I can get it to do. I use a for each loop container that scans a directory for a very specific filename format and then when that condition is met, it will run a script to import the contents of that specific Excel file. However, I cannot get it to debug successfully when I am using UNC paths.
0 Likes 0 ·

3 Answers

·
SirSQL avatar image
SirSQL answered
The problem is that your mapped drive is not available within SQL Server. It is mapped only for you when you are logged on. You will need to use the UNC path in order to be able to access the relevant network location. **Side note: do not run credentials under Domain Admin accounts! In doing this you put your entire Windows infrastructure at risk.**
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

nopol avatar image
nopol answered
> Executed as user: {domain admin acct} if you are using a proxy, you should have a credential (domain user) with the minimum rights required on the destination folder. 1. check that the SQL user is correctly linked to the credential 2. check that the SQL user has access rights to the proxy in SQL Server Agent 3. ...and not to loose time (happened to me :)) that the "Run as" list box is configured to use the right user (SSIS proxy - user) in the job step configuration. 4. if the probleme persists you may have an issue with the windows user (credential) permissions on the folder. This is usually what we check when we get this kind of errors. I would also recommend to use an UNC path.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dinstockmann avatar image
dinstockmann answered
In this case use to [ http://www.repairxls2003.xlsrepairtoolbox.com][1] [1]: http://www.repairxls2003.xlsrepairtoolbox.com It helps you how to repair .xls file and objects, fonts, colors, cells, borders and other items as well
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.