Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SSIS 2012 connection to Pervasive SQL V8 database

Status
Not open for further replies.
TheBugSlayer

TheBugSlayer

Programmer
Sep 22, 2002
887
US
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)
 
Sort by date Sort by votes
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:
SFqBIyF.png

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
 
Upvote 0 Downvote
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)
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Thanks mirtheil. I was able to upload the image. See below:

8imyuhm


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)
 
Upvote 0 Downvote
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)
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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)
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

ianbtheog
  • Locked
  • Question
Connection Manager is not running at the specified port number - Pervasive 13.31.014 - Intermittent
Replies
2
Views
545
ianbtheog
ianbtheog
dgillz
  • Locked
  • Question
DDF Builder for Actian fka Pervasive SQL
Replies
3
Views
533
mirtheil
mirtheil
profoundhypnotic
  • Locked
  • Question
How to read a csv file in Pervasive?
Replies
1
Views
578
mirtheil
mirtheil
dgillz
  • Locked
  • Question
How to Execute Create View code in Actian Zen?
Replies
1
Views
526
mirtheil
mirtheil
michellepace
  • Locked
  • Question
Source control for pervasive? (git)
Replies
1
Views
342
mirtheil
mirtheil

Part and Inventory Search

Sponsor

Back
Top