SSIS: Leitura de ficheiro CSV a partir de um Script Task

// paulogp

/* read a csv file using Script Task object */

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

namespace ST_0aa122bddcbe4dcbb10c58ee3defe98d.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

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

        /*
        The execution engine calls this method when the task executes.
        To access the object model, use the Dts property. Connections, variables, events,
        and logging features are available as members of the Dts property as shown in the following examples.

        To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
        To post a log entry, call Dts.Log("This is my log text", 999, null);
        To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

        To use the connections collection use something like the following:
        ConnectionManager cm = Dts.Connections.Add("OLEDB");
        cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

        Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

        To open Help, press F1.
    */

        public void Main()
        {
            Boolean the_fire_again = true;
            string the_path = "D:\\web_files\\wk_total_factura.csv";

            // log
            Dts.Events.FireInformation(0, "Info: ", "Begin process!", string.Empty, 0, ref the_fire_again);

            if (File.Exists(the_path))
            {
                string the_line_content = "";
                string[] _values = null;
                // int the_line_no = 0;

                // streaming
                StreamReader the_stream = new StreamReader(the_path); // @the_path
                // for set encoding
                // StreamReader sr = new StreamReader(@"file.csv", Encoding.GetEncoding(1250));

                while (!the_stream.EndOfStream)
                {
                    // the_line_no++;
                    the_line_content = the_stream.ReadLine();
                    _values = the_line_content.Split(';');

                    // retrieve 7th column to output standard log
                    Dts.Events.FireInformation(0, "Row: ", _values[7] + " " + _values.Length, string.Empty, 0, ref the_fire_again);
                }
                the_stream.Close();
            }
            else
            {
                // MessageBox.Show("File not found!");
                Dts.Events.FireInformation(0, "Error: ", "File not found!", string.Empty, 0, ref the_fire_again);
            }

            // log
            Dts.Events.FireInformation(0, "Info: ", "End process!", string.Empty, 0, ref the_fire_again);

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