topic DataFlux bulkload to SQL Server with datetimeoffset in SAS Data Management

DataFlux bulkload to SQL Server with datetimeoffset

HeidiDT — Mon, 31 Oct 2022 06:07:06 GMT

Hi All

I am ding a load of 183m rows from Dataflux into a SQL Server table. The current job is just a normal insert, but I am playing around with the bulk row count option to see if I can't speed it up (it ran for 42 hours). However, I am getting an error with a datetimeoffset field. The field seems to be a character value in Dataflux, so I would have thought it would be coerced into a datetime value, but I get this error:

[4:DEST_ODBC:Consolidated Customer Matchcode] Data Access Plugin - Max. ODBC error count (49999) exceeded. Last error: [22018] [DataFlux][ODBC SQL Server Wire Protocol driver]Invalid character value. Error in parameter 11. (0);[HY008] [DataFlux][ODBC SQL Server Wire Protocol driver]Operation cancelled. Error in parameter 116. (0).

I then created a new date variable and converted the character value to a date field (date DOB DOB=todate(left(DateOfBirth,10)), and mapped that to the datetimeoffset field, but I am still getting the same error. Has anyone had any experience with this, and can offer some suggestions, please?

To complicate matters further, it runs fine locally but gives that error when running on the server, so I assume it is using different ODBC drivers.

Re: DataFlux bulkload to SQL Server with datetimeoffset

SASKiwi — Tue, 01 Nov 2022 01:54:07 GMT

183m rows is a lot for DataFlux. If you by any chance have SAS/ACCESS interface to ODBC or SQL Server installed on your SAS DQ platform it will likely perform faster. This is what I would do but you may not have that as an option. Tech Support is probably your best option for solving tricky DataFlux issues like this.

Re: DataFlux bulkload to SQL Server with datetimeoffset

VincentRejany — Tue, 08 Nov 2022 20:41:17 GMT

In theory, there should be no issues for inserting such data volume with DM Studio. I have done much more in the past.

For the record, DataFlux and SAS/ACCESS uses the same client (DataDirect ODBC Driver for SQL Server)

However, bulk loading is very format sensitive, so you should make sure that dates are mapped with dates and so on.

Mind that as soon as you change the bulk load option, the commit interval options are not longer active

Re: DataFlux bulkload to SQL Server with datetimeoffset

RacheLGomez123 — Mon, 21 Nov 2022 07:37:31 GMT

You can enable bulk loading to most Microsoft SQL Server databases by specifying options in the odbc.ini file (for UNIX platforms) or by using the ODBC Administrator (for Windows platforms). For more information, see the ODBC driver information about bulk loading available at Progress DataDirect.

To bulk load data to most Microsoft SQL Server databases, use the INSERTBUFF= LIBNAME option or INSERTBUFF= data set option to configure bulk loading with Microsoft SQL Server.

This may help you,
Rachel Gomez