7 years 10 months ago #13881 by alexm
Hello Support,

We use your OLEDB provider to synchronise data between our SQL Server OLTP database and a Redshift data warehouse.
In our data flow there are few occurrences where we use UPDATE in an OLEDB command in a data flow.

In many cases when DATE or DATETIME columns are updated, resulting values in the destination are completely different from what's specified in the query. For instance, we run the following code (caught by PGNP profiler):
---------- Client SQL 2016/02/01 15:29:30.230 D27BC981 ------------ UPDATE inventorytransfer SET fromstoreid = ?, tostoreid = ?, status = ?, createdate = ?, transferedby = ?, receivedby = ?, cancelledby = ?, transferdate = ?, receivedate = ?, canceldate = ?, "__$etl_lsn" = ? WHERE inventorytransferid = ?; ---------- Executed SQL Parse:0.000 Prepare:0.000 Execute:1307.024 GetRows:0.000 Rows:0 --------- EXECUTE "ae7c76d7f34ea77c5b728157b2de8bd1"([DBTYPE_I8,613],[DBTYPE_I8,401],[DBTYPE_WSTR,"Cancelled"],[DBTYPE_DBTIMESTAMP,2015-09-10 15:38:09.447],[DBTYPE_WSTR,"Richie"],[DBTYPE_WSTR,"Joshua"],[DBTYPE_WSTR,"RDS Test"],[DBTYPE_DBTIMESTAMP,2016-02-01 15:26:27.360],[DBTYPE_DBTIMESTAMP,2015-09-10 15:43:41.533],[DBTYPE_DBTIMESTAMP,2016-02-06 11:56:57.000],[DBTYPE_I8,245680],[DBTYPE_WSTR,"0000087B000368760024"])
But when we check the destination on completion, we find following DATETIME values there:
createdate = 2015-09-25 11:16:20
transferdate = 2016-02-16 11:16:20
receivedate = 2015-09-25 11:16:20
canceldate = 2016-02-21 07:16:20

These are completely different dates, not present in the profiler trace.

Could you please propose some workaround for it and if it's a bug, expected date when it's going to be fixed?

Thank you very much in advance.

Best Regards,

Please Log in or Create an account to join the conversation.

7 years 10 months ago #13882 by Moderator
Hi Alexander!
Could you send DDL for the table?

Please Log in or Create an account to join the conversation.

7 years 10 months ago #13884 by alexm
Please see DDL for the table below:
create table if not exists inventorytransfer ( inventorytransferid bigint not null encode raw, fromstoreid bigint null encode raw, tostoreid bigint null encode raw, status nvarchar (40) null encode raw, createdate datetime null encode raw, transferedby nvarchar (200) null encode raw, receivedby nvarchar (200) null encode raw, cancelledby nvarchar (200) null encode raw, transferdate datetime null encode raw, receivedate datetime null encode raw, canceldate datetime null encode raw, __$etl_lsn varchar(20) not null encode runlength, primary key (inventorytransferid), foreign key (fromstoreid) references store (storeid), foreign key (tostoreid) references store (storeid) ) INTERLEAVED SORTKEY (fromstoreid, tostoreid, createdate, transferdate, receivedate, canceldate);
We are using the OLEDB provider version, Redshift OLE DB Provider (BI Alias) (Provider=SQLOLEDBRSNP.1).
We haven't tried to reproduce the issue from C# because we are doing an ETL project which requires us to use SSIS.

Please Log in or Create an account to join the conversation.

Time to create page: 0.154 seconds
Powered by Kunena Forum