CODEWITHSUNDEEP
sqlsql-serverssissnowflake-cloud-data-platformbcp
user16341900
user16341900

Reputation:

Creating multiple files for uploading to Snowflake

Currently, my company uses SSIS and BCP to export data from SQL Server to CSV files. However, we are only able to create a single file per SQL table (due to the limitations of BCP). Most of these files are quite large; if I am correct, they are too large to get the best performance when loading them into Snowflake. On their website, they state that we should be working with multiple gzip files to offer the best performance.

I am wondering how other people made this work? Splitting up the CSV to multiple files and zipping them? Any good tools that can do this during export from SSIS?

Upvotes: 1

Views: 398

Answers (1)

Eric Hauenstein
Eric Hauenstein

Reputation: 2565

I'd keep the current process that exports the large .csv files using SSIS, then run 7zip via command line to create a split gzip set for each text file, either within the SSIS package or via Powershell.

The -v switch is used to specify the volume size.

https://sevenzip.osdn.jp/chm/cmdline/switches/volume.htm

You may be able to start importing/uploading the completed chunks before the later ones are finished to pick up some additional time savings, but I've not tested that.

Upvotes: 1

Related Questions