How to create multiple flat files form Single SQL Server Table by Splitting Rows in SSIS Package - SSIS Tutorial

Scenario: Download Script

Sometime we have tables with large of records. In one of the example, I created multiple flat files from single SQL Server Table by using distinct Column value. You can check the post here. If we have a column that can be used to divide the data, we can use that and create multiple flat files. 

But we have a unique situation , we don't have any column in table that we can use to divide the records to multiple files. But we want to have a control how many records should be written to each file. Let's say we have a table with 5 million records and we want to create 5 files , a million records on each of the file. How we would do that?


This package can be used as template, By just changing the name of the table you can create files from any table, you will be able to define the type of file such as csv or txt. You can choose the file delimiter as well.


Solution:

We are going to use Script Task in our SSIS Package to create multiple files by dividing row data or splitting data table. We will use Row_Number function to generate the row numbers for records and then write to multiple files. 


Step 1: Create new SSIS Package and create Variables 
Open your SSDT ( SQL Server Data Tools) and create new SSIS Package. Once done, create below variables as shown.

RecordsPerFile: Provide how many records you would like to export to each flat file
FlatFileNamePart: Provide the flat file name part, Column such as Region values will be added to it.
DestinationFolder: Provide the destination folder path where you would like to create your files. 
TableName: Provide the table name with schema from which you like to get data
LogFolder: Provide the Log folder path where you would like to create log file in case of error in Script Task.
FileDelimiter : Provide the file delimiter you would like to use, you can provide comma, pipe etc.
FileExtension: Provide the extension for your file such as txt or csv etc.

Create variable in SSIS Package to split single table data to multiple text or csv files-SSIS Tutorial


Step 2: Create ADO.NET Connection in SSIS Package to use in Script Task
Create ADO.NET Connection Manager so we can use in Script Task to get tables data to export to flat files. This ADO.Net connection should be pointing to Database which contains the table from which we want to export data to flat files.
Create ADO.NET Connection in SSIS Package to use in Script Task to export data from single table to multiple flat files 

Step3: Add Variables to Script Task to use from SSIS Package
Bring the Script Task on Control Flow Pane in SSIS Package and open by double clicking Check-box in front of variable to add to Script Task.

Add variables to Script Task to generate multiple flat files form single large SQL Server Table in SSIS Package




Step 4: Add Script to Script task Editor in SSIS Package to Export Data from single SQL Server Table to Multiple flat files

Click Edit Button and it will open Script Task Editor.
Under #region Namespaces, I have added below code

using System.IO;
using System.Data.SqlClient;


Under public void Main() { 
I have added below code.


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

                //Declare Variables
      string FileNamePart = Dts.Variables["User::FlatFileNamePart"].Value.ToString();
      string DestinationFolder = Dts.Variables["User::DestinationFolder"].Value.ToString();
      string TableName = Dts.Variables["User::TableName"].Value.ToString();
      string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
      string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString();
      Int32 RecordCntPerFile = (Int32)Dts.Variables["User::RecordsPerFile"].Value;
                string RecordCntPerFileDecimal = RecordCntPerFile + ".0";


                //USE ADO.NET Connection from SSIS Package to get data from table
                SqlConnection myADONETConnection = new SqlConnection();
                myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection);


                //Read distinct Group Values for each Excel Sheet
                string query = "select ceiling(count(*)/" + RecordCntPerFileDecimal + ") AS LoopCnt from " + TableName;
                decimal LoopCnt = 0;

                //Get the Count of Sheets need to be created
                SqlCommand cmd = myADONETConnection.CreateCommand();
                cmd.CommandText = query;
                LoopCnt = (decimal)cmd.ExecuteScalar();

                int startRowCnt = 0;
                int endRowCnt = RecordCntPerFile;
                
                for (int fileloop = 1; fileloop <= LoopCnt; fileloop++)
                {


                    //Load Data into DataTable from SQL ServerTable
                    string queryString = ";with cte as (Select *, Row_Number() over (order by (Select 1)) AS RowNumber from " +
                    TableName + ") Select * From cte where RowNumber > " + startRowCnt.ToString() + " and RowNumber<=" + endRowCnt.ToString();
              SqlDataAdapter adapter = new SqlDataAdapter(queryString, myADONETConnection);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);

                    foreach (DataTable d_table in ds.Tables)
                    {
                        string FileFullPath = DestinationFolder +"\\"+ FileNamePart + "_"+fileloop.ToString()+"_" + datetime + FileExtension;

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

                        // Write the Header Row to File
                        int ColumnCount = d_table.Columns.Count;
                        for (int ic = 0; ic < ColumnCount; ic++)
                        {
                            sw.Write(d_table.Columns[ic]);
                            if (ic < ColumnCount - 1)
                            {
                                sw.Write(FileDelimiter);
                            }
                        }
                        sw.Write(sw.NewLine);

                        // Write All Rows to the File
                        foreach (DataRow dr in d_table.Rows)
                        {
                            for (int ir = 0; ir < ColumnCount; ir++)
                            {
                                if (!Convert.IsDBNull(dr[ir]))
                                {
                                    sw.Write(dr[ir].ToString());
                                }
                                if (ir < ColumnCount - 1)
                                {
                                    sw.Write(FileDelimiter);
                                }
                            }
                            sw.Write(sw.NewLine);
                            
                        }
                        
                        sw.Close();
                        
                    }
                    startRowCnt += RecordCntPerFile;
                    endRowCnt += RecordCntPerFile;
                    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 5: Save the script and run the Package
Save the script in Script Task editor and then run your SSIS Package, it should create multiple files from table depending upon the total records in a table and RecordsPerFile you have provided. I provided RecordsPerfile=6 and I had total 11 records in my table, it generated two files as shown below.
How to split large table data to multiple flat files in SSIS Package - SSIS Tutorial




Video Demo: How to create multiple files from single sql server table by row count





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




7 comments:

  1. Hi,
    I need to export table into .csv file using the script task.But I am facing the below issues.
    1. the column type BIT values are converted into true or false instead of 1 and 0.
    2. the datetime column values are converted into system datetime format i.e. in the table it is like 12-7-2019 13:00:12 000 but the output file has 12/7/2019 1 PM since the server has 12 hours format.
    Can this solved in script task? Kindly assist.

    ReplyDelete
  2. Can I create this without using SSIS? I am running a C# windows application with SQL Server 2017 and I can not create SSIS packages

    ReplyDelete
  3. How to skip/exclude the header in the output files?

    ReplyDelete
  4. you can one example with filedelimiter ; or : please

    ReplyDelete
  5. you can one example with filedelimiter ; or : please over 1 millon row partition as flat file

    ReplyDelete
  6. how do you add a where clause to the select statement . it seems to allow error out

    ReplyDelete
  7. Hi, Please help me in below situation.
    I have a configuration table which has all the list of table names (There are so many records in this table). By reading this configuration table, loop through each record and create a csv file for each record.
    I want handle this in parallel processing or threading concept. How can I handle it. Each record (table) in Configuration table will have millions of records.

    Thanks In Advance.

    ReplyDelete