OracleConnection not working ()

Hello, I'm trying to establish a connection with an Oracle database using the OracleConnection and OracleSource objects in BimlExpress for Visual Studio Enterprise 2017  using the following script:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>

        <OracleConnection
        Name="oracleConn"
        ConnectionString="Data Source=XXX.XX.XX.XXX:XXX/XXX; User Id=XXXX;Password=XXXX;Integrated Security=no;"/>

    </Connections>

    <Packages>
        <Package Name="package1" ConstraintMode="Linear">
            <Tasks>
                <Dataflow Name="obtain data">
                    <Transformations>

                        <OracleSource Name="load table" Connection="oracleConn">
                            <ExternalTableInput Table="table1"/>
                        </OracleSource>

                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>

</Biml>

However, when I try to run the validation this message shows up:

Validating Biml
Error    0    Oracle Client Access not detected. Install the 32-bit or 64-bit version of Oracle Client Access that matches your version of Biml/BimlStudio. Biml Validator threw an exception attempting to validate your package. Exception type: InvalidOperationException        0    0
AstValidator. There were errors during compilation. See compiler output for more information.

I already have the Oracle client installed along with the Attunity connectors for Oracle found here and have used them for projects not involving Biml (obtaining tables from Oracle in Visual Studio using the connectors). So I really don't know what the problem could be other than Biml somehow not being able to access the client. Is there a way I can debug this or is there some known fix for this?

Hello,
I have exactly same issue. I was wondering if BIML supports new MS ORA Connector (For MS SQL2019) and not just the original Attunity drivers?
Did anyone manage to use MSSQL 2019, Oracle Connector and BIML?

Thank you
Vojtech

Hello, I think what you said is the case. However I managed to use the Oracle Connectors in BIML using the custom component found in this old bimlscript.com post. (it is a link to the wayback machine as I think it was deleted, however if you where to find those posts do tell me)

Also I created the Oracle Connection by using a custom SSIS connection as shown in the link, however I found the easiest way to do that was by creating an Oracle connection in a SSIS project, right clicking it and selection "Convert SSIS package to Biml" which then creates a .biml file with the custom SSIS connection.

And that's it, the custom component and the custom connection somehow get identified as the MS ORA connectors.

The result of this is the following script:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <CustomSsisConnection Name="ORA_CONN" CreationName="ORACLE" ObjectData="LARGE OBJECT DATA STRING OBTAINED FROM SSIS"/>
</Connections>
    <Packages>
        <Package Name="package1" ConstraintMode="Linear">
            <Tasks>
                <Dataflow Name="obtain data">
                    <Transformations>
                        <CustomComponent Name="LOAD" 
                        LocaleId="None"
                        Version="4"
                        UsesDispositions="true"
                        ComponentClassId="{CB67CD40-126C-4280-912D-2A625DFAFB66}"
                        ComponentTypeName="CB67CD40-126C-4280-912D-2A625DFAFB66"
                        ContactInfo="Oracle Source;Microsoft Connector for Oracle by Attunity; Attunity Ltd.; All Rights Reserved; http://www.attunity.com;4"
                        >
                            <Annotations>
                                <Annotation AnnotationType="Description">Microsoft Oracle Source Component by Attunity</Annotation>
                            </Annotations>
                            <CustomProperties>
                                <CustomProperty Name="BatchSize" DataType="Int32" SupportsExpression="true" Description="The number of rows fetched in a batch.">100000</CustomProperty>
                                <CustomProperty Name="PrefetchCount" DataType="Int32" SupportsExpression="true" Description="Number of pre-fetched rows.">0</CustomProperty>
                                <CustomProperty Name="LobChunkSize" DataType="Int32" SupportsExpression="true" Description="Determines the chunk size allocation for LOB columns">32768</CustomProperty>
                                <CustomProperty Name="DefaultCodePage" DataType="Int32" SupportsExpression="true" Description="The code page to use when code page information is unavailable from the data source.">1252</CustomProperty>
                                <CustomProperty Name="AccessMode" DataType="Int32" TypeConverter="AccessMode" Description="The mode used to access the database.">0</CustomProperty>
                                <CustomProperty Name="TableName" DataType="String" SupportsExpression="true" Description="The name of the table to be fetched.">"TABLE"</CustomProperty>
                                <CustomProperty Name="SqlCommand" DataType="String" SupportsExpression="true" Description="The SQL command to be executed."></CustomProperty>
                            </CustomProperties>
                            <Connections>
                                <Connection Name="MSOraConnection" ConnectionName="ORA_CONN"/>
                            </Connections>
                            <OutputPaths>
                                <OutputPath Name="Output"
                                            ErrorRowDisposition="FailComponent"
                                            TruncationRowDisposition="FailComponent"
                                            IsErrorOutput="false"
                                            >
                                </OutputPath>
                                
                                <OutputPath Name="Error"
                                            IsErrorOutput="true">
                                </OutputPath>
                            </OutputPaths>
                        </CustomComponent>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

I'm also having problems with with the new "Microsoft connector for Oracle" when using BIML express.<br> <br> We upgraded from (Visual Studio 2017 and SQL server 2017 Enterprise) to (Visual Studio 2019 and SQL server 2019 Enterprise) and now our ssis packages created with BIML express fails with XML encoding errors.<br> <br> When I view XML code for the SSIS package created with BIML Express, it looks like this:<br> <br> &nbsp; &nbsp; &nbsp; &nbsp; &lt;OracleXMLPackage&gt;<br> ?&nbsp;<br> ?&nbsp;<br> ?&nbsp;<br> ?&nbsp;<br> ?&nbsp;<br> ?&nbsp;<br> ?&nbsp;<br> ?&nbsp;<br> ?&nbsp;<br> ?&nbsp;<br> <br> Does anyone know if BIML express supports the latets Oracle drivers from microsoft (2019)?<br> Did anyone manage to use MSSQL 2019, Oracle Connector and BIML? Or does it only support the old Attunity drivers?<br> <br> Kindly regards&nbsp;<br> Jonas<br> <br> &nbsp;

Hi Jonas,<br> <br> yes, I manage to make it work. I have used a solution <a class="author-link" href="https://support.varigence.com/forums/bimlexpress-forum/1a01161f-3476-ec11-a81b-000d3ae505a1#new" title="Nan Qui Edo"> Nan Qui Edo</a>&nbsp; posted. Basically I have created a package with connection and converted package to BIML....From that I have taken the relevant part and put it into my template.<br> <br> Thank you<br> Vojtech <grammarly-desktop-integration data-grammarly-shadow-root="true"></grammarly-desktop-integration>