BIML Package using AdoNetConnection - Keyword not supported: 'host'

[daveharrison.it]

I am trying to use the Devart.Data.PostgreSql.PgSqlConnection provider in an ADONetConnection.

This works fine directly in SSIS, but I am now trying to generate the SSIS package using BIML. Every time I try to generate a package (using BIMLExpress or BIDSHelper in Visual Studio) from BIML which uses a data flow using such a connection I get tan error like...

Node ADO NET Source: COuld not execute Query on Connection MyConnection:

"My Source Query"

Keyword not supported: 'host'


It's like it cannot make a connection using my connection string: "User Id=MyUserName;Password=MyPassword;Host=MyServerName;Database=MyDatabaseName;Persist Security Info=False;Unicode=True;Default FetchAll=true;"

Can anyone help?

Example of the sort of BIML code i'm using:

AdoNetConnection at top level:

Code: Select all

<AdoNetConnection Name="MyConnection"
					  Provider="Devart.Data.PostgreSql.PgSqlConnection, Devart.Data.PostgreSql, Version=7.4.521.0, Culture=neutral, PublicKeyToken=00MyKey00"
					  ConnectionString="User Id=MyUserName;Password=MyPassword;Host=MyServerName;Database=MyDatabaseName;Persist Security Info=False;Unicode=True;Default FetchAll=true;"
					  DelayValidation="true">
</AdoNetConnection>

Connection brought in at package level:

Code: Select all

<Connections>
        <Connection ConnectionName="MyConnection"/>
 </Connections>

Dataflow using connection:

Code: Select all

<Dataflow Name="Data Flow Task">
          <Transformations>
            <AdoNetSource Name="ADO NET Source" ConnectionName="MyConnection">
              <DirectInput>
                SELECT columnA, ColumnB FROM MyTable
              </DirectInput>
            </AdoNetSource>
            <DataReaderDestination Name="DataReaderDest">
              <InputPath OutputPathName="ADO NET Source.Output" />
            </DataReaderDestination>
          </Transformations>
        </Dataflow>

[Shalex]

Try using PgSqlConnectionManager, for example:

Code: Select all

    <DTS:ConnectionManager
      DTS:refId="Package.ConnectionManagers[Devart PostgreSql Connection Manager]"
      DTS:CreationName="DevartPostgreSql"
      DTS:DTSID="{25CFC5D8-04C6-4185-81D3-A92827EC054C}"
      DTS:ObjectName="Devart PostgreSql Connection Manager">
      <DTS:ObjectData>
        <PostgreSqlConnectionManager>
          <ConnectionString>User Id=postgres;Host=dbx64;Database=postgres;Persist Security Info=True</ConnectionString>
          <Password
            Sensitive="1"
            Encrypted="1">AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAFHIN2D2Hg06W3FfrlrAoHwAAAAAIAAAARABUAFMAAAADZgAAwAAAABAAAAA5JDcbNDRGjaBEvdX1T1c0AAAAAASAAACgAAAAEAAAAN/npVCRdPwR9XBYzismsTwYAAAAdmZSpcaO/pjl3q+m7qcvBzNJSIVgxpDsFAAAAN6efjFlDA66EDUtVBRdcBXjEUKV</Password>
        </PostgreSqlConnectionManager>
      </DTS:ObjectData>
    </DTS:ConnectionManager>

In Integration Services Project, right click surface in the Connection Managers window > New Connection > DevartPostgreSql to create a Devart PostgreSql Connection Manager.

If this doesn't help, send us a test package and specify the exact steps we should follow for reproducing.