SSIS - How to create flat files for all the tables from a SQL Server Database

Scenario:

Today's requirement is to generate flat files for all the tables of a SQL Server Database. The file name should be SchemaName_TableName.txt. 

Solution:

Let's start with step by step approach.

Step 1: 

Create an SSIS Package and then create the variables as shown below.

 Fig 1: Create variables in SSIS Package

FileDelimited: 

By changing the value of this variable you will be able to create file with required delimiter.

FileExt:

By using this variable, you can set the extension of files you want to create.

FileName:

You don't need to provide the value of this variable, This variable value will be populated in For-each loop from Object type variable.

FolderPath:

Provide the folder path in which do you want to create the flat files for all the tables.

SchemaName:

You don't need to provide any value for this variable. The value will be saved in this variable from Object type variable ( SchemaName_TableName) in For-Each Loop.

SchemaName_TableName:

This is object type variable. We will save Schema name and Table Names in this variable by using Execute SQL Task first and then we will loop through the records in For-each Loop.

Step 2: 

Create ADO.NET connection as shown below. You have to provide the ServerName and Database Name where your Tables exist.

To create ADO.NET connection, Right Click in Connection Manager Pane. Choose New ADO.NET Connection. Then in wizard you have to provide Server Name and Database Name.

 

Fig 2: Create ADO.NET connection

Step 3:

Bring Execute SQL Task to Control Flow Pane and configure as shown below. By using query we will fill the object type variable with Schema Name and Table Names List. You can always change the query according to your requirement.

SELECT Schema_name(schema_id) AS SchemaName,
       name                   AS TableName
FROM   sys.tables
WHERE  is_ms_shipped = 0 

 

Fig 3: Configure Execute SQL Task to populate Object Type variable with Schema Name and Table Names

Map the result set to object type variable.

 

Fig 4: Save the query results to SchemaName_TableName Object Type variable.

Step 4:

Bring the For-each Loop Container and then configure as shown below.

 

Fig 5: Loop through the records which we saved in SchemaName_TableName Object Variable

Go to Variable Mapping and then Map the variables as shown. We are reading the records from object type variable and saving the value in SchemaName and FileName variables on each iteration. These variables will be used in Script Task to generate text file.

Fig 6: Map variables in For-each loop container

Step 5:

Drag Script Task and place inside For-each Loop Container. Provide the list of variables as shown below.

Fig 7: Provide the variable list those will be used inside Script Task 

Step 6:

Paste the below code in Script Task.


#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
#endregion

namespace ST_7f3c3d5098254ef19723aca98d42a9f5
{
    /// 

    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// 

    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
   

        public void Main()
        {
            // TODO: Add your code here
            SqlConnection myADONETConnection = new SqlConnection();
            myADONETConnection = (SqlConnection)(Dts.Connections["ADONET_TestDB_Conn"].AcquireConnection(Dts.Transaction) as SqlConnection);
            //MessageBox.Show(myADONETConnection.ConnectionString, "ADO.NET Connection");

            string SchemaName = Dts.Variables["User::SchemaName"].Value.ToString();
            string FileName = Dts.Variables["User::FileName"].Value.ToString();
            string FileDelimited = Dts.Variables["User::FileDelimited"].Value.ToString();
            string FolderPath = Dts.Variables["User::FolderPath"].Value.ToString();
            string FileExt = Dts.Variables["User::FileExt"].Value.ToString();

            string query = "SELECT * FROM " + SchemaName + "." + FileName;

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

            StreamWriter sw = new StreamWriter(FolderPath + SchemaName+"_"+FileName+"."+FileExt, false);
            int ColumnCount = dt.Columns.Count;

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

            // Write All Rows to the File
            foreach (DataRow dr in dt.Rows)
            {
                for (int i = 0; i < ColumnCount; i++)
                {
                    if (!Convert.IsDBNull(dr[i]))
                    {
                        sw.Write(dr[i].ToString());
                    }
                    if (i < ColumnCount - 1)
                    {
                        sw.Write(FileDelimited);
                    }
                }
                sw.Write(sw.NewLine);
            }
            sw.Close();
           
            Dts.TaskResult = (int)ScriptResults.Success;
        }

        #region ScriptResults declaration
  
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    }
}

Save the script and close the Script Task Editor.

Your complete SSIS Package should look like this.

 

 Fig 8: SSIS Package to create flat file for each of the Table in SQL Server Database.

Let's run our SSIS Package and check if the text files are created for all the tables from a SQL Server database that we pointed in ADO.NET connection.

 

Fig 10: Text File created for all the Tables from a SQL Server Database.

This package can be good start to generate flat files/Text files for entire database tables but if you have a large tables in database this package can take long time to complete. The package is reading from one table at a time and writing to the file. You can replicate the Control Flow Components and divide the tables in Execute SQL Task to run the process in Parallel.