February 17, 2010 at 3:36 am
Hi,
i have a very strange and boring problem.I has an ssis 2005 project with almost 15 package.In each package there are a sql 2005 oledb connection and a jet 4.0 oledb connection.
Now sometimes this issue happen.When i try to connect to my mdb file using jet oledb connection, on each mdb i try to connect, using for example the button 'test connection' in mdb connection properties,ssis returns this error:
"Test connection failed because of an error in initializing provider. Unspecified error"
This error is intermittent.
The mdb path is correct.
I try to change mdb file.Nothing.
I try to delete connection and recreate a new one, pointing directly to another folder.Nothing
I try to reboot machine.Nothing
I try to reinstall mdac with latest version.Nothing
After a lack of random time the error fix automatically
No oledb connection can be made using this provider but ONLY in this project..:angry:
The package is ok beacuse now it's running in production without problem.If a take a running packag from production the problem remains
here i post more details:
Wher i click on oledb source in data flow:
"Error at Import classe C [Connection manager "Mdb_data"]: 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".
Error at Load check table OPER [V_OPER [96983]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Mdb_data" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed."
Can somebody help me?Next step i want to try is to hit computer against the wall, after hitting my head many times against the keyboard....:crazy:
Thak you in advance...
February 18, 2010 at 1:35 am
Ok the problem was related to solution.
I must delete my solution with 15 projects and recreate it...and now is all ok.
So probably something wrong in soluition...
very very boring...:hehe:
thanks.
February 19, 2010 at 3:06 am
I can offer sympathy if not a solution. I have recently been importing from multiple tables in several Jet databases into SQL Server, and found SSIS very unforgiving if I tried to reuse Jet connections. The solution was a bit like yours - I constantly had to recreate the Jet connections rather than attempt to reuse/modify existing ones.
I think the problem relates to the object IDs SSIS allocates to everything, which seem to get invalidated by any change to the connection definition. I'm sure there's a programmatic solution, but redefining the connections every time I needed them worked for me in the time available and for the scope of the project.
May 5, 2011 at 2:24 pm
First, thank you for looking - and all help offered is gratefully received and appreciated.
I have a similar problem through SSMS. For an unknown reason, my linked server to an Access 2003 .mdb file will just stop working. :exclamationmark: Right-clicking and testing the connection reveals the 7303 error. I don't know how to cause the connection to the local mdb file to become available again. There is quite a bit of data in this mdb file that I need to compare to SQL data until we can move all data to SQL.
I didn't quite understand the previous poster's reference to 'rebuilding' their connection. is that dropping and recreating the linked server? When I try this manually, the newly recreated linked server still fails to connect(?) Thank you for looking (- and helping?)
May 5, 2011 at 2:24 pm
First, thank you for looking - and all help offered is gratefully received and appreciated.
I have a similar problem through SSMS. For an unknown reason, my linked server to an Access 2003 .mdb file will just stop working. :exclamationmark: Right-clicking and testing the connection reveals the 7303 error. I don't know how to cause the connection to the local mdb file to become available again. There is quite a bit of data in this mdb file that I need to compare to SQL data until we can move all data to SQL.
I didn't quite understand the previous poster's reference to 'rebuilding' their connection. is that dropping and recreating the linked server? When I try this manually, the newly recreated linked server still fails to connect(?) Thank you for looking (- and helping?)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply