Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2011
    Posts
    8

    Question Pervasive to MS SQL 2008

    I am currently working on a project that is copying data from a Pervasive SQL application to MS SQL.

    Short-story, i am trying to use SSIS to transition various data tables to MS SQL for consumption in other applications that work with MS SQL and do not interact with Pervasive SQL.

    If anyone has successfully done this using the dtsexec utility, i would greatly appreciate some assistance.

    Problems, randomly it fails out on the ADO.Net source trying to attach to the Pervasive SQL source.

    Not sure if the fact that i have MaxConcurrentConnections set to -1 is a factor or not for the ADO.Net source but i am postulating that may be one of many of the problems.

    As well, if SSIS is not the best approach to copying data from Pervasive SQL to MS SQL, i would greatly appreciate the advise.

  2. #2
    Join Date
    Jan 2011
    Posts
    8
    If it matters data is coming from Sage's Timberline application to SQL Server 2008.

  3. #3
    Join Date
    Dec 2001
    Posts
    1,079
    A few questions:
    - You say it "randomly fails." What error are you getting?
    - Where do you have "MaxConcurrentConnections" set?
    - You say you're using ADO.NET. Are you using OdbcConnection, OleDbConnection, or PsqlConnection to access the PSQL data?
    - If you are using PsqlConnection, what version of Pervasive.Data.SqlClient.dll are you using?
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  4. #4
    Join Date
    Jan 2011
    Posts
    8
    - You say it "randomly fails." What error are you getting?
    > From the best i can tell it is do to either the connection timing out, too many concurrent connections to the database as it is, or the server concurrentconnections are getting bottlenecked and it is timing out. The jury is still out on this segment until i can get a successful run without using 'dtexecui'

    - Where do you have "MaxConcurrentConnections" set?
    > in the package itself, i have not setup an external configuration file as of yet, so its all internal to the package.

    - You say you're using ADO.NET. Are you using OdbcConnection, OleDbConnection, or PsqlConnection to access the PSQL data?
    > the base class is ODBC since the Timberline/Pervasive system don't do OleDB or any other type of connections. I am using System DSN ODBC connection and attaching to it within the SSIS package.

    - If you are using PsqlConnection, what version of Pervasive.Data.SqlClient.dll are you using?
    > Not doing any serious coding as of yet, since we are looking for a SSIS package style deployment with Workflow like processing. This package needs to be run every night at mid-night to update fields for the next day's processing of information by the corporate office.

    Side Note:
    I am currently reviewing a possible integration of LinkedServer into the MS SQL 2008 server for processing the data collection.

    This whole project is not to update the Timberline database but to simply use it as a core data warehouse where we copy information out of it and then massage it during the day with other client applications. At the end of the day, the comptroller then exports the data from the internal applications, as a CSV style doc, and then uploads it back into Timberline.

    This is my first exposure to Pervasive as a DBMS and am unimpressed with their exposure to the back end for extracting of information.

  5. #5
    Join Date
    Dec 2001
    Posts
    1,079
    - You say it "randomly fails." What error are you getting?
    > From the best i can tell it is do to either the connection timing out, too many concurrent connections to the database as it is, or the server concurrentconnections are getting bottlenecked and it is timing out. The jury is still out on this segment until i can get a successful run without using 'dtexecui'
    What is the exact error being returned? Once you give the exact error being returned by PSQL, it might help with narrowing down where the problem occurs.

    - Where do you have "MaxConcurrentConnections" set?
    > in the package itself, i have not setup an external configuration file as of yet, so its all internal to the package.
    So this is a DTS setting and not a PSQL setting. Is that correct?


    - You say you're using ADO.NET. Are you using OdbcConnection, OleDbConnection, or PsqlConnection to access the PSQL data?
    > the base class is ODBC since the Timberline/Pervasive system don't do OleDB or any other type of connections. I am using System DSN ODBC connection and attaching to it within the SSIS package.
    Actually, PSQL has supported OLEDB since PSQL 2000i and a Managed Provider interface since PSQL v8.7. If Timberline doesn't provide those interfaces, that's not a limitation of the database but of the application using the database. What version of PSQL are you using?

    - If you are using PsqlConnection, what version of Pervasive.Data.SqlClient.dll are you using?
    > Not doing any serious coding as of yet, since we are looking for a SSIS package style deployment with Workflow like processing. This package needs to be run every night at mid-night to update fields for the next day's processing of information by the corporate office.
    I understand.

    Side Note:
    I am currently reviewing a possible integration of LinkedServer into the MS SQL 2008 server for processing the data collection.
    That might work. It would be something to look into.

    This whole project is not to update the Timberline database but to simply use it as a core data warehouse where we copy information out of it and then massage it during the day with other client applications. At the end of the day, the comptroller then exports the data from the internal applications, as a CSV style doc, and then uploads it back into Timberline.
    This is pretty common and I know of a lot of people doing exactly what you are trying to do.

    This is my first exposure to Pervasive as a DBMS and am unimpressed with their exposure to the back end for extracting of information.
    I've always found that accessing Pervasive data has been very easy if the application generating the data doesn't prevent it. PSQL supports almost any programming language using a bunch of different interfaces including Btrieve API, ActiveX, ODBC, OLEDB, ADO.NET, COBOL, PDAC (Delphi VCL components), JCL (Java Class Library giving Btrieve style access), and JDBC.
    I do know of some applications that prevent access by not installing some of the PSQL interfaces (ODBC, OLEDB, etc). Don't blame the DBMS if the application vendor didn't provide a full PSQL.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  6. #6
    Join Date
    Jan 2011
    Posts
    8
    Im not putting a hit on PSQL, just ignorant of the bottleneck.

    After looking at the Timberline system and as well the PSQL system. I do not see the database in the PSQL system. Is there a situation where an application uses the PSQL system as a medium instead of as a storage location?

    All of the ODBC connections are broken once i try to use a Microsoft interface to access the information. Timberline has a one liner answer to all my questions about access to the information...."Can Excel or Access connect to the information through ODBC?"....which makes me wonder if they removed the remote component to the data and made it near impossible for exporting of data from their system into other DBMS.

    Im still in the discover phase of what kind of quick-sand i will be getting into.

  7. #7
    Join Date
    Jan 2011
    Posts
    8
    Quote Originally Posted by mirtheil View Post
    What is the exact error being returned? Actually, PSQL has supported OLEDB since PSQL 2000i and a Managed Provider interface since PSQL v8.7. If Timberline doesn't provide those interfaces, that's not a limitation of the database but of the application using the database. What version of PSQL are you using?
    And this i have seen all over the web but seems any attempt to get to the data from anything else besides the Timberline interface becomes near impossible.

    Will work on getting an error response code. Most of the time it has the Sage/Timberline namespace system but i "assumed" it was simply wrapping the error into their own system instead of it being a response from their system.

    Any pointers on how i can verify if the data is being stored directly into the Pervasive system or if it is abstracted from the system but using the Pervasive framework to handle the data relations?

    Quote Originally Posted by mirtheil View Post
    I've always found that accessing Pervasive data has been very easy if the application generating the data doesn't prevent it. PSQL supports almost any programming language using a bunch of different interfaces including Btrieve API, ActiveX, ODBC, OLEDB, ADO.NET, COBOL, PDAC (Delphi VCL components), JCL (Java Class Library giving Btrieve style access), and JDBC.
    I do know of some applications that prevent access by not installing some of the PSQL interfaces (ODBC, OLEDB, etc). Don't blame the DBMS if the application vendor didn't provide a full PSQL.
    Wish i could get ahold of someone that has had to do a connection to PSQL 10.10 through MS SQL 2k8 from either a SSIS (DTS) package or LinkedServer.

    So since the PSQL server has the default databases setup, do i need to test the default databases compared to the Timberline database?

  8. #8
    Join Date
    Dec 2001
    Posts
    1,079
    I didn't try with 10.10 but I did use 11.00 and was able to read the PSQL DEMODATA Class table using the "Import and Export Data" tool in MS SQL 2008 R2. Here's a screenshot of it after running the SSIS package:


    I would suggest testing with the default databases. I also attached the dtsx file that was created.
    One thing to be aware of is that I'm using a full version of PSQL from the Pervasive web site. I am not using the version that ships with Timberline (or any other app vendor). I do this because I am a developer that uses the PSQL engine in my apps.

    Any pointers on how i can verify if the data is being stored directly into the Pervasive system or if it is abstracted from the system but using the Pervasive framework to handle the data relations?
    I can say with certainty that Timberline does use the PSQL engine for it's data storage. How it stores it is entirely dependent on Timberline. If you have DDFs (FILE.DDF, FIELD.DDF, INDEX.DDF, and any other DDF files) and the data files, you should be able to create an ODBC DSN pointing to the data and access it.

    One ting you might try is to create a test system and install the PSQL trial from the Pervasive website (Pervasive Database). Then copy the DDFs and data files from the Timberline server to the test system. Then try to access the data on that test system. First, use the Pervasive Control Center (to make sure the data is accessible). Then, use the tool of your choice.
    Attached Files Attached Files
    Last edited by mirtheil; 01-10-11 at 22:19. Reason: edit for clarity
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  9. #9
    Join Date
    Jan 2011
    Posts
    8
    I will try that tomorrow.

    what is the keystone file i should be looking for?

    Also, why would dtexecui successfully run the package but dtexec and sql server implementations do not?

  10. #10
    Join Date
    Dec 2001
    Posts
    1,079
    I'm not sure what you mean by "keystone file." All of the PSQL trials are listed here: Pervasive PSQL v11
    No idea why one way would work but another would fail. Permissions might be a possibility but that would only apply if dtexec is running as a different user than what's logged in.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  11. #11
    Join Date
    Jan 2011
    Posts
    8

    Extensen you

    Would there be a problem to transition from 32-bit Timberline to 64-bit SQL Server.

    I know you can downgrade the package to run in 32-bit but seems the Server is still trying to find the DSN in the 64-bit ODBC instead of checking the 32-bit ODBC.

  12. #12
    Join Date
    Jan 2011
    Posts
    8
    Quote Originally Posted by goldbishop View Post
    what is the keystone file i should be looking for?
    The Pervasive system that Timberline has implemented resembles alot like the InSQL system i have seen used by Wonderware. All the tables have physical files and the application uses a "keystone" file to link everything together. This is just my sky-view look at the Timberline system right now.

    Still learning the Timberline application so learning something new everyday.

  13. #13
    Join Date
    Dec 2001
    Posts
    1,079
    The 64bit / 32 bit could be an issue in connecting but it wouldn't cause random errors. It just wouldn't work all the time. SOunds like the server is still running in 64 bit mode rather than 32 bit mode.
    The "keystone" files are probably the DDF files (FILE.DDF, FIELD.DDF, INDEX.DDF, and up to 10 other DDF files). Those files describe the data files. FILE stores table name and physical file name. FIELD stores the field definitions of the tables in FILE. INDEX stores the indexes on the fields in FIELD.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •