C# in Script Task of SSIS

[yrstruly]

I am running C# in SSIS Script Task to convert an Excel to CSV. I have modified a code that is working, which is:

#region Help: Introduction to the script task
/* The Script Task allows you to perform virtually any operation that can be accomplished in
 * a .Net application within the context of an Integration Services control flow. 
 * 
 * Expand the other regions which have "Help" prefixes for examples of specific ways to use
 * Integration Services features within this script task. */
#endregion

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

namespace ST_2b47b8ac8e2c4267bda654e8a458e33e
{
    /// <summary>
    /// ScriptMain is the entry point class of the script. Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        #region Help: Using Integration Services variables and parameters in a script
        /* To use a variable in this script, first ensure that the variable has been added to
         * either the list contained in the ReadOnlyVariables property or the list contained in
         * the ReadWriteVariables property of this script task, according to whether or not your
         * code needs to write to the variable. To add the variable, save this script, close this instance of
         * Visual Studio, and update the ReadOnlyVariables and
         * ReadWriteVariables properties in the Script Transformation Editor window.
         * To use a parameter in this script, follow the same steps. Parameters are always read-only.
         *
         * Example of reading from a variable:
         * DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
         *
         * Example of writing to a variable:
         * Dts.Variables["User::myStringVariable"].Value = "new value";
         *
         * Example of reading from a package parameter:
         * int batchId = (int) Dts.Variables["$Package::batchId"].Value;
         *
         * Example of reading from a project parameter:
         * int batchId = (int) Dts.Variables["$Project::batchId"].Value;
         *
         * Example of reading from a sensitive project parameter:
         * int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
         */
        #endregion

        #region Help: Firing Integration Services events from a script
        /* This script task can fire events for logging purposes.
         *
         * Example of firing an error event:
         * Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
         *
         * Example of firing an information event:
         * Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
         *
         * Example of firing a warning event:
         * Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
         */
        #endregion

        #region Help: Using Integration Services connection managers in a script
        /* Some types of connection managers can be used in this script task. See the topic
         * "Working with Connection Managers Programmatically" for details.
         *
         * Example of using an ADO.Net connection manager:
         * object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
         * SqlConnection myADONETConnection = (SqlConnection)rawConnection;
         * //Use the connection in some code here, then release the connection
         * Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
         *
         * Example of using a File connection manager
         * object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
         * string filePath = (string)rawConnection;
         * //Use the connection in some code here, then release the connection
         * Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
         */
        #endregion

        /// <summary>
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// </summary>
        public void Main()
        {
            // TODO: Add your code here
            string ConnString;
            ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Dts.Variables["User::Var_ExcelFilePath"].Value.ToString() +
                         ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\";";
            var conn = new OleDbConnection(ConnString);
            conn.Open();
            string query;
            query = "SELECT * FROM [" + Dts.Variables["User::Var_sheet_name"].Value.ToString() + "$]";
            var command = new OleDbCommand(query, conn);
            OleDbDataAdapter adap = new OleDbDataAdapter(command);
            var datatable = new DataTable();
            adap.Fill(datatable);

            // Create CSV file
            using (var sw = new StreamWriter(Dts.Variables["User::Var_CsvFilePath"].Value.ToString()))
            {
                for (int row = 0; row < datatable.Rows.Count; row++)
                {
                    var strRow = "";

                    for (int col = 0; col < datatable.Columns.Count; col++)
                    {
                        strRow += datatable.Rows[row][col].ToString() + "|";
                    }

                    // Remove last '|' from row
                    strRow = strRow.Remove(strRow.Length - 1);

                    // Write row to file
                    sw.WriteLine(strRow);
                }
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        ///
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
    }
}

My code:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;

namespace ST_a916a8b3a6d640be9f6302fae0a06c8e
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            // TODO: Add your code here
            string ConnString;
            ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Dts.Variables["User::Var_ExcelFilePath"].Value.ToString() +
                         ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\";";
            var conn = new OleDbConnection(ConnString);
            conn.Open();
            string query;
            query = "SELECT * FROM [" + Dts.Variables["User::Var_sheet_name"].Value.ToString() + "$]";
            var command = new OleDbCommand(query, conn);
            OleDbDataAdapter adap = new OleDbDataAdapter(command);
            var datatable = new DataTable();
            adap.Fill(datatable);

            // Create CSV file
            using (var sw = new StreamWriter(Dts.Variables["User::vr_csv_path"].Value.ToString()))
            {
                for (int row = 0; row < datatable.Rows.Count; row++)
                {
                    var strRow = "";

                    for (int col = 0; col < datatable.Columns.Count; col++)
                    {
                        strRow += datatable.Rows[row][col].ToString() + "|";
                    }

                    // Remove last '|' from row
                    strRow = strRow.Remove(strRow.Length - 1);

                    // Write row to file
                    sw.WriteLine(strRow);
                }
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        }
    }
}

I have attached the SSIS package for easy understanding of the issue.

 

[jmcilhinney]

You know what would actually enable easy understanding of the issue? If you were to explain it.

 

[Skydiver]

Not directly related to your problem: As an aside CSV files are comma separated. The pipe symbol ('|') is not a comma (',').

Now regarding your problem: From what I can see, you end up with a lot of string allocations with your constant appending to a line, or to a string, and then later removing the last character. Each of those operations allocates a brand new string. It takes time to allocate new memory and copy from the old string to the newly made string. Use String.Join() or a StringBuilder instead.

Another potential source of slow down is the constant access of data table.Rows[row]. Just grab a reference to the row just once and then access the columns from that row. E.g. line 31 can be a foreach(var row in datatable.Rows)

Beyond that, if the slow part is filling the data table, I question why you even need a data table. Why not just process the results of the query directly?

As another aside not directly related to your problem: recall that the ACE driver is not meant to be run on a server and Microsoft doesn't support it. (If you don't recall this, re-read the documentation for ACE.) If you have this code as a script that is run by your SSIS server that means you are running the driver on a server.

 

[yrstruly]

Thank you for responding. The code that works is:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;

namespace ST_a916a8b3a6d640be9f6302fae0a06c8e
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            string ConnString;
            ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                         Dts.Variables["User::Var_ExcelFilePath"].Value.ToString() +
                         ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\";";
            var conn = new OleDbConnection(ConnString);
            conn.Open();
            string query;
            query = "SELECT * FROM [Interco Sales$]"; // Hardcoded sheet name
            var command = new OleDbCommand(query, conn);
            OleDbDataAdapter adap = new OleDbDataAdapter(command);
            var datatable = new DataTable();
            adap.Fill(datatable);

            // Create CSV file
            using (var sw = new StreamWriter(Dts.Variables["User::Var_CsvFilePath"].Value.ToString())) // Fixed variable name
            {
                for (int row = 0; row < datatable.Rows.Count; row++)
                {
                    var strRow = "";

                    for (int col = 0; col < datatable.Columns.Count; col++)
                    {
                        strRow += datatable.Rows[row][col].ToString() + "|";
                    }

                    // Remove last '|' from row
                    strRow = strRow.Remove(strRow.Length - 1);

                    // Write row to file
                    sw.WriteLine(strRow);
                }
            }

            conn.Close(); // Close the connection
            Dts.TaskResult = (int)ScriptResults.Success;
        }

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        }
    }
}

AND

Thank you for responding. The code that works is:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;

namespace ST_a916a8b3a6d640be9f6302fae0a06c8e
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            string ConnString;
            ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                         Dts.Variables["User::Var_ExcelFilePath"].Value.ToString() +
                         ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\";";
            var conn = new OleDbConnection(ConnString);
            conn.Open();
            string query;
            query = "SELECT * FROM [Interco Sales$]"; // Hardcoded sheet name
            var command = new OleDbCommand(query, conn);
            OleDbDataAdapter adap = new OleDbDataAdapter(command);
            var datatable = new DataTable();
            adap.Fill(datatable);

            // Create CSV file
            using (var sw = new StreamWriter(Dts.Variables["User::Var_CsvFilePath"].Value.ToString())) // Fixed variable name
            {
                for (int row = 0; row < datatable.Rows.Count; row++)
                {
                    var strRow = "";

                    for (int col = 0; col < datatable.Columns.Count; col++)
                    {
                        strRow += datatable.Rows[row][col].ToString() + "|";
                    }

                    // Remove last '|' from row
                    strRow = strRow.Remove(strRow.Length - 1);

                    // Write row to file
                    sw.WriteLine(strRow);
                }
            }

            conn.Close(); // Close the connection
            Dts.TaskResult = (int)ScriptResults.Success;
        }

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        }
    }
}