×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SSIS 2012 connection to Pervasive SQL V8 database

thread318-1717002

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

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

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

RE: SSIS 2012 connection to Pervasive SQL V8 database

(OP)
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)

RE: SSIS 2012 connection to Pervasive SQL V8 database

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

RE: SSIS 2012 connection to Pervasive SQL V8 database

(OP)
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)

RE: SSIS 2012 connection to Pervasive SQL V8 database

(OP)
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)

RE: SSIS 2012 connection to Pervasive SQL V8 database

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

RE: SSIS 2012 connection to Pervasive SQL V8 database

(OP)
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)

RE: SSIS 2012 connection to Pervasive SQL V8 database

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

RE: SSIS 2012 connection to Pervasive SQL V8 database

(OP)
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)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close