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.
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.
- 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
- Create a SSIS package
- Create an OLE DB target with your SQL Server
- 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\"
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!