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="<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts">
 <OraConnectionString>SERVER=SomeOracleServer;USERNAME=myUser;WINAUTH=0;data source=SomeOracleServer;user id=myUser;</OraConnectionString>
 <OraPassword Sensitive="1"></OraPassword>
 <OraRetain>False</OraRetain>
 <OraInitialCatalog></OraInitialCatalog>
 <OraServerName>SomeOracleServer</OraServerName>
 <OraUserName>ext-bi-pg</OraUserName>
 <OraOracleHome></OraOracleHome>
 <OraOracleHome64></OraOracleHome64>
 <OraWinAuthentication>False</OraWinAuthentication>
 <OraEnableDetailedTracing>False</OraEnableDetailedTracing>
</DTS:ConnectionManager>" />
</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
OracleConnection
approach, do you have the client access tool installed? I don't do Oracle beyond knowing to blame TNSNAMES for everything so my Biml knowledge will likely not be of much help – billinkc