Unanswered: Automate export to text file or to MS SQL?
I know this might be a dead horse, from what I've seen thus far in this forum, but i don't see an actual answer. I have a Pervasive SQL DB v8 Server and a MS SQL 2000 Server. I need to get data from Pervasive to Microsoft nightly for data analysis and reporting. I know of a few possible scenarios but each has their own issues so i'm asking for a little guidance and advice:
Option 1: Use Data Transformation Services in Enterprise Manager. Easy to manage and schedule.
Problem 1: For starters, I don't have the Pervasive ODBC driver on the MSSQL Server and from what I can see I would need to install the PSQL DB just to get the ODBC drivers, which is not going to happen. Is there a way to get the ODBC onto this server without a full install?
Option 2: Automate some sort of export in PSQL.
Problem 2: My understanding is that PSQL has no automation or scheduling tools...
Option 3: Write a .Net app to handle the entire process, read from PSQL and insert into MSSQL.
Problem 3: I have no idea how to automate my own app. Scheduler? Services?
Any insight into any option would be greatly appreciated. I'm leaning towards #3, I think I'll have a better chance finding support and more option on scheduling my app... Thanks!!
To answer your question in Option 1, you need to install the Pervasive Client or the Pervasive engine depending on where the data is located. If the data is located on the same server as the MS SQL server and that server is running PSQL already, you may not need anything. If MS SQL is running on a different server than the PSQL engine, you would need to install the PSQL client. That is the only way to get the Pervasive ODBC drivers.
Option 2, PSQL does not have any scheduling or automation tools. You can use the integrated OS scheduling to run programs.
Option 3, I would suggest the WIndows Scheduler to automate any custom app. It'll be easier and quicker to develop. You could put a timer in your app if you are wanting to sync the data more often.
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.