Welcome To TechBrothersIT: How to Load all text/csv files to single Text/CSV File from a folder in SSIS Package

Scenario : Download Script

You are working as SQL Server Integration Services(SSIS) developer. You receive tons of text or csv files in your source folder. You need to create an SSIS Package that should read all the files form Source folder and create single text or csv file with date-time and load data from all of source files.

How to load multiple Text or CSV files to single Text/CSV file in SSIS Package

Notice that all the files have same number of columns. From these two files, I need to create consolidated single file.

Solution:

This requirement can be done by using builtin tasks and transformation but I am going to perform this by using Script Task.

Step 1: Create new SSIS Package with Variables to Make it Dynamic

Open SSDT ( Sql Server Data Tools) and create new SSIS Package. After that create below variables

DestinationFolder: Folder path where you would like to create your destination consolidate file

FileDelimiter : Provide the delimiter such as comma (,), Pipe( | ) Whatever your files are using.

FileExtension : Provide the extension of files that you would like to read.

SourceFolder : Source folder path where text files exists

LogFolder: Provide the path of log folder where error file will be created in case of error happen in Script Task

FileName : Provide the file name for your destination file. Date time part will be added to it.

DestinationFileExtension: Provide the extension for your destination file.

Create variables to load all the flat files into single flat file in SSIS Package by using Script Task-SSIS Tutorial

Step 2: Add Script Task to SSIS Package and Map Variables

Bring Script Task to Control Flow Pane and open it by double clicking. Add the SSIS Package variables to it so we can use inside.

Add variables to Script Task to generate single flat file from multiple text or csv files in SSIS Package

Step 3: Add Script to Script task Editor in SSIS Package to Load Each CSV File from a folder to destination flat file ( csv or text)

Click Edit Button and it will open Script Task Editor.

Under #region Namespaces, I have added below code

using System.IO;


Under public void Main() { 

I have added below code.




            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            try
            {

                //Declare Variables
       string DestinationFolder = Dts.Variables["User::DestinationFolder"].Value.ToString();
       string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
       string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString();
       string SourceFolder = Dts.Variables["User::SourceFolder"].Value.ToString();
       string FileName = Dts.Variables["User::FileName"].Value.ToString();
       string DestinationFileExtension = Dts.Variables["User::DestinationFileExtension"].Value.ToString();

                //Building Destination file name
       string FileFullPath = DestinationFolder + "\\" + FileName + "_" + datetime + DestinationFileExtension;

                int counter = 0;

                //Looping through the flat files 
                string[] fileEntries = Directory.GetFiles(SourceFolder, "*" + FileExtension);
                foreach (string fileName in fileEntries)
                {

                   string line;

                    System.IO.StreamReader SourceFile =
                    new System.IO.StreamReader(fileName);

                    StreamWriter sw = null;
                    sw = new StreamWriter(FileFullPath, true);

                    int linecnt = 0;
                    while ((line = SourceFile.ReadLine()) != null)
                    {
                        //Write only the header from first file
                        if (counter == 0 && linecnt == 0)
                        {
                            sw.Write(line);
                            sw.Write(sw.NewLine);

                        }
                        //Write data records from flat files
                        if (linecnt != 0)
                        {
                            sw.Write(line);
                            sw.Write(sw.NewLine);

                        }
                        linecnt++;
                        counter++;
                    }

                    sw.Close();
                    Dts.TaskResult = (int)ScriptResults.Success;
                }

            }

            catch (Exception exception)
            {

                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(Dts.Variables["User::LogFolder"].Value.ToString() + "\\" +
                    "ErrorLog_" + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());
                    Dts.TaskResult = (int)ScriptResults.Failure;

                }
            }

Step 4:

Save the script in script task editor and then exit. Run your SSIS Package, it should create single file in destination folder from multiple source files. You are free to change the value in variable to create txt or csv file from txt or csv files.

I generate csv and txt destination file from my sample source files as shown below.

How to create single flat file from multiple flat files in SSIS Package by using Script Task

Video Demo: How to create single file from multiple cvs files in SSIS Package

Welcome To TechBrothersIT

Label

How to Load all text/csv files to single Text/CSV File from a folder in SSIS Package - SSIS Tutorial

Scenario : Download Script

Solution:

Related Posts / Videos on Dynamic Text / CSV files by Script Task

3 comments: