The following question was posed on the SSIS forum recently:

I have a table with 25 Million records. I want to transfer the records from the table to flat files. One flat file can contain only 1 Million record. So I need to generate 25 flat files with 1 Million in each file. Whats the best way to accomplish this task using SSIS.

On the surface this sounds fairly simple but if you look into it in a bit more detail it becomes an interesting little problem. Firstly, I can inteprete the question in one of two ways:

1. Always give me 25 files with the same number of records in each file, or,
2. Split the data into as many files as are required so that there are no more than 1 million rows in each file

#1 here is quite easy to accomplish so lets deal with that first. We simply have to split the data into the required number of files (which in the question was 25, but I've simpified it to 5). Here's the data-flow that does it. You will notice that I have used Konesans' Data Generator Source Adapter and 5 instances of the Trash Destination Adapterwhich are invaluable tools for scenarios like these. The data generator is used here to synthesize some random string data and the trash destination is used to simulate some flat files into which the data flows.

As you can see from the diagram we use a script. component to generate a surrogate key value (this technique is described here: http://www.sqlis.com/default.aspx?37). We use that value in the conditional spilt transform. along with the modulus (%) operator to split the data in a round-robin technique. Here is the conditional split editor showing this:

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

    int counter = 0;

    public override void PreExecute()

    {

        base.PreExecute();

    }

    public override void PostExecute()

    {

        base.PostExecute();

    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)

    {

        counter += 1;

        Row.SK = counter;

    }

}