3

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.".".

Muds
  • 4,006
  • 5
  • 31
  • 53
  • What is the exact error message? – Amira Bedhiafi Mar 09 '21 at 16:35
  • hey, added it in the question. thansk – Muds Mar 10 '21 at 09:53
  • Have you tried adjusting the "String Column Length" under "Advance Options" of the ODBC DSN Setup? – vhoang Mar 15 '21 at 22:20
  • so if I change the length to what I would need we get the above message, if I limit it to 4000 I get returned data that does not match expected data length for column '[MSDASQL].Tes1'. The (maximum) expected data length is 4000, while the returned data length is 4233 – Muds Mar 16 '21 at 14:38
  • changing above to 8000 then fails for next rows with 10k plus length – Muds Mar 16 '21 at 14:47
  • 1
    If the driver has a size max for string that is too small and it lets in Text, just let it cast it to Text or do so explicitly in the select to get past the driver. As far as ssis is concerned, varchar(max) = dt_text, nvarchar(max) = dt_ntext. You should be able to leave both end in the non-text form and mangle it in between. – vhoang Mar 16 '21 at 17:05

1 Answers1

2

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

MetaData
  • 116
  • 11