Referencing ABS location for stored procedure Using Azure Data Factory

Question

My ADF pipeline processes through many imput files and lands them on an ABS container. A 3rd party stored proc has two params: filename and datasourcelocation and does a bulk insert into an Azure SQL DB.

For the Data Source Location I pass: landing/Vendor For the Data File /Company_05_17_22_05_54.csv

The full ABS location is for a single file is: https://...use2dev01.blob.core.windows.net/landing/Vendor/Company_05_17_22_05_54.csv

The error message says

Execution fail against sql server. Sql error number: 12703. Error Message: Referenced external data source "landing/vendor" not found.

How should I be passing the ABS location to the proc?

Answer 1

The error message says

Execution fail against sql server. Sql error number: 12703. Error Message: Referenced external data source "landing/vendor" not found.

For this error you can create a new data source (eg myazureblobstorage1 and provided that name in the second BULK command)

• Try to Check the TLS version of the storage account. sometimes TLS settings may cause this error
• Try to create the external data source ,wait for mins before running the BULK Insert
• Check the CSV file is formatted properly, it may cause other issues
• Take a note of additional parameters in the BULK Insert command
• Make sure Table column names must match with that of CSV file

If you want to bulk insert from Azure blob, please refer to following script of so thread

Please check if the data source exists , You can verify the name of the data source by querying sys.external_data_sources and referring this MsDoc

For more in detail, please refer below links:

Bulk insert from Azure blob storage to Azure SQL database

SQL Server BULK INSERT does not work with Azure Blob Storage emulator

https://docs.microsoft.com/en-us/azure/data-factory/quickstart-create-data-factory-portal