April 1, 2019 at 1:45 pm
I have following table structure in Oracle (Destination): (This is the same structure we have in DB2 as Source)
CREATE TABLE "SCOTT"."TLAnticipatory"
("ACCOUNT_NUMBER" VARCHAR2(15 BYTE),
"PROFILE_METRIC" VARCHAR2(8 BYTE),
"DRCR_INDICATOR" VARCHAR2(1 BYTE),
"PROFILE_PERIOD_UNIT" VARCHAR2(1 BYTE),
"PERIOD_AVG_TXN_COUNT" NUMBER(*,0),
"PERIOD_AVG_TOT_TXN_AMT" NUMBER(*,0),
"FIC_MIS_DATE" DATE,
"DATA_ORIGIN" VARCHAR2(3 BYTE)
)
I tried to import csv using import/export and done it without any issue. However for large tables, I need to use Microsoft Attunity Connector for Oracle to import (version 4) (Fast Load using Direct Path) using SSIS (Visual Studio 2015). This works well when I am doing data import from DB2 tables to Oracle directly using this said connector, to import 32 million of rows in seconds. But the strategy is to first export DB2 data in csv and then import in oracle from that csv (to release the DB2 source database as soon as possible-Incase of any error in loading to destination, we do not need to engage source db server again). I used to export DB2 tables data to .csv, now When I am trying to convert date and numeric columns from .csv to Oracle using attunity connector, it is giving me "DataType conversion of input column is not supported..." This is frustrating and I tried several ways with data conversion/derived column logic, all at the end gives me same error.
"DataType conversion of input column is not supported".
My situation is also well explained here:
https://stackoverflow.com/questions/42907312/attunity-ssis-datatype-oracle/55453773#55453773
a sample data is here,
"0001-006564-259"|"CHK"|"D"|"M"|"2"|"25000"|20180228|"RHC"
"0001-006564-259"|"CHK"|"D"|"M"|"2"|"25000"|20180228|"RHC"
"0001-009130-259"|"CHK"|"D"|"M"|"1"|"25000"|20180228|"RHC"
"0001-010588-259"|"CHK"|"D"|"M"|"1"|"25000"|20180228|"RHC"
"0001-011746-259"|"CHK"|"D"|"M"|"1"|"25000"|20180228|"RHC"
"0001-012793-259"|"CHK"|"D"|"M"|"1"|"25000"|20180228|"RHC"
"0001-012793-259"|"CHK"|"D"|"M"|"1"|"25000"|20180228|"RHC"
"0001-014141-259"|"CHK"|"D"|"M"|"1"|"25000"|20180228|"RHC"
"0001-015318-259"|"CHK"|"D"|"M"|"1"|"25000"|20180228|"RHC"
"0001-018120-259"|"CHK"|"D"|"M"|"1"|"25000"|20180228|"RHC"
"0001-018120-259"|"USD-CASH"|"D"|"M"|"1"|"25000"|20180228|"RHC"
"0001-020524-259"|"CHK"|"D"|"M"|"1"|"25000"|20180228|"RHC"
"0001-021199-259"|"CHK"|"D"|"M"|"2"|"25000"|20180228|"RHC"
"0001-021261-259"|"CHK"|"D"|"M"|"1"|"25000"|20180228|"RHC"
"0001-021300-259"|"CHK"|"D"|"M"|"1"|"25000"|20180228|"RHC"
"0001-021504-259"|"CHK"|"D"|"M"|"1"|"25000"|20180228|"RHC"
"0001-021555-259"|"CHK"|"D"|"M"|"1"|"25000"|20180228|"RHC"
"0001-021571-259"|"CHK"|"D"|"M"|"1"|"25000"|20180228|"RHC"
"0001-021610-259"|"CHK"|"D"|"M"|"1"|"25000"|20180228|"RHC"
"0001-021660-259"|"CHK"|"D"|"M"|"2"|"25000"|20180228|"RHC"
"0001-021717-259"|"CHK"|"D"|"M"|"1"|"25000"|20180228|"RHC"
"0001-022039-259"|"CHK"|"D"|"M"|"1"|"25000"|20180228|"RHC"
"0001-022381-259"|"CHK"|"D"|"M"|"1"|"25000"|20180228|"RHC"
Remember that using attunity, I can easily import this data from DB2 table to Oracle directly and I did 32 million of rows in seconds, but all I am failing to do it export this data into .csv and then import flat file to Oracle using Attunity.
I wanted to share the dtsx package but the look of this site is totally changed, can't see the option to upload picture or file.
Please guide...
April 1, 2019 at 11:07 pm
From what I see above, it looks like there's most likely an implicit conversion between the integer source value and date target value.
I would try to either encapsulate the date field with quotes in your text file, so that it is interpreted by the SSIS task as a string. Alternatively you can also try the advanced editor in SSIS to force a datatype for this source field.
April 2, 2019 at 5:33 am
Tried several options for date, "2019-02-28" with and without quotes, tried other different formats like 20180228, "28-Feb-2018" etc. and also tried advanced editor to provide DT_DBDATE all tried when frustrated get tired invest lots of time failing then came here to ask. If someone had done this practically and solved, let me know plz.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply