Unable to import data into SQL Server from Progress DB, issue with data type conversion?

Question

I'm trying to import data from a Progress database into a MS SQL 2005 Server database.

On SQL Server, I'm right clicking on my schema name and going Tasks > Import Data... and running through the wizard.

I have an ODBC Connection to progress setup, no issues there, I also test my queries using ODBC Explorer first to ensure I have no syntax issues.

A statement that I'm using is as follows:

SELECT "MYTABLE"."FIRST-NAME",
       "MYTABLE"."LAST-NAME",
       "MYTABLE"."D-O-B"
FROM PUB."MYTABLE"

This works fine in ODBC Explorer, but when I try to use this in SSIS I get the following error

Executing (Error)
Messages
Error 0xc02090f5: Data Flow Task: The component "Source - Query" (1) was unable to process the data.
 (SQL Server Import and Export Wizard)



Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Source - Query" (1) returned error code 0xC02090F5.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED.  Thread "SourceThread0" has exited with error code 0xC0047038.  There may be error messages posted before this with more information on why the thread has exited.
 (SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: SSIS Error Code DTS_E_THREADCANCELLED.  Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.  There may be error messages posted before this with more information on why the thread was cancelled.
 (SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED.  Thread "WorkThread0" has exited with error code 0xC0047039.  There may be error messages posted before this with more information on why the thread has exited.
 (SQL Server Import and Export Wizard)

My first thought was maybe an issue between the Date data types between Progress and MSSQL, so I have tried TO_CHAR in my statement (tested in ODBC Explorer first) but that didn't resolve it, I've tried everything I can think of including

1. Using TO_CHAR in the Progress Select statement
2. During the data mappings in SSIS; try using Datetime, smalldatetime, nvarchar etc..
3. Using TO_CHAR and NVL
4. Increasing the size of all destination columns to 200 (no columns need more than 50 at current so this is more than enough)

Even removing that Date field from the select statement still produces the same error

Is there anything I have missed? Is it possible that the source data could be incorrect and not supported in SQL Server?

I have found some posts on MSDN suggestion that there could be an issue with data type conversion and there may also be an issue with the overflow of data in Progress columns

This seems to be an intermittent issue, I have other data import jobs from Progress that use dates and there are no issues (and yes, I have cross-referenced all settings to ensure I haven't missed something)

My only option seems to be to move data from Progress > Access (or some other DB) > MS SQL

Answer 1

When in doubt:

Progress --> CSV file --> SSIS --> SLQ Server

Answer 2

James, I have the same issue. The package works against a Progress DB my test environment but fails this same way against a db in my production environment. I am working on it.

Answer 3

The Progress DB stores all data as variable length. This often causes problems with databases that expect data to be fixed length. The solution is to run the "dbtool" utility.

dbtool is in the Progress "bin" directory. You want option #2 "SQL Width Scan w/Fix Option".

Answer 4

I've had good luck using a SQL Linked Server object to connect the Progress Database, via the Microsoft OLE DB Provider for ODBC Drivers. You need to use the OpenQuery object to query the linked server, which looks like:

select MyField, MyOtherField from OpenQuery ([MyLinkedServer],'select MyField, MyOtherField from PUB.My_ProgressTable where dtLastUpdated > {d ''2009-01-31''}') 

For the ODBC DSN setting the Default Isolation Level on the Advanced Tab to 'READ UNCOMMITTED'

This is using SQL 2005 against a Progress 10.1B db on a Linux server. Not the most elegant or efficient solution perhaps, but it's been pretty reliable.