Using Oracle Source & Attunity in BIML

Question

I would like to transfer data from oracle to SQL with biml.

So far I was able to create a (simple) package to transfer data manualy. This package contains a SQL_Destination-Connection (Native OLEDB), an Oracle-Source Connection (oracle connection manager\attunity) an Oracle Source Task and an OleDBDestination.

After creation of this package I tried to convert this package to BIML but did only get a "CustomSsisConnection" (=Oracle Source-Connection) and a "Connection" (SQL-Destination-Connection). The Package itself is not included in the new biml-script.

Based on the created Connections I tried to "code" the package on my own and did come up with this code:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <Connection Name="SQL.DWH_NEW" ConnectionString="data source=SOMESERVER;initial catalog=DWH_NEW;provider=SQLNCLI11.1;integrated security=SSPI;auto translate=False;" />
        <CustomSsisConnection Name="OracleSource" CreationName="ORACLE" ObjectData="&lt;DTS:ConnectionManager xmlns:DTS=&quot;www.microsoft.com/SqlServer/Dts&quot;&gt;&#xD;&#xA;  &lt;OraConnectionString&gt;SERVER=SomeOracleServer;USERNAME=myUser;WINAUTH=0;data source=SomeOracleServer;user id=myUser;&lt;/OraConnectionString&gt;&#xD;&#xA;  &lt;OraPassword Sensitive=&quot;1&quot;&gt;&lt;/OraPassword&gt;&#xD;&#xA;  &lt;OraRetain&gt;False&lt;/OraRetain&gt;&#xD;&#xA;  &lt;OraInitialCatalog&gt;&lt;/OraInitialCatalog&gt;&#xD;&#xA;  &lt;OraServerName&gt;SomeOracleServer&lt;/OraServerName&gt;&#xD;&#xA;  &lt;OraUserName&gt;ext-bi-pg&lt;/OraUserName&gt;&#xD;&#xA;  &lt;OraOracleHome&gt;&lt;/OraOracleHome&gt;&#xD;&#xA;  &lt;OraOracleHome64&gt;&lt;/OraOracleHome64&gt;&#xD;&#xA;  &lt;OraWinAuthentication&gt;False&lt;/OraWinAuthentication&gt;&#xD;&#xA;  &lt;OraEnableDetailedTracing&gt;False&lt;/OraEnableDetailedTracing&gt;&#xD;&#xA;&lt;/DTS:ConnectionManager&gt;" />
    </Connections>
    <Packages>
        <Package Name="Package2" Language="None" ConstraintMode="LinearOnCompletion" ProtectionLevel="EncryptSensitiveWithPassword">
            <Tasks>
                <Dataflow Name="Data Flow Task">
                    <Transformations>
                       <OracleSource Name="oracleSourceStmt" Connection="OracleSource" >
                           <DirectInput>"SELECT * FROM SomeTable"</DirectInput>
                       </OracleSource>
                        <OleDbDestination Name="OLE DB Destination" ConnectionName="SQL.DWH_NEW">
                            <ExternalTableOutput Table="[dbo].[SomeTable]" />
                        </OleDbDestination>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

If I check for errors I get "could not resolve reference to .... in property 'Connection'. '' is invalid. Provide valid scoped name.

I did also try to use "OracelConnection" instead of the CustomSSISConnection but then I did receive another error message: Oracle Client Access not detected. Install the 32-bit or 64-bit version of oracle client access that matches your version of Biml/BimlStudio.

I did read "Using the Attunity Oracle Connector with Biml" by Scott Currie but this didn't help either since the built-in support for the attunity connector should be available by now.

• could someone provide a hint\shed some light what my mistake is?
• Should I (still) use the CustomSSISConnection or would be the OracelConnection the correct choice? If I should use the oracleConnection any hints what is missing?
• did anybody created an oracle to sql ssis-package in biml (successfully ;)?

so many questions - hope someon could help! :-)

Regards, Gregor

Answer 1

The format that I found worked for Oracle connections whilst using just BimlExpress was the below which, whilst littered with embedded variable names from my personally developed Biml framework, should get you going in the right direction.

Oracle Connection Node

The Expressions node is how the connection is set up to take values from the Project Parameters, which I have always found to be useful to have as an option to save redeploying just to change a small config value:

    <CustomSsisConnection Name="<#=ConnectionName#>"
                          CreationName="MSORA"
                          ObjectData="&lt;OracleXMLPackage&gt;&lt;OraConnectionString&gt;<#=conn#>&lt;/OraConnectionString&gt;&lt;OraRetain&gt;False&lt;/OraRetain&gt;&lt;OraInitialCatalog&gt;&lt;/OraInitialCatalog&gt;&lt;OraServerName&gt;<#=ServerName#>&lt;/OraServerName&gt;&lt;OraUserName&gt;<#=Username#>&lt;/OraUserName&gt;&lt;OraOracleHome&gt;<#=Config.GetConfigValue(@"OracleHome")#>&lt;/OraOracleHome&gt;&lt;OraOracleHome64&gt;<#=Config.GetConfigValue(@"OracleHome")#>&lt;/OraOracleHome64&gt;&lt;OraWinAuthentication&gt;False&lt;/OraWinAuthentication&gt;&lt;OraEnableDetailedTracing&gt;False&lt;/OraEnableDetailedTracing&gt;&lt;OraPassword Sensitive=&quot;1&quot; Encrypted=&quot;0&quot;&gt;<#=Password#>&lt;/OraPassword&gt;&lt;/OracleXMLPackage&gt;"
                          CreateInProject="true"
                          >
        <Expressions>
            <Expression ExternalProperty="ConnectionString"
                        >@[$Project::<#=ConnectionName#>_Conn]</Expression>
            <Expression ExternalProperty="OracleHome"
                        >@[$Project::OracleHome]</Expression>
            <Expression ExternalProperty="OracleHome64"
                        >@[$Project::OracleHome64]</Expression>
        </Expressions>
    </CustomSsisConnection>

Oracle Data Source Node

Note that this is a Custom Component, which will output an Attunity Oracle Source. So you have to have this available on your machine. Again there are some relics from the framework in there, but they should help you understand what is going on if anything. The big hassle if you aren't doing this programatically like I am, is adding all the columns and their data types:

    <CustomComponent Name="<#=SourceTechType#>Src - Retrieve Rows - <#=FromSchema#> <#=FromTable#>"
                     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.">1</CustomProperty>
            <CustomProperty Name="TableName" DataType="String" SupportsExpression="true" Description="The name of the table to be fetched."></CustomProperty>
            <CustomProperty Name="SqlCommand" DataType="String" SupportsExpression="true" Description="The SQL command to be executed.">
select cols
from tables
where conditions = met
            </CustomProperty>
        </CustomProperties>
        <Connections>
            <Connection Name="MSOraConnection"
                        ConnectionName="<#=SourceConnectionName#>"
                        />
        </Connections>
        <OutputPaths>
            <OutputPath Name="Output"
                        ErrorRowDisposition="FailComponent"
                        TruncationRowDisposition="FailComponent"
                        IsErrorOutput="false"
                        >
                <OutputColumns>
                <#  foreach (var column in table.Columns) { #>
                        <OutputColumn Name="<#=column.Name#>"
                                      ExternalMetadataColumnName="<#=column.Name#>"
                                      DataType="<# if(column.GetTag("ConvertFrom").Length > 0) { #><#=column.GetTag("ConvertFrom")#><# } else { #><#=column.DataType#><# } #>"
                                      <# if(column.DataType.ToString() == "String"){ #>CodePage="1252"<# } #>
                                      Length="<#=column.Length#>"
                                      Precision="<#=column.Precision#>"
                                      Scale="<#=column.Scale#>"
                                      />
                <#  } #>
                </OutputColumns>
                
                <ExternalColumns>
                <#  foreach (var column in table.Columns) { #>
                    <ExternalColumn Name="<#=column.Name#>"
                                    DataType="<# if(column.GetTag("ConvertFrom").Length > 0) { #><#=column.GetTag("ConvertFrom")#><# } else { #><#=column.DataType#><# } #>"
                                    <# if(column.DataType.ToString() == "String"){ #>CodePage="1252"<# } #>
                                    Length="<#=column.Length#>"
                                    Precision="<#=column.Precision#>"
                                    Scale="<#=column.Scale#>"
                                    />
                <#  } #>
                </ExternalColumns>
            </OutputPath>
            
            <OutputPath Name="Error"
                        IsErrorOutput="true">
                <OutputColumns>
                <#  foreach (var column in table.Columns) { #>
                    <OutputColumn Name="<#=column.Name#>"
                                  DataType="<# if(column.GetTag("ConvertFrom").Length > 0) { #><#=column.GetTag("ConvertFrom")#><# } else { #><#=column.DataType#><# } #>"
                                  <# if(column.DataType.ToString() == "String"){ #>CodePage="1252"<# } #>
                                  Length="<#=column.Length#>"
                                  Precision="<#=column.Precision#>"
                                  Scale="<#=column.Scale#>"
                                  />
                <#  } #>
                </OutputColumns>
            </OutputPath>
        </OutputPaths>
    </CustomComponent>