CODEWITHSUNDEEP
sqlssisodbcamazon-athenasimba
Muds
Muds

Reputation: 4116

Connecting to Athena using Simba ODBC Driver - Long string converts to Text issue

We have recently moved to AWS Data Lake using Athena. We connect to Athena using a LinkedServer in SQL which works with ODBC using Simba Driver.

Issue we are facing is that whenever there is any string in Athena which is longer than 4000 characters, driver converts it to Text object which when we read from SSIS fails to convert to a standard datatype like nvarchar.

One simple option is to start using Text as DataType but Text is deprecated and mustn't be used.

Has anyone faced a similar issue. Happy to provide any info as needed.

I see the following message -

An OLE DB record is available. Description: "OLE DB provider "MSDASQL" for linked server "AWS-Test-DataLake-EU-West-1" returned message "Requested conversion is not supported.".".

Upvotes: 3

Views: 1425

Answers (1)

MetaData
MetaData

Reputation: 116

the str and wstr datatype in ssis has maximum length of 4000. Text and ntext will be depreacted but i think it is only in sql server and not in ssis . so you can use ntext in your ssis package as datatype and use nvarchar(max) in sql destination table.

Datatype screenshot from microsoft

Upvotes: 2

Related Questions