SSIS 2012 connection to Pervasive SQL V8 database

[TheBugSlayer]

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.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[mirtheil]

I'm not familiar with the "Server or file name" option. Can you post a screen shot of where you're entering the PSQL connection information?
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

 

[TheBugSlayer]

mirheil, thanks. This is the first time I see an embedded image in a TT post. I had made a suggestion they add the feature to upload from a hard drive. So I still am unable to attach an image to this post. I am using SQL Server 2012. If you have it you should see it while trying to add a connection through the connection manager or otherwise. Sorry.

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)

 

[mirtheil]

It is only embedded in that it pulls from a web site. I used Imgur.com to host the image then added that image link to the post using the "Picture" button on the post.
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

 

[TheBugSlayer]

Thanks mirtheil. I was able to upload the image. See below:

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)

 

[TheBugSlayer]

Well, that did not work so I used a link. Thanks for sharing the site.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[mirtheil]

I don't remember that dialog from the V8 provider but it's been a while since I've tried with it. What I would suggest trying would be to specify the Pervasive Database Name in the "Server or file name" field and the server name in the "Location" parameter.

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

 

[TheBugSlayer]

I do not see ODBC in the list of providers. Remember I am using SQL Server 2012. Some error related to unable to initialize provider and whether cnstring.dll is registered properly...The script is failing...apparently unable to load the DLL. Going live tomorrow...Oh, well.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[mirtheil]

PSQL v8 is so old, it's hard for me to remember all the tricks with it. Too bad you can't upgrade to PSQL v11. It works with newer versions of SQL Server.

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

 

[TheBugSlayer]

I may still upgrade but for now I don't want to be distracted with a upgrade and its potential problems.

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)