Topics

SSIS - Dynamically load csv files into SQL table, then zip with 7-zip and move to archive location

Case

I want to load csv files from a parameterized location into a SQL table, after processing I want to zip and compress the csv files and move them to a parameterized archive location.


Solution

With SSIS you can loop through files in a specific directory, process each individual file, move them to a different directory and compress the files. In this solution I will demonstrate the processing of flat files with the foreach loop container, data flow task, file system task and execute process task.
The compressing is handled by 7-zip, a free open source file archiver.

Prerequisite
  • 7-zip is installed on your server or local machine, click here to download.
  • 7-zip is added to your PATH enviroment variable
  • You have a sql server database and are able to write data to
Steps

  1. Create a SSIS package
  2. Create an OLE DB target with your SQL Server
  3. Create project parameters Sourcepath and Archivepath containing the path to your csv files and the path to your archive destination. E.g.:
    • Sourcepath: "C:\temp\"
    • Archivepath: "C:\temp\archive\"
  4. Create user parameters CSVFilename and CSVFilepath, these will be used by the foreach loop container to dynamically set the path to each file when the foreach loop container loops through the directory.
  5. Create a sequence container in the control flow tab
  6. Add a "Execute SQL task" to drop and create your target table
  7. Add a "Foreach loop containter" to loop though the flatfiles in "C:\temp\"
  8. Add a "Data flow task" to write the data of each flat file to your SQL destination table.
  9. Add a "File System task" to move the file to the "C:\temp\archive\" location
  10. Add a "Execute Process task" to call 7-zip, compress the files to an archive and remove the processed files.
 The result should look like this:




The foreach loop container can be set as follows:

"Setting the collection part"


"Map the user variable CSVFilename to dynamically process files"

 


The "Drop and create target table" SQL task can contain SQL code like:

DROP TABLE IF EXISTS [dbo].[Target]

CREATE TABLE [dbo].[Target](
    [id] [int] NULL
) ON [PRIMARY]

The "DFT Stage data" dataflow task can be set as follows:



The "Move file to archive" File System Task can be set as follows:



The "Zip files to archive file and remove" Execute Process task can be set as follows:





Be sure you make the connection string of your Flat File Connection Manager dynamic by setting the "ConnectionString" property:




When you run your SSIS package you will see that your flatfiles in "C:\temp\" are processed and loaded into your SQL Table "dbo.Target".






And the files are compressed and moved to "C:\temp\archive"




I hope you enjoyed reading this article and you can use this example in your daily work.
Have a nice day!