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

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

Postby daveharrison.it » Thu 12 Jan 2017 12:31

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>
daveharrison.it
 
Posts: 1
Joined: Thu 12 Jan 2017 12:15

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

Postby Shalex » Fri 13 Jan 2017 18:41

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.
Shalex
Devart Team
 
Posts: 7793
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL