SSIS: Connecting to Oracle destination in Data Flow task

[Abba]

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

[ML]

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/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.dts&mid=b9225c3e-acc4-45b9-bbf3-51e870a8886a


ML

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

[Abba]

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.aspx?mkt=en-US&setlang=en-US&q=oracle+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.

Post by ML
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/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.dts&mid=b9225c3e-acc4-45b9-bbf3-51e870a8886a
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

[ML]

So could it be that the MSDN article is wrong? Have you tried asking about
data types in an Oracle newsgroup? Have you tried casting the source column
to another compatible Oracle data type (another date/time type that is listed
in the MSDN article)?


ML

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

[Abba]

Post by ML

Post by ML
So could it be that the MSDN article is wrong?

First of all, you did not point to a article. You pointed to a search. And
majority of articles mentions about Datareader provider while Iam using OLE
DB.

Post by ML

Have you tried asking about data types in an Oracle newsgroup?

Yes.No replies yet.

Post by ML

Have you tried casting the source column

to another compatible Oracle data type (another date/time type that is listed
in the MSDN article)?

Answered above.

Post by ML
So could it be that the MSDN article is wrong? Have you tried asking about
data types in an Oracle newsgroup? Have you tried casting the source column
to another compatible Oracle data type (another date/time type that is listed
in the MSDN article)?
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

[Gerald Aichholzer]

Hello Abba,

Post by Abba
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
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".

this of course cannot work, because - as the error message states -
the last column is read-only because it is an expression. You can't
make an expression writable.

I've done a quick search with Google and found the following article,
which might help you:

How to convert SQL Server datetime to Oracle timestamp (or other type
with similar precision)?
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1843532&SiteID=1


good luck and regards,
Gerald

[ML]

Judging from the posts in the forum thread using timestamp(3) is worth a try.


ML

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