How to export data to multiple flat files from Single SQL Server Table depending upon Column Distinct values in SSIS Package - SSIS Tutorial

Scenario: Download Script

You are working as ETL developer or an SSIS developer. You have a table let's say dbo.TotalSale. Once of the column in this table is Region. You would like to create multiple flat files dynamically from table by using distinct Region Value Or you can say that for each Region you would like to generate separate file.


If there are three distinct Regions values in Region Column, it should create three flat files, one for each region.

So if tomorrow, the Region decreases or increases, the flat files should be created according to distinct values.

In your scenario, you can chose any column that you would like to use for dividing the table data to multiple flat files.

How to create multiple flat files from single SQL Server Table in SSIS Package - SSIS Tutorial


Solution:

We are going to use Script Task in SSIS Package to create multiple flat files from single Table data depending upon the distinct value from a column. 

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.

ColumnNameForGrouping : Provide the column you would like to use for creating sheet data
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.
FolderPath: Provide the Path where you would like to create excel 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 variables in SSIS Package to create multiple flat files from single sql server table


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 create multiple text or csv files from single 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 Excel Sheets in Excel File
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();
      string ColumnNameForGrouping = Dts.Variables["User::ColumnNameForGrouping"].Value.ToString();


                //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 distinct " + ColumnNameForGrouping + " from " + TableName;

                //MessageBox.Show(query.ToString());
                SqlCommand cmd = new SqlCommand(query, myADONETConnection);
                //myADONETConnection.Open();
                DataTable dt = new DataTable();
                dt.Load(cmd.ExecuteReader());
                myADONETConnection.Close();

                //Loop through values for ColumnNameForGroup

                foreach (DataRow dt_row in dt.Rows)
                {
                    string ColumnValue = "";
                    object[] array = dt_row.ItemArray;
                    ColumnValue = array[0].ToString();


                    //Load Data into DataTable from SQL ServerTable
                    string queryString =
                     "SELECT * from " + TableName + " Where " + ColumnNameForGrouping + "='" + ColumnValue + "'";
              SqlDataAdapter adapter = new SqlDataAdapter(queryString, myADONETConnection);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);

                    foreach (DataTable d_table in ds.Tables)
                    {
                        string FileFullPath = DestinationFolder +"\\"+ FileNamePart + "_"+ColumnValue+"_" + 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();
                    }

                }

            }

            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 SSIS Package to create Multiple flat files from single SQL Server Table
Hit save button and then close the script task editor window. Run your SSIS Package and it should create multiple flat files from single SQL server Table. Number of files depends upon distinct values in column that we have used for grouping.

I executed the package by using .txt and .csv extensions and it worked and created multiple files from sql server sample table as shown in starting of post.
How to create csv or text files dynamically from single SQL Server Table in SSIS Package



Video Demo: How to create multiple flat files from single SQL Server Table





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



2 comments:

  1. Thank you for sharing, I have an error doing this procedure this is the log:
    System.InvalidOperationException: ExecuteReader: Connection property has not been initialized.
    at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader()
    at ST_8baa6b4232fb43a681aa956a2b143618.ScriptMain.Main()

    Can you help me?

    Regards

    ReplyDelete