1

I have a progress openedge server which I have an ODBC connection to. I can read and write to the server through ODBC with SSIS from a MSSQL database. I've used SSIS to write to a test database but because I now want to write to live I'm more hesitant I've been trying to find documentation from progress to give me a step by step, what to look out for etc.

I can't find this, I can only find Progress to MSSQL but I've checked the collation and it seems compatible iso8859-1 to Latin. I've never worked with progress before so I'm now looking at using ABL but if using SSIS is ok I would prefer to use this method.

If anyone has used this method before I would appreciate some advice. My main concern is does the ODBC connection work well the other way around.

I don't see any issues from my understanding of databases, if I can populate the fields with data that conforms to the constraints and the conversion doesn't drop 0s or any other corruption once the data is there an application will pick it up.

The main issue I've read about is if the collation is different or incompatible I need to make sure it's converted but I'm confident this isn't an issue.

Any responses are welcome, I'm probably over thinking this.

1

Progress' collation tables are to control how indexes are sorted and assigned and some character handling if you are operating across several locales. Open Edge comes with a variety of collation tables for popular languages. The purpose is to help with sorting and comparisons. You can specify collation tables upon startup. If you do not but name a database in startup Progress will use the collation table for that database. If you do not specify either then Progress will use a collation table called BASIC.

As for MS SQL: both databases have data in the same language, correct? And your ODBC test was from SQLServer -> Open Edge which was successful. Some things to watch out for:

  • If you are using any "only in SQLServer" datatypes the data could get corrupted. Open Edge has a match for all types they say, but I have not tried them all. If you are sticking with the standard ones VARCHAR, INT, NUMERIC, DATE, TEXT you should be fine. If you have any binary columns Open Edge will convert those to character, so they will not operate the same. The Open Edge documentation lists the datatype equivalents (https://www.progress.com/documentation).

In case you are interested, Open Edge does have a SQLServer specific DataDirect branded ODBC driver you can use. It is intended to make connections to SQLServer easier.

  • Make sure you have the Open Edge driver on the MS ODBC Data Source Administrator box you will use.

  • I've heard of issues with the 64 bit versions of various drivers and the ODBC Data Source Administrator. I do not know if they are now fixed in late 2017, but if you have the 32-bit versions they may be more compatible all around.

How To options:

  • You can use SSIS. Build your package and run it to import the data into SQL Server. If you want to try any automation make sure that the SQL Server Agent job runs on the same server as your ODBC Data Source Administrator and the user has the privileges to all of those areas.

  • You can use the SQL Server Import/Export wizard to move your data. This might be the easiest way. Setup your data source and target and import data from Open Edge. They do note that any binary data will be converted to "datatype 23", and you have to manually go to each column definition and set the datatype in the dropdown to binary. To play it safe, check the datatypes after you import.

  • Another option, if you wanted to go this way, is to use free ETL tools to move your data. You don't say how much, how complex or how often; but ETL is another choice. This would remove the connection concerns since these packages typically come with a variety of connectors for many types of data sources. If that interests you, you can check out this ETL wiki for options.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.