1

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?

Connor Low
  • 5,900
  • 3
  • 31
  • 52
  • 1
    How large are the single files that SSIS produces? It's just a recommendation to use smaller files for performance reasons but if you're doing a once-off load of big tables I'd suggest simply trying it with large files and see how you go? – Simon D Jun 29 '21 at 13:18
  • Hi Simon, thanks for the reply. Its a Health database with 1000+ objects and some tables being very large since they have records since 2015 in them. So some of the csv's are getting 60gb+ in size. The trouble with this database is, that it's not easy to filter for increments. So with some bad luck we have to full load almost every day. –  Jun 30 '21 at 09:25
  • See https://stackoverflow.com/a/68718176/132438 to split files easily into smaller ones. – Felipe Hoffa Aug 09 '21 at 20:24

1 Answers1

1

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.

Eric Hauenstein
  • 2,557
  • 6
  • 31
  • 41
  • Hi Eric, thanks for the reply. I am afraid that this setting will get stuck on the fact that all zip files need to be uploaded first in order to copy the records in the tables. Its not possible to unzip a file, when the set of zip files is not complete. –  Jun 30 '21 at 09:27