May 14, 2008 at 10:18 am
Hi, I've got a small problem with an excel connection manager. Below is the series of error messages I receive:
Error: 2008-05-14 08:59:21.17
Code: 0xC0202009
Source: WiReCAPSLoad Connection manager "Excel Connection Manager"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" H
result: 0x80040E21 Description: "Multiple-step OLE DB operation generated error
s. Check each OLE DB status value, if available. No work was done.".
End Error
Error: 2008-05-14 08:59:21.18
Code: 0xC020801C
Source: Data Flow Task 1 Source - Sheet1$ [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAG
ER. The AcquireConnection method call to the connection manager "Excel Connecti
on Manager" failed with error code 0xC0202009. There may be error messages post
ed before this with more information on why the AcquireConnection method call fa
iled.
End Error
Error: 2008-05-14 08:59:21.18
Code: 0xC0047017
Source: Data Flow Task 1 DTS.Pipeline
Description: component "Source - Sheet1$" (1) failed validation and returned
error code 0xC020801C.
End Error
Progress: 2008-05-14 08:59:21.18
Source: Data Flow Task 1
Validating: 66% complete
End Progress
Error: 2008-05-14 08:59:21.18
Code: 0xC004700C
Source: Data Flow Task 1 DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2008-05-14 08:59:21.18
Code: 0xC0024107
Source: Data Flow Task 1
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
So from the message I'm assuming that somewhere in the package configuration its missing the path to be able to find the Excel spreadsheet in question. But when opening up the package configuration I can't see anything immediately wrong. For the path of the spreadsheet location I've used the full UNC path instead of any mapped drive locations I've being guilty of using in the past. Below is the Excel Connection Manager section of my configuration file:
I've checked the rest of the configuration file and wherever something needs a username and password, they are entered correctly. I've also made sure that the account I'm logged onto our server and running the package under can access the excel file in question with no issues.
What is abit strange is that I have another package that runs in a similar way and also uses an Excel Connection Manager but from a different folder in the same area and that runs fine. I've tried to compare the 2 sets of connection managers to see if I can spot the difference but I've been unable to so far. Al that I can see that is different is the folder winningproject somtimes being in lowercase and then sometimes having capitals, but that doesn't seem to make any difference.
I'm hoping a fresh pair of eyes will be able to spot something obvious that I've missed!! Below is the Excel Connection Manager section of the configuration file I have that works fine.:
I went to check that I can gain access to the Excel Spreadsheet through BIDS and I noticed when reloading the project in BIDS it came up with the error AcquireConnection method call to the connection managerfailed with error code I noticed there was a red cross on the Source Excel Data flow and when doubling clicking on it and then clicking preview it pops up with the box Password Required, An user id and password are required to connect to Excel Connection Manager. I don't remember having to specify a username and password when setting it up so I just used my own network id and password but that wasn't good enough, so I'm not sure what User Id and password it wants there. So I created a new Excel Connection Manager and went through the process of specifying the full unc path of the spreadsheet, specifying the sheet it is to use then clicking on preview and open pops the spreadsheet with the relevant information. I then recreated the configuration package referencing the new Excel connection manager and making sure it doesn't reference the old one and save everything to my sql server where I want to run it from. Again the same error message appears when trying to run it so I open the project backup in BIDS and this time the error message about the AcquireConnection appears and the red cross is back on the data flow and when I click on it and click preview the Password Required box reappears for the new connection, even though when setting it up originally it was all happy to let me view the spreadsheet but not now!!
So with the Excel Connection Manager, where is the option to specifiy a username and password that it should be using to access this spreadsheet? Because when I go to set it up I get the option to specify the Excel File Path and Excel version is Excel connection settings, and then when clicking on the Excel Source Editor I have the options of OLE DB connection manager, Data access mode and Name of the Excel Sheet. From there I can choose Sheet1$ and hit preview and it brings up the data. I then can't see anything in Columns or Error Output that would appear to be relevant to Username or Password. Whereabouts it is possible to specify this and want account shall I be using?
Thanks
May 15, 2008 at 8:56 am
Hi,
I am facing an excatly similar problem and am myself confused on why this is happening. In my case it was working well in the development environment and in the test it came up with the issue.
On one of the posts I have read that some SYSTEM PATH variable needs to be reset to pick up DTEXEC from 90\ .
I am not sure how to reset the SYSTEM variable. Any ideas on this or an alternate to fix the issue??
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply