Search code examples
sql-serverazureazure-storageazure-blob-storagebulkinsert

Can I Bulk Load Compressed Files into SQL Server?

Is there a way to bulk load data from Azure Blob to SQL Server? I'm accessing the files through Azure Storage Explorer. I'm pretty sure it can be done, but I'm getting an error that says the 'file could not be opened'. Here is my code.

DECLARE @cmd varchar(1000)
SET @cmd = 'BULK INSERT [dbo].[dest_table]
FROM ''alldata/2019/06/29/BB/dds_id.out.20190629.gz''
WITH (      FIELDTERMINATOR = ''\n'',
            FIRSTROW = 46,
            ROWTERMINATOR = '''+CHAR(10)+''')';
PRINT @cmd
EXEC(@cmd)

The file ends in .gz, so it's compressed. Is that the problem here? More importantly, is there a workaround for this? All I have is SQL Server; no access to SSIS.

Solution

  • Not exactly what you're asking for but the powershell library dba-tools is able to bulk copy gzip compressed CSV files to SQL Server.

    Filename just needs to end in .csv.gz

    https://docs.dbatools.io/Import-DbaCsv.html