November 1, 2012 at 9:23 am
Good Afternoon.
I was wondering if you could assit in the following please.
We have our Housing Management system that sits on a server with PROGRESS Version 10.1b on it.
At present we have a bunch of programes that extract each database table and exports it into a text file.
From these text files we import them into SQL2000!
We have installed SQL2012 in order to be our new central datawarehousing server.
Is there a way I can connect to the PROGRESS databases direct from within SQL? What would I need to do to achieve this?
By using a connection direct to the Progress database I could then aviod exporting to text and import straight from Progress into SQL after amending the data.
Thanks
November 1, 2012 at 9:33 am
Do you have a driver (for ODBC) which can connect to Progress? If so it would just be a case of installing the driver and setting up a linked server to use a DSN which will then link the two servers together.
Alternativly you could use something like SSIS to create a custom ETL package which does all of your exporting transforming and loading from Progess to SQL.
Side note, you could of done it this way in SQL 2000 as well, but using DTS instead of SSIS.
November 1, 2012 at 9:42 am
I dislike linked servers, especially do non-SQL databases since I have seen errors, especially memory errors from third party drivers in the past. This is usually over time, and in warehousing type scenarios where you pull lots of data.
I'd prefer to use something like SSIS and control the flow of data, using it to import data into SQL Server.
November 1, 2012 at 10:32 am
I prefer to go the SSIS route really.
What would I need - just the actual driver installed on the SQL server of the Progress DB?
November 1, 2012 at 2:41 pm
A driver installed on whatever machine is running SSIS. Usually this is the same SQL Server instance, but it does not have to be.
November 8, 2012 at 12:29 pm
Ryan Keast (11/1/2012)
I prefer to go the SSIS route really.What would I need - just the actual driver installed on the SQL server of the Progress DB?
If you can get an ADO NET compliant driver, it's real easy ... you pretty much just need to install the driver on whatever boxes will run the package (including your dev environment.)
If that's not available and you have to use an ODBC driver there is a bit more work. After installing the driver you have to set up an ODBC system dsn to use it. The package connection will use that system dsn, so that process has to be the same for each machine that will run the package.
If it is a 32-bit ODBC driver (which some of the similar drivers I have seen are), you need to use the 32-bit version of the ODBC admin tool (defaults to C:\Windows\SysWOW64\odbcad32.exe) to set it up. The job step that runs the package would need to be set to run in 32-bit. And for debugging, you would need to change your project settings to debug in 32-bit.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply