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

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

Postby » 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;"

Connection brought in at package level:
Code: Select all
        <Connection ConnectionName="MyConnection"/>

Dataflow using connection:
Code: Select all
<Dataflow Name="Data Flow Task">
            <AdoNetSource Name="ADO NET Source" ConnectionName="MyConnection">
                SELECT columnA, ColumnB FROM MyTable
            <DataReaderDestination Name="DataReaderDest">
              <InputPath OutputPathName="ADO NET Source.Output" />
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:refId="Package.ConnectionManagers[Devart PostgreSql Connection Manager]"
      DTS:ObjectName="Devart PostgreSql Connection Manager">
          <ConnectionString>User Id=postgres;Host=dbx64;Database=postgres;Persist Security Info=True</ConnectionString>

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

Return to dotConnect for PostgreSQL