Results 1 to 2 of 2

Thread: Attunity SSIS Oracle Conn. sees CLOB unicode column as non-unicode DT_TEXT stream

  1. #1
    tdeagan is offline Junior Member
    Join Date
    May 2015
    Posts
    2
    Rep Power
    0

    Attunity SSIS Oracle Conn. sees CLOB unicode column as non-unicode DT_TEXT stream

    I have an SSIS package used to pull Oracle tables into my SQL Server database using the Attunity 2.0 Oracle Connector.

    The package runs nightly from a list of tables and, due to sad unavoidable issues, cannot pull deltas. It recreates all tables completely each night, including any schema changes. The package builds a package for each table in the list using a package API script.

    Step one picks up schema changes and gives me a destination table by doing: Select * into tablename_tmp from openquery(ORACLE_SOURCE, 'select * from tablename where 1=0')
    Step two executes a (lengthy) script that builds Data Flow which includes an Oracle Source and a (fast load) OLE DB Destination. This destination must already exist for me to able to programtically map the metadata.
    (there are a number of ancillary logging steps and components which are not relevant.)

    Recently, on the Oracle side, a CLOB (unicode) column was added to a table I pull.
    - The openquery select into statement creates this column in SQL Server as an ntext (unicode column)
    - The Attunity Oracle Source sees the External Column and the Output Column as DT_TEXT text streams (non-unicode)
    - The OLE DB Destination sees the External Column as DT_NTEXT (unicode) and the Input Column as DT_TEXT (non-unicode)

    This results in an error: Error 1 Validation error. ... Column "COMMENTS" cannot convert between unicode and non-unicode string data types.

    So, my questions;
    1.) Is there any way to get the Attunity 2.0 Oracle Source to recognize the CLOB as a DT_NTEXT text stream?
    2.) Would the Attunity 3.0 Oracle Connector see the CLOB as unicode?

    I don't want to have to inspect each column programtically and am not sure how to force the ntext type in the Source if I do (I suppose I could force some conversion steps.) I'd rather just get everything to play nice.

    Thanks!!
    --Tim

  2. #2
    tdeagan is offline Junior Member
    Join Date
    May 2015
    Posts
    2
    Rep Power
    0

    maybe a matrix of conversion types?

    Oh well, no response to my questions, but my problem remains.

    Any chance someone has a matrix of Attunity 2.0 (and perhaps 3.0) SSIS Oracle Connector data type conversions (i.e. how Attunity converts Oracle data types to SQL data types?)

    thanks,
    --t

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •