SSIS - Issue with DATE and DATETIME in UPDATE

More
3 years 8 months ago #13881 by alexm
alexm created the topic: SSIS - Issue with DATE and DATETIME in UPDATE
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,
Alexander.

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

More
3 years 8 months ago #13882 by Moderator
Moderator replied the topic: Re: SSIS - Issue with DATE and DATETIME in UPDATE
Hi Alexander!
Could you send DDL for the table?

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

More
3 years 8 months ago #13884 by alexm
alexm replied the topic: Re: SSIS - Issue with DATE and DATETIME in UPDATE
Hi,
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 1.4.0.3392, 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.047 seconds
Powered by Kunena Forum