SSIS: Using Data Flow to load data to Oracle

[Abba]

Hello,

SQLserver Table - Audit_Trx
------------------
[TrxID] [char](11) NOT NULL,
[ProdSKU] [int] NOT NULL,
[AcctNum] [int] NOT NULL,
[InsertDate] [datetime] NULL,
)


Oracle table - TranAudit
------------
TRXID NOT NULL VARCHAR2(11 CHAR)
PRODUCT NUMBER(10)
ACCTNUM NUMBER(10)
INSERTDATE TIMESTAMP(6)


I am trying to load from SQL to Oracle. For that:
(1) I created a "Data Flow task" in SSIS and in the source, I have this
query:

select TrxID,
ProdSKU,
AcctNum,
ISNULL(CONVERT(VARCHAR,getdate(),101),0) as InsertDate
from Audit_Trx

(2) Then there is 'Data Conversion' where I have performed the following
conversions:
- TrxID - String[DT-STR]
- ProdSKU - 4 byte signed integer
- AcctNum - 4 byte signed integer
- InsertDate - DT_DBTimeStamp

(3) Then , I have selected the Oracle table as destination.When I select
'table or view' as 'Data access mode', it throws an error that 'datatype is
not supported'. So I have selected 'sql command' as 'Data access mode' and
enterd this:
SELECT Trxid,product,acctnum,
TO_CHAR(InsertDate,'mm/dd/yyyy') as InsertDate
from TranAudit

In the 'mappings', I have selected the respective 'data conversion.' as
against the Oracle columns.

Question: But this is giving me validation warning that "failure inserting
into read_only column InsertDate". When I remove the InsertDate column from
SQL query and also from Oracle , there are no errors. Also, I tried changing
InsertDate to DT_DATE and DT_DBDATE but still same error.

Can anyone help?


TIA,
Abba

[ML]

Post by Abba
select TrxID,
ProdSKU,
AcctNum,
ISNULL(CONVERT(VARCHAR,getdate(),101),0) as InsertDate
from Audit_Trx

Why are you converting date/time values to varchar? What are you actually
trying to achieve here? When do you expect CONVERT(VARCHAR,getdate(),101) to
be null?

Post by Abba
SELECT Trxid,product,acctnum,
TO_CHAR(InsertDate,'mm/dd/yyyy') as InsertDate
from TranAudit

The transformation around the InsertDate column makes the resulting column
read-only if you intend to use the query as a destination.


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

[Abba]

Matija,

Thanks for the reply.

Post by ML
Why are you converting date/time values to varchar? What are you actually
trying to achieve here?

The date column in Oracle is defined as TIMESTAMP(6) and the one in
SQLServer is DATETIME. A sample date value in Oracle is 10/10/2008
00:00:00.000000 and for SQL, it is 2008-11-24 03:06:38.660. Thus you can see
there is a mismatch.

By CONVERT(VARCHAR,getdate(),101), I get the SQL datetime as mm/dd/yyyy and
by TO_CHAR(InsertDate,'mm/dd/yyyy') , I get the Oracle also as mm/dd/yyyy

Post by ML

Post by ML
When do you expect CONVERT(VARCHAR,getdate(),101) to

be null?

That was on oversight.Sorry.

Post by ML

Post by ML
The transformation around the InsertDate column makes the resulting column

read-only if you intend to use the query as a destination

What other options do I have? When I directly select the name as
destination, its saying the datetime datatype is not supported.

Post by ML

Post by ML
select TrxID,
ProdSKU,
AcctNum,
ISNULL(CONVERT(VARCHAR,getdate(),101),0) as InsertDate
from Audit_Trx

Why are you converting date/time values to varchar? What are you actually
trying to achieve here? When do you expect CONVERT(VARCHAR,getdate(),101) to
be null?

Post by ML
SELECT Trxid,product,acctnum,
TO_CHAR(InsertDate,'mm/dd/yyyy') as InsertDate
from TranAudit

The transformation around the InsertDate column makes the resulting column
read-only if you intend to use the query as a destination.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

[ML]

You're confusing display formats of date/time values with the way they're
stored.

I'm not familiar with Oracle data types, but I'm pretty sure the mapping to
SQL Server data types is available in documentation. Have you tried searching
for it on MSDN?

E.g.
http://search.microsoft.com/results.aspx?mkt=en-US&setlang=en-US&q=oracle+data+types


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/