I read from Using the Attunity Oracle Connector with Biml a way to define an element for Attune Oracle connector by using CustomSsisConnection element . It's different from the actual connection definition in place in the project connection I have to work with. The current Attunity connector definition is defined by a DTS:ConnectionManager element, in which I change values to @@@@@@ for security reasons:
<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"
DTS:ObjectName="Oracle_TVIA"
DTS:DTSID="{608D9C98-CC33-4EC7-BF35-DDDB7BDBDBC7}"
DTS:CreationName="MSORA">
<DTS:PropertyExpression
DTS:Name="ConnectionString">@[$Project::Oracle_Connector]
</DTS:PropertyExpression>
<DTS:ObjectData>
<OracleXMLPackage>
<OraConnectionString>SERVER=@@@@@@;USERNAME=
@@@@@@;ORACLEHOME=;ORACLEHOME64=;WINAUTH=0</OraConnectionString>
<OraRetain>False</OraRetain>
<OraInitialCatalog></OraInitialCatalog>
<OraServerName>@@@@@@</OraServerName
<OraUserName>@@@@@@</OraUserName>
<OraOracleHome></OraOracleHome>
<OraOracleHome64></OraOracleHome64>
<OraWinAuthentication>False</OraWinAuthentication>
<OraEnableDetailedTracing>False</OraEnableDetailedTracing>
<OraPassword
Sensitive="1"
p4:Salt="@@@@@@"
p4:IV="@@@@@@"
xmlns:p4="www.microsoft.com/SqlServer/SSIS">@@@@@@@@@@@@</OraPassword>
</OracleXMLPackage>
</DTS:ObjectData>
</DTS:ConnectionManager>
Attunity's connection mentioned in post is:
<CustomSsisConnection Name="Oracle Connector 1" CreationName="MSORA" ObjectData="<OracleXMLPackage>
 <OraConnectionString>SERVER=XXXX;USERNAME=username;ORACLEHOME=;ORACLEHOME64=;WINAUTH=0</OraConnectionString>
 <OraRetain>False</OraRetain>
 <OraInitialCatalog></OraInitialCatalog>
 <OraServerName>ec2-107-20-117-195.compute-1.amazonaws.com</OraServerName>
 <OraUserName>username</OraUserName>
 <OraOracleHome></OraOracleHome>
 <OraOracleHome64></OraOracleHome64>
 <OraWinAuthentication>False</OraWinAuthentication>
 <OraEnableDetailedTracing>False</OraEnableDetailedTracing>
 <OraPassword Sensitive="1" Encrypted="1">AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAm1Wd3JvvU0+cUJlVQyqnaAAAAAAIAAAARABUAFMAAAADZgAAwAAAABAAAAA6xkf97NyWpMZVJk8BfUfOAAAAAASAAACgAAAAEAAAABcdn9bN864uL88pKlNCAlwYAAAAcbtMszBiQhegvc8dBJXJRRNt9xmQRe2pFAAAANMQTe7MVbECC97BJJ8QQO0KMUfc</OraPassword>
</OracleXMLPackage>" />
When I attemp to add this code within Biml schema, I got an compilation error message telling DTS:ConnectionManager is not a valid child of Biml. Would you please advise how would I convert the DTS:ConnectionManager's definition to CustomSsisConnection's.
Thanks
The code generated by XSLT is :
<?xml version="1.0" encoding="utf-8"?>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<Connection Name="Oracle_TVIA" ConnectionString="SERVER=@@@@@@;USERNAME=@@@@@@;ORACLEHOME=;ORACLEHOME64=;WINAUTH=0" />
<Connection Name="Target_SQLBIPRO" ConnectionString="Data Source=SQLBIDES;Initial Catalog=TVIA;Provider=SQLNCLI11.1;Integrated Security=SSPI;" />
</Connections>
<Packages>
<Package ConstraintMode="Linear" Name="staging_AMS_CD_PAYMENT_CATEGORY">
<Tasks>
<ExecuteSQL Name="Truncate_AMS_CD_PAYMENT_CATEGORY" ConnectionName="Target_SQLBIPRO">
<DirectInput>"exec dbo.delete_AMS_CD_PAYMENT_CATEGORY ?,?"</DirectInput>
</ExecuteSQL>
<Dataflow Name="Load_AMS_CD_PAYMENT_CATEGORY">
<PrecedenceConstraints>
<Inputs>
<Input OutputPathName="Truncate_AMS_CD_PAYMENT_CATEGORY.Output" />
</Inputs>
</PrecedenceConstraints>
<Transformations>
<OleDbSource Name="Source_AMS_CD_PAYMENT_CATEGORY" ConnectionName="Oracle_TVIA">
<ExternalTableInput Table="AMS_CD_PAYMENT_CATEGORY" />
</OleDbSource>
<OleDbDestination Name="Target_AMS_CD_PAYMENT_CATEGORY" ConnectionName="Target_SQLBIPRO">
<InputPath OutputPathName="Source_AMS_CD_PAYMENT_CATEGORY.Output" />
<ExternalTableOutput Table="Staging_BOS_VR_OWNER_AMS_CD_PAYMENT_CATEGORY" />
</OleDbDestination>
</Transformations>
</Dataflow>
</Tasks>
</Package>
</Package>
</Packages>
</Biml>