Welcome To TechBrothersIT: How to export all the tables to Flat files dynamically from SQL Server Database in SSIS Package

How to export all the tables to Flat files dynamically from SQL Server Database in SSIS Package - SSIS Tutorial

Scenario : Download Script

You are working as SQL Server Integration Services(SSIS) developer. You need to create an SSIS Package that should create flat files for all the tables from a database dynamically. There should be one file for each table and it should be created with date-time.

Solution:

We are going to use Script Task in our SSIS Package to create flat files for all the tables in a database dynamically.

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.

DestinationFolder: Provide the destination folder path where you would like to create your files.

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 flat files for all the tables from a SQL Server Database

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 data for each table from database and create flat files. This ADO.Net connection should be pointing to Database from which we would like to export all tables.

Create ADO.NET Connection in SSIS Package to use in Script Task to export all the tables to 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 flat files dynamically from all the tables in SQL Server Database

Step 4: Add Script to Script task Editor in SSIS Package to Export Data from SQL Server Table or View to flat file dynamically.

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 DestinationFolder = Dts.Variables["User::DestinationFolder"].Value.ToString();
      string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
      string FileExtension = Dts.Variables["User::FileExtension"].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 list of Tables with Schema from Database
              string query = "SELECT Schema_name(schema_id) AS SchemaName,name AS TableName FROM   sys.tables WHERE  is_ms_shipped = 0";

                //MessageBox.Show(query.ToString());
                SqlCommand cmd = new SqlCommand(query, myADONETConnection);
                DataTable dt = new DataTable();
                dt.Load(cmd.ExecuteReader());
                
                //Loop through datatable(dt) that has schema and table names
           foreach (DataRow dt_row in dt.Rows)
                {
                    string SchemaName = "";
                    string TableName = "";
                    object[] array = dt_row.ItemArray;
                    SchemaName = array[0].ToString();
                    TableName = array[1].ToString();
                   
                   string FileFullPath =DestinationFolder +"\\"+ SchemaName+"_"+TableName + "_" + datetime+FileExtension;

                //Get the data for a table into data table 
                string data_query = "Select * From ["+SchemaName+"].["+TableName+"]";
                SqlCommand data_cmd = new SqlCommand(data_query, myADONETConnection);
                DataTable d_table = new DataTable();
                d_table.Load(data_cmd.ExecuteReader());
         
                        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();                            
                  
                    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 close the script task editor windows. Run your SSIS Package, it should create flat file for each of the table from a database. As it is going to create files for all the tables, it can take long time, depending upon the total tables and records in each table. I would suggest to test in DEV, QA and UAT environments before you run for production.

I executed for one of my database, really small and it exported all the tables to csv files with date-time as shown below.

How to export all the tables from a database to CSV / Text files dynamically in SSIS Package

Video Demo: How to Export all tables to Flat files in SSIS Package

Welcome To TechBrothersIT

Label

How to export all the tables to Flat files dynamically from SQL Server Database in SSIS Package - SSIS Tutorial

Scenario : Download Script

Solution:

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

6 comments: