Link to home
Start Free TrialLog in
Avatar of Alicia_Rose
Alicia_Rose

asked on

Error when using Package configuration for Source and destination

Hi all,

 

I have a package that does simple exporting from an excel sheet to a table.

I used a Dataflow task with Excel Source and OLEDB Destination Components.

And i created Package configurations for Source and Destination Components.

After than when i execute the package i get the following error.

 

Information: 0x40016041 at ProductDetails_Import: The package is attempting to configure from the XML file "D:\TEST_ETL\LPL_Config2.dtsConfig".

Information: 0x40016041 at ProductDetails_Import: The package is attempting to configure from the XML file "D:\TEST_ETL\DBCon2.dtsConfig".

SSIS package "ProductDetails_Import.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Error: 0xC0202009 at ProductDetails_Import, Connection manager "Excel Connection Manager": 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" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Error: 0xC020801C at Data Flow Task, Excel Source [1]: 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.

Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "Excel Source" (1) failed validation and returned error code 0xC020801C.

Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Data Flow Task: There were errors during task validation.

SSIS package "ProductDetails_Import.dtsx" finished: Failure.

The program '[2416] ProductDetails_Import.dtsx: DTS' has exited with code 0 (0x0).

I have been trying to troubleshoot the error message given below from last evening.

 

I have been trying to troubleshoot the error  from last morning.

Counld not figure out what is causing this error to occur.

 

Please help!!!!

Any pointers\Suggestions would be highly appreciated.

 

Thanks & Regards


Avatar of chapmandew
chapmandew
Flag of United States of America image

It seems like the SSIS package can't find the XL file....is the XL file on another server from the SQL machine?  if so, are you using the UNC path to that file?

\\servername\\sharename\excelfile.xls
Avatar of Alicia_Rose
Alicia_Rose

ASKER

Chapmandew,

ThanK You so much for reponding.

The XL file is in the same server as that of SQL.
I have figured out something:
when i click the 'Preview' button in the XL Source Connection Component, it prompts me for user name and password.
I tried to give the same user name and password that i have filled up in the properties and in the config file, but still  it is not accepting the user name and password.

Is there any way to ignore the username and password in the properties window(at teh time of creating Xcel connection manager). Because i see default password there.

Does it take my windows username and password by default?

Thanks a lot again.

ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Tim,

Thanks for replying again,
When i execute the package itself, i get the above error.
Which username and password does the package take while creating the Xcel con manager( by default)?

Thanks,
The user name of the person trying to access it.  Can you login into the sql machine and actually open the excel file?  have you talked to any network guys about it?