How to create zip file which contains single text file with a fixed threshold in SSIS.

What is SSIS?

SQL Server Integration Service (SSIS) is a component of the Microsoft SQL Server database software that can be used to execute a wide range of data migration tasks. The SSIS ETL tool Extracts data from different sources and transforms that data according to user requirements. And then loads data into various destinations. SSIS is a fast & flexible data warehousing tool used for data extraction, loading and transformation like cleaning, aggregating, merging data, etc.

Following are components of SSIS that we need to create zip file-

Execute SQL Task: As its name suggests, it will execute a SQL statement against a relational database.

Data Flow Task: This task can read data from one or more sources. Transform the data when it is in the memory and write it out against one or more destinations.

File System Task: It performs manipulations in the file system. Like moving, renaming, deleting files, and creating directories

Script Task: This is a blank task. You can write NET code which performs any task; you want to perform.

Containers-

Sequence Container: Allows you to organize subsidiary tasks by grouping them and allows you to you apply transactions or assign logging to the container.

For loop container: Provides the same functionality as the sequence Container except that it also lets you run the tasks multiple times. However, it is based on an evaluation condition, like a looping from 1 to 100.

For each Loop Container: It also allows looping. But the difference that instead of using a condition expression, loop s done over a set of objects, likes files in a folder.

Threshold: A “threshold” is not a maximum but a point at which something changes. For example, in this package, “There is a text file which allows 20 rows, but if your count reaches the THRESHOLD then it will create the new zip file.”

Let’s see how we can create zip file which contains single text file with a fixed threshold in SSIS.

Following are the variables which we need to implement the same.

Step 1- Drag and drop the Sequence Container and inside the sequence container drag the for each loop container and configure it as below-

Step 2- Now, Add the File system task inside the for each loop container to delete the existing text files.

Step 3- Next, drag the Execute SQL Task and configure the SQL statement for each record count by executing the procedure and configure the parameter mapping and result set.

Step 4- Now, drag the For-loop container and configure it as below.

Step 5- Add the data flow task the data will flow from OLE DB source, record count and Flat File destination inside the for-loop container.

Step 6- Finally, add script task to convert text file into zip file and to move that zip file with fixed threshold in archive folder in the for loop container and configure it as below.

We have 40 records in our source, and we have set the threshold as 20 and it has separated 20 records in single text file and 20 in another file.

Here we have successfully archived the zip file which contains single text file with a fixed threshold in SSIS.

For more such blogs click here

Thanks for reading!! Hope you enjoyed reading this blog.