Amazon Redshift connectivity issue for data flows

[meyerovb]

I try to create a data flow, add table, choose amazon redshift as data source, enter server name and database exactly as it was in the on-premise gateway, the gateway instantly populates into the drop down and the username [ENCRYPTED] and password ***** populate as well.  All good so far.  Press next, wait a few seconds, and consistently get this:

An exception occurred: 'Role' is not a valid Amazon Redshift option. Valid options are: 'BatchSize'

Tried manually changing the credentials to the same thing used by the gateway (which it probably is anyways), no joy.  Turned off Use Encrypted Connection, no joy.  Auto-refreshing reports using the gateway works just fine, but can't do this for some reason.

[GilbertQ]

Hi @meyerovb 

What happens if you try and use an ODBC connection instead of the Amazon Redshift option and see if that works?

You can use the blog post here to show how to configure the ODBC connection: Using Azure Analysis Services to connect via an ODBC Source (Redshift) - Reporting/Analytics Made ea...

[meyerovb]

I'm using the website, not desktop

Tried odbc connection string, selected the on-premise gateway in the drop down, tried anonymous or same sql user login as on-prem gateway

Driver=Amazon Redshift (x64); Server=...; Database=...; Port=5439

Immediately Returns Unexpected Error

Tried x86 instead of x64, got this after like 30 seconds:

An exception occurred: The 'Driver' property with value 'Amazon Redshift (x86)' doesn't correspond to an installed ODBC driver.

[v-deddai1-msft]

Hi @meyerovb ,

Did you install both 32bit and 64bit odbc driver for  Redshift on the server that hold the gateway? It could be a permission issue in Amazon Redshift based on your error message in your original post. You may need to check forums that specialize in RedShift.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai