Welcome To TechBrothersIT: How to create Text or CSV File Dynamically from Table or View in SSIS Package by using Script Task

Scenario: Download Script

You are working as SQL Server Integration Services developer, You are asked to create an SSIS Package that should get the data from table or view and create flat file with date-time. But there is catch, the definition of table or view can change anytime. Your Package should be able to create flat file dynamically.

Solution:

As the number of columns can change anytime for Table or view from which we are extracting data, we can not really use the built-in Source and Destination. If we do, we have to remap columns every time table or view definition change.

We are going to use Script Task and handle the situation by using C# inside Script Task.

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.

FlatFileNamePart: Provide the file name that you would like to create. Date-time part 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 variables in SSIS Package to generate flat file dynamically from table or view

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 table or view data to export to flat file. This ADO.Net connection should be pointing to Database which contains the table from which we want to export data to flat file.

Create ADO.NET Connection in SSIS Package to use in Script Task to export data from table or view to text/csv file dynamically in SSIS Package

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 file dynamically from SQL Server Table or View in SSIS Package

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 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();
               


                //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 data from table or view to data table
                string query = "Select * From "+TableName;
                SqlCommand cmd = new SqlCommand(query, myADONETConnection);
                //myADONETConnection.Open();
                DataTable d_table = new DataTable();
                d_table.Load(cmd.ExecuteReader());
                myADONETConnection.Close();


                                       
              string FileFullPath = DestinationFolder +"\\"+ FileNamePart +"_" + 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();                            
                  
                    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 the flat file with datetime every time you will execute package. You have freedom to change the extension of file such as txt or csv. Also you can change delimiter by changing value of variable.

I execute package couple of times and it generated files as per variable values. Also I dropped the column in source table and view and tested. It was able to generate file dynamically without making any change to package.

How to create flat file dynamically from SQL Server Table or View in SSIS Package

Video Demo: How to create flat file dynamically from Table or View in SSIS Paclage

Welcome To TechBrothersIT

Label

How to create Text or CSV File Dynamically from Table or View in SSIS Package by using Script Task - SSIS Tutorial

Scenario: Download Script

Solution:

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

5 comments: