Got DTS_E_OLEDBERROR with access 2007 data source after generated package configuration

Question

The package was working fine in BIDS when debugging, but got the following error after generated package configuration:

Error information is as below:

[Connection manager "***.accdb"] Error: 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.".

The connection string in properties window is as below:
Data Source=C:\***.accdb;Provider=Microsoft.ACE.OLEDB.12.0;

The connection string in .dstx file is as belwo:
Data Source="C:\***.accdb";User ID=;Initial Catalog=;Provider=Microsoft.ACE.OLEDB.12.0;

what could be the cause and how to solve it?

Thanks.

Answer 1

Hi Diyang,
After generating Package configuration are you trying to run it from BIDS only or from SQL Server?

Check your connections again and see that your SQL server has access to it...

Answer 2

hi when every i have a package that is working fine in the Dev. section and it dosen't work when i add a configuration
( remeber every thing is wroking fine in SSIS )



1- run the package check if every thing is OK
2- check and set the Variables arrording to the current setting needed (e.g. set the production destinatin source and destination connection manager objects)
3-go to "Package configuration"
4- check the ENABLE PACKAGE CONFIGURATION ( it ust be checked)
5- select the Configuration in the Grid
6- select Edit
7- check if you have selected the opetion that you need, the variable ( e.g. connection string and etc...)
8- next mext ..... finish
9- do the same thing on other Configuration in the Grid ( sam as in going to number 5 - repeat if needed)
10-close configuration

11- do the deployment (if you want)
sincerely
sh

---

Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)

Answer 3

Hi Bikash,

I am still running it from BIDS and got the error.
Is that normal?

Answer 4

How are you passing the password to the following connection string?
Data Source="C:\***.accdb";User ID=;Initial Catalog=;Provider=Microsoft.ACE.OLEDB.12.0;

You need to edite the package config file and add password manually.
Something like:
Data Source="C:\***.accdb";User ID= <UID>;password = <pwd>;Initial Catalog=;Provider=Microsoft.ACE.OLEDB.12.0;

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 5

Hi Nitesh,

I don't think I have a password for the access source. I can open it directly.
Is there any way I can check what the password is?

/Di