I just started working on a data conversions team. Basically, they get a data dump from a non-Progress db, figure out how to map the data, and put it in text files for the conversion program to use to get it into their Progress db.
Our team leader is wondering if there is a browser-type tool in Progress (or elsewhere, if not) to connect to other databases (SQL, Sybase, mySQL) so we can see its schema, map their fields to ours, and transfer their data directly to our Progress db. Right now they have to manipulate the raw data A LOT.
I appreciate any help you can give me. Thanks!
BizTalk seems to be the norm for the M$ side.
It can map incoming fields to fields you define that you want to
import to the Progress DB.
As far as integrating directly to Progress DB's. I'm not so sure about that.
On Wed, Nov 25, 2009 at 12:05 PM, Teresa Schenkel
One potentially important question might be whether this is an on-going need or whether it is a one-time transfer.
I would not expect to find a one-step converter like this if for no other reason than that the access to the other databases is SQL, but you probably want to be using ABL to do the mapping on the OE side. If there is a transform tool, it would have to be all SQL, which might be less fun. But, there are certainly plenty of free SQL tools for reading the foreign databases, including schema, and writing queries to export data which you can then massage in ABL.
On the other hand, a bit of googling produced this http://www.iwaysoftware.com/products/etl_data_migrator.html which does support Progress.
If this is an on-going transfer, then you might have justification to spend a little money on the process. The first thing I would do then is to contact your PSC sales rep and explain that you really, really wish that DataXtend would support an OE database. That would give you exactly what you need since you would provide semantic mappings from the data in one database to the data in the OE database and DataXtend would automatically keep the databases in sync in the background. Failing in that, if you have the ability to write code against the foreign databases, you could implement Sonic and keep them in sync via messages about each change. If you don't need them that current, then it is back to the batch job approach, but then I would probably go with considering the export and import as separate steps, the former a SQL job run against the foreign database and the latter an ABL job run against your OE database. You could also use DataServer to query the foreign database, but if all you are doing is once a day dumps or some such, then I can't see it being justified.
This is a one-time occurance. They're moving from an old software application with a non-Progress database to our software application that uses a Progress database. We dump the data out of their old db. Try to figure out how to match up their tables and fields with those in our Progress db. Do a TON of data manipulation. Then we run a conversion utility to get their exported data (that we've totally massaged!) into an empty Progress db that will be used by our software application. We don't need to keep syncing it; we just need to get the data from point A to point B.
I hope I explained it better this time!
Do you know anything about Progress DataDirect?
For a one-time process, there really is no magic. Some migration tool might help a tiny bit, but really all the heavy lifting is in figuring out the sematic map between the old system and the new. Even there, there is no magic answer. In some cases you will find that you want to write SQL on the old system to create a special dump combining information from multiple files or summarizing information while in others you will be better off just doing a straight dump from the old and doing your special manipulation on the new. The programming is rarely difficult ... it is figuring out how to convert one semantic map into the other that is hard. Been there, done that, many times over.
Thank you for the info!