SSIS 2012 connection to Pervasive SQL V8 database
SSIS 2012 connection to Pervasive SQL V8 database
(OP)
Hello all.
I need help creating a connection to a Pervasive SQL V8 database from an SSIS 2012 package. So far I have no success. I choose "Native OLE DB\Pervasive.SQL V8 OLE DB Provider" as the provider. I have an IP address, user name and password and I am able to connect to and query the database through Pervasive SQL Data Manager from the same PC I am creating the package on.
In the connection manager configuration in SSDT, I am a little unsure ass to what the difference is between "Server or file name" and "Location". I enter server name in one, IP address in the other, hit Test Connection and I get
Why is the same user name and password used in the Pervasive client is not accepted from SSDT?
What is the correct provider to use and the correct procedure to connect to Pervasive from an SSIS package?
Thank you.
I need help creating a connection to a Pervasive SQL V8 database from an SSIS 2012 package. So far I have no success. I choose "Native OLE DB\Pervasive.SQL V8 OLE DB Provider" as the provider. I have an IP address, user name and password and I am able to connect to and query the database through Pervasive SQL Data Manager from the same PC I am creating the package on.
In the connection manager configuration in SSDT, I am a little unsure ass to what the difference is between "Server or file name" and "Location". I enter server name in one, IP address in the other, hit Test Connection and I get
.Quote ("Test connection failed because of an error in initializing provider. [LNA)
[Pervasive][ODBC Engine Interface][ODBC Engine Interface][Data Record Manager]Invalid user authorization specification"]
Why is the same user name and password used in the Pervasive client is not accepted from SSDT?
What is the correct provider to use and the correct procedure to connect to Pervasive from an SSIS package?
Thank you.
MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)
RE: SSIS 2012 connection to Pervasive SQL V8 database
The "Location" parameter is the computer name of the machine running the PSQL engine. The Location parameter was new in V8.
Using SQL Server 2008 and PSQL v11, the connection properties look like this:
Where "Data Source" is the Pervasive Database Name you want to access, Location is the machine name where PSQL is running. The User Name and Password are required if your database has security enabled (which based on the error, it probably does) and does not allow read only access without a username. "Master" is the admin user for a PSQL database if security has been enabled.
Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com
RE: SSIS 2012 connection to Pervasive SQL V8 database
Since time is of the essence here, I am coding the DB operations into a DLL that will then use in a Script Task. Later when the project is complete I will continue troubleshooting...
Thank you.
MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)
RE: SSIS 2012 connection to Pervasive SQL V8 database
I haven't installed SQL Server 2012 yet. I might have to try it this weekend.
Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com
RE: SSIS 2012 connection to Pervasive SQL V8 database
Well, it does not show in the preview...I will have to post to see.
MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)
RE: SSIS 2012 connection to Pervasive SQL V8 database
MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)
RE: SSIS 2012 connection to Pervasive SQL V8 database
If that doesn't work and you can use the database from VB.NET or C# code with the same user name / password, then there might be something in the interaction between SSIS and the OLEDB provider. Is ODBC an option? Can you use the OLEDB provider for ODBC drivers in the SSIS package? Does that work?
Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com
RE: SSIS 2012 connection to Pervasive SQL V8 database
MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)
RE: SSIS 2012 connection to Pervasive SQL V8 database
You might try registering the following dlls: POLEDB.DLL, POLEDBER.DLL, POLEDSY.dll, and CNSTRING.DLL. At least that's what I think the names are. I don't have a V8 machine to check.
Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com
RE: SSIS 2012 connection to Pervasive SQL V8 database
The DLL I created does not work inside the script task. I add a reference to it but it seems the task is not able to load it. What I did is to add the code to the script project itself. Now the package runs but not the way I wanted it to be, i.e. create a connection and run a query from an SQL task.
Thank you for your time.
MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)