BULK INSERT: "Referenced external data source "(null)" not found" when pointing to local file

[migajek]

I'm running an MS SQL instance in Docker, mounted a volume with CSV files inside (see docker-compose)

services:
    db:
        image: mcr.microsoft.com/mssql/server
        environment:
            SA_PASSWORD: "<pass>"
            ACCEPT_EULA: "Y"
        ports:
            - 1433:1433
        volumes:
            - ../test-data:/home/test-data

the volume is mounted correctly:

$ docker-compose exec db sh -c 'ls /home/test-data'
Advisors.csv

now when I issue the following query:

BULK INSERT Advisors                 
FROM 'C:\home\test-data\Advisors.csv'            
WITH                                       
(FORMAT = 'CSV')

I get the error: Referenced external data source "(null)" not found

Please note I've tried 'home/test-data/Advisors.csv' at first, but then found the StackOverflow answer pointing out I should convert it to Windows-styled path with "C:" as a root placeholder https://stackoverflow.com/a/41865509

[forkazmoden]

Hi,

Another way to bulk copy data from a data file to an instance of SQL Server in a docker container is using the bcp command-line utility. Please refer to Bulk copy data with bcp to SQL Server on Linux.

root@779cb6e36a97:/# /opt/mssql-tools/bin/bcp TestEmployees in ~/test_data.txt -S localhost -U sa -P "123456" -d test -c -t  ','

Starting copy...

3 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 70     Average : (42.9 rows per sec.)

[migajek]

@forkazmoden thanks. I didn't mention my main goal is to use docker SQL Server for local dev environment and unit testing of the process involving bulk insert query, which - on production - will be run against actual SQL Server.

[ErikJansenIRefact]

Hi,

Is there any progress in fixing the issue with the BULK INSERT regarding the error? Are is there any explanation how to get it working or why it doesn't work?

The alternative with BCP is not an option for us.

[mohameddahrouj]

@migajek, we are attempting to accomplish the same thing using bulk insert statements. We also use SQL Server on Docker. BCP is not viable either. Did anyone find a solution?

[EfrenGG]

Hello everyone,

I had the same trouble using Docker desktop for macOS. It seems the problem only occurs when you try to run the bulk insert statement with a file residing inside a directory configured as a bind mount.

You can move your file to a folder outside the bind mount volume and it should work as expected.

[Mercy811]

@EfrenGG Thanks! That works for me!

[andritcharthur]

I ran into the same issue, and my use case included an automatic process of bulk inserting files that were written to a particular folder. So I ended up taking @EfrenGG's suggestion and mounted a different folder but used https://axkibe.github.io/lsyncd/ to automatically sync to the actual folder my program needed

[EfrenGG]

@andritcharthur thanks for sharing!

I was about to develop a custom solution for sync, but it seems that's what I need too.

[PranavGadeQC]

This sounds weird, but this is what worked for me: you need to create the folder you are going to mount using mkdir on the host. So, you can mkdir /home/user/mount_folder, which can be mounted using -v /home/user/mount_folder/:/usr/share/tables/.

I cannot figure out why this happens though, and the only difference I observed is that stat /home/user/mount_folder has a value in the birth field, whereas this does not appear if you don't mkdir.

For some reason, mkdir /tmp/mount_folder does not have the birth field, and(therefore?) does not work either.