Loading data from DB into CSV with special characters and then load it into Azure using bulk load

Rathesh — Fri, 15 Nov 2024 23:42:23 GMT

Hello,

I am trying to load a load a table from on-prem SQL server to Azure SQL Server(single instance) DB using bulk load. Source table is having 20 millions records. My job flow is tDBInput --> tMap --> tFileOutputDelimited --> tAzureStoragePut --> tDBRow. I have used bulk insert script inside the tDBRRow component.

BULK INSERT dim.table1

FROM 'bulkexecfiles/table1.txt'

WITH ( DATA_SOURCE = 'BlobStorageAccount',

FIRSTROW = 2,

FIELDTERMINATOR = ',',

ROWTERMINATOR = '0x0a',

KEEPIDENTITY

);

I have the below issues

Source data is having lot of special characters(including new lines), so while loading into txt file I am not able to use any of the separators.
If I use text enclosure while writing into file, Bulk insert is failing.

Can anyone suggest how to process this data. Thanks in advance

topic Loading data from DB into CSV with special characters and then load it into Azure using bulk load in Talend Studio

Loading data from DB into CSV with special characters and then load it into Azure using bulk load