CODEWITHSUNDEEP
sql-serverssisflat-file
cool breeze
cool breeze

Reputation: 4811

Split flat file destination for every 100 rows into a new file

I currently have a dataflow task which has a OLEDB source, and a flat file destination.

I now have to modify this to create a flat file as it has now, but for every 100 rows create a new file.

So if there are 250 rows in total, I would have to create 3 files.

file1.txt  // contains rows 1-100
file2.txt  // 101-200
file3.txt  // 201-250

The number of rows is dynamic, I don't know before hand how many rows/files I will have to create.

What options do I have?

I am using VS 2008 for this project.

Upvotes: 1

Views: 2147

Answers (1)

Eric Hauenstein
Eric Hauenstein

Reputation: 2565

I do something similar to this in a script task:

    public void Main()
    {
        string var_FileSource = Dts.Variables["var_FileSource"].Value.ToString();
        string baseName = var_FileSource + "file_";

        StreamWriter writer = null;

        try
        {
            using (StreamReader inputfile = new System.IO.StreamReader(var_FileSource + "P:\txt"))  
            {

                int count = 0;
                int filecount = 0;
                string line;

                while ((line = inputfile.ReadLine()) != null)
                {

                    if (writer == null || count > 99)
                    {
                        count = 0;
                        if (writer != null)
                        {
                            writer.Close();
                            writer = null;
                        }
                        ++filecount;
                        writer = new System.IO.StreamWriter(baseName + filecount.ToString() + ".txt", true);
                    }
                    writer.WriteLine(line);

                    ++count;
                }
            }
        }
        catch (Exception ex)
        {
            Dts.TaskResult = (int)ScriptResults.Failure;
        }

        finally
        {
            if (writer != null)
                writer.Close();
        }

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

This script should break the target files into 100 row subfiles, similar to what you want. I'm sure a C# expert could suggest some improvements.

Edit: Also change all of the paths to variable references, as well as the count value where you split the file. It will make future changes much easier.

Upvotes: 2

Related Questions