- Thank you received: 0
SSIS - Issue with DATE and DATETIME in UPDATE
- alexm
- Topic Author
- Offline
- User
-
Less
More
7 years 10 months ago #13881
by alexm
SSIS - Issue with DATE and DATETIME in UPDATE was created 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):
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.
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):
Code:
---------- 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"])
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.
- Moderator
-
- Offline
- New Member
Less
More
- Thank you received: 0
7 years 10 months ago #13882
by Moderator
Replied by Moderator on topic Re: SSIS - Issue with DATE and DATETIME in UPDATE
Hi Alexander!
Could you send DDL for the table?
Could you send DDL for the table?
Please Log in or Create an account to join the conversation.
- alexm
- Topic Author
- Offline
- User
-
Less
More
- Thank you received: 0
7 years 10 months ago #13884
by alexm
Replied by alexm on topic Re: SSIS - Issue with DATE and DATETIME in UPDATE
Hi,
Please see DDL for the table below:
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 see DDL for the table below:
Code:
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 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