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

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?


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/

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?


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/

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?


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/

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?


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/

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?


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/

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?


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/

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?


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/

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?


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/

Matija,

Thanks for the reply.

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


destination, its saying the datetime datatype is not supported.


"ML" <ML (AT) discussions (DOT) microsoft.com> wrote