BULK IMPORT a zip file in T-SQL

Question

I've got some data files that are stored compressed on our company's server with a .Z extension (UNIX compress utility used to zip them down).

Can SQL Server's BULK IMPORT operation read these files in that format? Or must I uncompress them before getting at the data?

Answer 1

The BULK IMPORT would not natively be able to do this however if you are on SQL2005 or greater you can use SSIS. The first step would be to perform an Exectute Process Task and use a zip utility to unzip the file. The second step is to use the SSIS Bulk Insert task to push the data into SQL Server.

EDIT: use the compress from unixutils rather than cygwin to uncompress the files as it understands native windows filenames. This means that you don't have to maintain /cygdrive paths as well as native paths.