The alternate I am trying is to use " (double quotes) for Character Fields while exporting data, and when importing the data I will use Replace(column_name, '\~', '~'), assuming escape character used is \ and delimiter used is ~. Let me know if you see any issues in this approach please.
Sorry for the confusion, there was a regression during deployment in the Large Object support that caused a rollback of the functionality. We are actively working on getting a fix.
For loading data to Azure SQL DWH, one of the alternate I am thinking is, creating Azure SQL DWH Table with Heap which is allowing VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) data types. Create External Table with multiple columns for columns having more than allowed length. In the Select statement while exporting data from Source, use Substring to split the column data, load the Text file to External Table with same number of Columns, finally while loading to Azure SQL DWH Table, merge the content of multiple column into one column. Let me know your views or other alternates please.
CREATE TABLE play.table1 (c1 varchar(max), c2 nvarchar(max), c3 varbinary(max))
--this is working in Azure SQL DWH
--I tried loading 1 million character length data with mix of alphanumeric and few special characters, it is working and storing the data. I am using Table which has 50k character length, I am able to regular operations.
--Yes, only Heap supporting (MAX), for "CLUSTERED COLUMNSTORE INDEX" Table types (MAX) is not working.
Planned for SQL DB- no time frame to share yet. Available in SQL Managed Instance
I agree, this is extra difficulty. More than changing the code, validation and testing takes time.
For now, I am thinking to create below function and do find & replace Getdate() with udo.Getdate(getdate()) in the code. Let me know any issues or challenges you see OR better alternate available. Note: udo is a schema name I used, it can be anything like dbo or your own schema name. I am adding 4 hours, you need to change this accordingly to your time zone.
CREATE FUNCTION udo.GETDATE (@d datetime) RETURNS DATETIME
RETURN DATEADD(HOUR, 4, @d);
Thanks for your suggestion. We are looking into this scenario for a future release. 10703657