Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 4 of 4 total
Thread SSIS Export + DBISAM ODBC. Why are null fields getting default values?
Fri, Dec 10 2010 11:29 AMPermanent Link

Craig

Hi,

I'm trying to export data from MSSQL 2008 using SSIS (Intigration Services, formerly DTS).  In MSSQL I have integer and date field values that are NULL.  When I export them to DBISAM using the ODBC driver, the driver fills them in with default values (. ie 0 for ints and 12/30/1899 for dates)  If I use the same SSIS project to export to excel or a flat file, the nulls stay null.  Only when I used the DBISAM ODBC driver do the fields get filled in.  The target application I'm exporting to expects the null fields to be blank in DBISAM.  I can't seem to figure out how to stop this.  Any ideas?

Craig.
Fri, Dec 10 2010 2:22 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Craig,

<< I'm trying to export data from MSSQL 2008 using SSIS (Intigration
Services, formerly DTS).  In MSSQL I have integer and date field values that
are NULL.  When I export them to DBISAM using the ODBC driver, the driver
fills them in with default values (. ie 0 for ints and 12/30/1899 for dates)
If I use the same SSIS project to export to excel or a flat file, the nulls
stay null.  Only when I used the DBISAM ODBC driver do the fields get filled
in.  The target application I'm exporting to expects the null fields to be
blank in DBISAM.  I can't seem to figure out how to stop this.  Any ideas?
>>

You're going to have to give me a week or so on this one.  I've got to set
up MS SQL Server 2008 and the whole source tables before I can test this,
and it isn't a huge priority right now.  I suspect that the issue is with
how the NULL flags are being set (if the inserts are happening in-place and
not via actual INSERT statements), but that's just a guess at this point.

--
Tim Young
Elevate Software
www.elevatesoft.com
Wed, Jan 5 2011 9:53 AMPermanent Link

Craig

Hi Tim,

I've also discovered that the ODBC driver is really buggy when it comes to columns with mixed null and non-null data.   It will fill in the null fields with data from other records and it doesn't appear to be from the records preceeding or following the ones with the null fields.  Weird.  It will also blank out non-null data when most of the fields in the same column are null.  I've managed to consistently re-create these problems.  Either breaking the data into two groups (nulls and non-nulls) and exporting them seperately or filling in the null fields with default values (ie 0 in integers fields), will make things work properly.

I'm hoping you can fix this driver.  I'm really relying on it to dump data from my MSSQL ETL back to DBISAM.  So far, it's extremely unreliable and creating a lot of manual checking of the data for me.

Thanks!

Craig.
Tue, Jan 11 2011 3:10 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Craig,

<< I've also discovered that the ODBC driver is really buggy when it comes
to columns with mixed null and non-null data.   It will fill in the null
fields with data from other records and it doesn't appear to be from the
records preceeding or following the ones with the null fields.  Weird.  It
will also blank out non-null data when most of the fields in the same column
are null.  I've managed to consistently re-create these problems.  Either
breaking the data into two groups (nulls and non-nulls) and exporting them
seperately or filling in the null fields with default values (ie 0 in
integers fields), will make things work properly.

I'm hoping you can fix this driver.  I'm really relying on it to dump data
from my MSSQL ETL back to DBISAM.  So far, it's extremely unreliable and
creating a lot of manual checking of the data for me. >>

I can guarantee you that this is most likely *not* a problem in the DBISAM
ODBC Driver.  95% of the time the issue is with the way that the application
is incorrectly using the ODBC specification.  Our ODBC drivers are
diligently written exactly according to the spec.

Please contact me via email (timyoung@elevatesoft.com).  I'm going to
require that you give me some customer information and have an active
support plan in order to track this one down.

--
Tim Young
Elevate Software
www.elevatesoft.com
Image