SSIS Connection Manager Error

• Loudamy JD

  SSC-Addicted

  Points: 494

  More actions

  March 4, 2010 at 7:09 am

  #139152

  Here is the Error that I receive when I try to "Test Connection" on a Data Source:

  TITLE: Connection Manager

  ------------------------------

  Test connection failed because of an error in initializing provider. Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

  DBPROP_AUTH_INTEGRATION or DBPROP_AUTH_MASK_PASSWORD have been set incorrectly

  A little background. I developed a SSIS Package on my laptop using BIDS 2008, it contains a Data Flow Task that pulls from a Pervasive Database and inserts into a SQL 2008 DB. I had to install the Pervasive Client to use the drivers for this connection, the provider being ".Net Providers for OleDb\Pervasive.SQL OLE DB Provider". On my laptop this all worked as planned.

  I then copied my Project folder from my laptop to the server from which it will run. Before deploying this package I wanted to test it by executing it through Visual Studio, so when I opened the package and tested the connection I received the above error.

  I've verified that the connection is identical from my laptop where it works, to the server. I have tried running the package in 32bit mode as well as changing the "Mask Password" and "Cache Authentication" under the "All" section of the Connection Manager Properties.

  I've done some research but it is hard to find information about Pervasive as it relates to SQL 2008. Any ideas or help would be greatly appreciated, Also if more information is needed please let me know.

  Thank You.

• servercentral.10.bde123

  SSC Rookie

  Points: 47

  More actions

  April 23, 2010 at 12:09 pm

  #1157065

  In the connection manager settings dialog, select 'ALL' to bring up the OLEDB connection details.

  Under "Pooling" Change "OLE DB Services" from "Default" to "DisableAll"

  Some other settings seem to work as well - I'm not really sure what they all mean - a little experimentation might be in order.

  I've had much more success with the Pervasive .NET provider.