Hello,
[SQL2005]

SQL table
=======
[TrxID] [char](11) NOT NULL,
[ProdSKU] [int] NOT NULL,
[AcctNum] [int] NOT NULL,
[InsertDate] [datetime] NULL,


Oracle Table
=========
TRXID NOT NULL VARCHAR2(11 CHAR)
PRODUCT NUMBER(10)
ACCTNUM NUMBER(10)
INSERTDATE TIMESTAMP(6)

I am creating a Data Flow task that will transfer data from SQL to Oracle.
Since I have a WHERE filter, I choose SQL command for SQL source. I cannot
choose 'Table or view' in Oracle destination as I get an error saying
"Datatype is not supported". I think its the Oracle Timestamp datatype. So I
choose "SQL command" and write:
SELECT TRXID, PRODUCT, ACCTNUM,TO_CHAR(InsertDate,'mm/dd/yyyy') as
InsertDate
FROM OracleTable

But on 'preview', its giving me the error "Failure inserting to read_only
column InsertDate'. I had to put TO_CHAR... prefix as InsertDate alone is
giving me the same "Datatype is not supported error". I also have a data
conversion task in between that does the conversion as:

TrxId - String [DT_STR]
Product - Numeric [DT_NUMERIC]
AcctNum - Numeric [DT_NUMERIC]
Insertdate - String [DT_STR]

I tried changing Insertdate to Datetimestamp but same error.

How can I accomplish this?



TIA,
AbbA

Why are you starting a new thread when you haven't posted back in the
existing thread dealing with the same issue?
http://www.microsoft.com/communities...3-51e870a8886a


ML

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

Why are you starting a new thread when you haven't posted back in the
existing thread dealing with the same issue?
http://www.microsoft.com/communities...3-51e870a8886a


ML

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

Why are you starting a new thread when you haven't posted back in the
existing thread dealing with the same issue?
http://www.microsoft.com/communities...3-51e870a8886a


ML

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

Why are you starting a new thread when you haven't posted back in the
existing thread dealing with the same issue?
http://www.microsoft.com/communities...3-51e870a8886a


ML

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

Why are you starting a new thread when you haven't posted back in the
existing thread dealing with the same issue?
http://www.microsoft.com/communities...3-51e870a8886a


ML

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

Why are you starting a new thread when you haven't posted back in the
existing thread dealing with the same issue?
http://www.microsoft.com/communities...3-51e870a8886a


ML

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

Why are you starting a new thread when you haven't posted back in the
existing thread dealing with the same issue?
http://www.microsoft.com/communities...3-51e870a8886a


ML

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

Why are you starting a new thread when you haven't posted back in the
existing thread dealing with the same issue?
http://www.microsoft.com/communities...3-51e870a8886a


ML

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

This was your reply for that thread:

"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....cle+data+types "

I do not know what to answer when you have mentioned that you are not
familiar with Oracle data types. Regarding the mapping, it does not work as
I have stated that SSIS does not recognise the Oracle TIMESTAMP.


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