SSIS: Problems with data types DT_TEXT in Script Task

1

I have a Script to read the data on a sheet Excel . There are 78 columns and the first 10 are of type varchar(max) , so I had to put the data% Script to the outputs of DT_TEXT .

The script is as follows:

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;
using System.Data.OleDb;
using System.Text;
#endregion

/// <summary>
/// This is the class to which to add your code.  Do not change the name, attributes, or parent
/// of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    #region Help:  Using Integration Services variables and parameters
    /* 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 component, according to whether or not your
     * code needs to write into the variable.  To do so, 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 or parameter:
     *  DateTime startTime = Variables.MyStartTime;
     *
     * Example of writing to a variable:
     *  Variables.myStringVariable = "new value";
     */
    #endregion

    #region Help:  Using Integration Services Connnection Managers
    /* Some types of connection managers can be used in this script component.  See the help topic
     * "Working with Connection Managers Programatically" for details.
     *
     * To use a connection manager in this script, first ensure that the connection manager has
     * been added to either the list of connection managers on the Connection Managers page of the
     * script component editor.  To add the connection manager, save this script, close this instance of
     * Visual Studio, and add the Connection Manager to the list.
     *
     * If the component needs to hold a connection open while processing rows, override the
     * AcquireConnections and ReleaseConnections methods.
     * 
     * Example of using an ADO.Net connection manager to acquire a SqlConnection:
     *  object rawConnection = Connections.SalesDB.AcquireConnection(transaction);
     *  SqlConnection salesDBConn = (SqlConnection)rawConnection;
     *
     * Example of using a File connection manager to acquire a file path:
     *  object rawConnection = Connections.Prices_zip.AcquireConnection(transaction);
     *  string filePath = (string)rawConnection;
     *
     * Example of releasing a connection manager:
     *  Connections.SalesDB.ReleaseConnection(rawConnection);
     */
    #endregion

    #region Help:  Firing Integration Services Events
    /* This script component can fire events.
     *
     * Example of firing an error event:
     *  ComponentMetaData.FireError(10, "Process Values", "Bad value", "", 0, out cancel);
     *
     * Example of firing an information event:
     *  ComponentMetaData.FireInformation(10, "Process Values", "Processing has started", "", 0, fireAgain);
     *
     * Example of firing a warning event:
     *  ComponentMetaData.FireWarning(10, "Process Values", "No rows were received", "", 0);
     */
    #endregion

    /// <summary>
    /// This method is called once, before rows begin to be processed in the data flow.
    ///
    /// You can remove this method if you don't need to do anything here.
    /// </summary>
    public override void PreExecute()
    {
        base.PreExecute();
        /*
         * Add your code here
         */
    }

    /// <summary>
    /// This method is called after all the rows have passed through this component.
    ///
    /// You can delete this method if you don't need to do anything here.
    /// </summary>
    public override void PostExecute()
    {
        base.PostExecute();
        /*
         * Add your code here
         */
    }

    public override void CreateNewOutputRows()
    {
        OpenReadAndCloseExcel();
    }

    private string GetConnectionString(string p_strFileName)
    {
        if (!File.Exists(p_strFileName))
            throw new IOException("Fichero excel no localizado en la ruta: " + p_strFileName);

        string extension = Path.GetExtension(p_strFileName);
        extension = extension.Replace(".", "");

        string l_strConnectionString;

        if (extension.ToLower() == "xlsm")
        {
            l_strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                "Data Source=" + p_strFileName + ";Extended Properties=\"Excel 12.0 Macro;HDR=NO;IMEX=1\";";
        }
        else
        {
            l_strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + p_strFileName + ";Extended Properties=\"Excel 4.0;HDR=NO;IMEX=1\";";
        }


        return l_strConnectionString;
    }

    private void ReadExcelData(OleDbDataReader excelReader)
    {

        while (excelReader.Read()  )
        {
            if (excelReader[0] == null || excelReader[0].ToString() == string.Empty )
                break;
            Salida0Buffer.AddRow();
            var a = Encoding.Unicode.GetBytes(excelReader.FieldCount > 0 ? excelReader[0].ToString() : "");
            Salida0Buffer.Columna.AddBlobData(Encoding.Unicode.GetBytes (excelReader.FieldCount > 0 ? excelReader[0].ToString() : ""));
            Salida0Buffer.Columna2.AddBlobData(Encoding.Unicode.GetBytes (excelReader.FieldCount > 1 ? excelReader[1].ToString() : ""));
            Salida0Buffer.Columna3.AddBlobData(Encoding.Unicode.GetBytes (excelReader.FieldCount > 2 ? excelReader[2].ToString() : ""));
            Salida0Buffer.Columna4.AddBlobData(Encoding.Unicode.GetBytes ( excelReader.FieldCount > 3 ? excelReader[3].ToString() : ""));
            Salida0Buffer.Columna5.AddBlobData (Encoding.Unicode.GetBytes (excelReader.FieldCount > 4 ? excelReader[4].ToString() : ""));
            Salida0Buffer.Columna6.AddBlobData (Encoding.Unicode.GetBytes (excelReader.FieldCount > 5 ? excelReader[5].ToString() : ""));
            Salida0Buffer.Columna7.AddBlobData (Encoding.Unicode.GetBytes (excelReader.FieldCount > 6 ? excelReader[6].ToString() : ""));
            Salida0Buffer.Columna8.AddBlobData (Encoding.Unicode.GetBytes (excelReader.FieldCount > 7 ? excelReader[7].ToString() : ""));
            Salida0Buffer.Columna9.AddBlobData (Encoding.Unicode.GetBytes (excelReader.FieldCount > 8 ? excelReader[8].ToString() : ""));
            Salida0Buffer.Columna10.AddBlobData(Encoding.Unicode.GetBytes(excelReader.FieldCount > 9 ? excelReader[9].ToString() : ""));
            Salida0Buffer.Columna11 = excelReader.FieldCount > 10 ? excelReader[10].ToString() : "";
            Salida0Buffer.Columna12 = excelReader.FieldCount > 11 ? excelReader[11].ToString() : "";
            Salida0Buffer.Columna13 = excelReader.FieldCount > 12 ? excelReader[12].ToString() : "";
            Salida0Buffer.Columna14 = excelReader.FieldCount > 13 ? excelReader[13].ToString() : "";
            Salida0Buffer.Columna15 = excelReader.FieldCount > 14 ? excelReader[14].ToString() : "";
            Salida0Buffer.Columna16 = excelReader.FieldCount > 15 ? excelReader[15].ToString() : "";
            Salida0Buffer.Columna17 = excelReader.FieldCount > 16 ? excelReader[16].ToString() : "";
            Salida0Buffer.Columna18 = excelReader.FieldCount > 17 ? excelReader[17].ToString() : "";
            Salida0Buffer.Columna19 = excelReader.FieldCount > 18 ? excelReader[18].ToString() : "";
            Salida0Buffer.Columna20 = excelReader.FieldCount > 19 ? excelReader[19].ToString() : "";
            Salida0Buffer.Columna21 = excelReader.FieldCount > 20 ? excelReader[20].ToString() : "";
            Salida0Buffer.Columna22 = excelReader.FieldCount > 21 ? excelReader[21].ToString() : "";
            Salida0Buffer.Columna23 = excelReader.FieldCount > 22 ? excelReader[22].ToString() : "";
            Salida0Buffer.Columna24 = excelReader.FieldCount > 23 ? excelReader[23].ToString() : "";
            Salida0Buffer.Columna25 = excelReader.FieldCount > 24 ? excelReader[24].ToString() : "";
            Salida0Buffer.Columna26 = excelReader.FieldCount > 25 ? excelReader[25].ToString() : "";
            Salida0Buffer.Columna27 = excelReader.FieldCount > 26 ? excelReader[26].ToString() : "";
            Salida0Buffer.Columna28 = excelReader.FieldCount > 27 ? excelReader[27].ToString() : "";
            Salida0Buffer.Columna29 = excelReader.FieldCount > 28 ? excelReader[28].ToString() : "";
            Salida0Buffer.Columna30 = excelReader.FieldCount > 29 ? excelReader[29].ToString() : "";
            Salida0Buffer.Columna31 = excelReader.FieldCount > 30 ? excelReader[30].ToString() : "";
            Salida0Buffer.Columna32 = excelReader.FieldCount > 31 ? excelReader[31].ToString() : "";
            Salida0Buffer.Columna33 = excelReader.FieldCount > 32 ? excelReader[32].ToString() : "";
            Salida0Buffer.Columna34 = excelReader.FieldCount > 33 ? excelReader[33].ToString() : "";
            Salida0Buffer.Columna35 = excelReader.FieldCount > 34 ? excelReader[34].ToString() : "";
            Salida0Buffer.Columna36 = excelReader.FieldCount > 35 ? excelReader[35].ToString() : "";
            Salida0Buffer.Columna37 = excelReader.FieldCount > 36 ? excelReader[36].ToString() : "";
            Salida0Buffer.Columna38 = excelReader.FieldCount > 37 ? excelReader[37].ToString() : "";
            Salida0Buffer.Columna39 = excelReader.FieldCount > 38 ? excelReader[38].ToString() : "";
            Salida0Buffer.Columna40 = excelReader.FieldCount > 39 ? excelReader[39].ToString() : "";
            Salida0Buffer.Columna41 = excelReader.FieldCount > 40 ? excelReader[40].ToString() : "";
            Salida0Buffer.Columna42 = excelReader.FieldCount > 41 ? excelReader[41].ToString() : "";
            Salida0Buffer.Columna43 = excelReader.FieldCount > 42 ? excelReader[42].ToString() : "";
            Salida0Buffer.Columna44 = excelReader.FieldCount > 43 ? excelReader[43].ToString() : "";
            Salida0Buffer.Columna45 = excelReader.FieldCount > 44 ? excelReader[44].ToString() : "";
            Salida0Buffer.Columna46 = excelReader.FieldCount > 45 ? excelReader[45].ToString() : "";
            Salida0Buffer.Columna47 = excelReader.FieldCount > 46 ? excelReader[46].ToString() : "";
            Salida0Buffer.Columna48 = excelReader.FieldCount > 47 ? excelReader[47].ToString() : "";
            Salida0Buffer.Columna49 = excelReader.FieldCount > 48 ? excelReader[48].ToString() : "";
            Salida0Buffer.Columna50 = excelReader.FieldCount > 49 ? excelReader[49].ToString() : "";
            Salida0Buffer.Columna51 = excelReader.FieldCount > 50 ? excelReader[50].ToString() : "";
            Salida0Buffer.Columna52 = excelReader.FieldCount > 51 ? excelReader[51].ToString() : "";
            Salida0Buffer.Columna53 = excelReader.FieldCount > 52 ? excelReader[52].ToString() : "";
            Salida0Buffer.Columna54 = excelReader.FieldCount > 53 ? excelReader[53].ToString() : "";
            Salida0Buffer.Columna55 = excelReader.FieldCount > 54 ? excelReader[54].ToString() : "";
            Salida0Buffer.Columna56 = excelReader.FieldCount > 55 ? excelReader[55].ToString() : "";
            Salida0Buffer.Columna57 = excelReader.FieldCount > 56 ? excelReader[56].ToString() : "";
            Salida0Buffer.Columna58 = excelReader.FieldCount > 57 ? excelReader[57].ToString() : "";
            Salida0Buffer.Columna59 = excelReader.FieldCount > 58 ? excelReader[58].ToString() : "";
            Salida0Buffer.Columna60 = excelReader.FieldCount > 59 ? excelReader[59].ToString() : "";
            Salida0Buffer.Columna61 = excelReader.FieldCount > 60 ? excelReader[60].ToString() : "";
            Salida0Buffer.Columna62 = excelReader.FieldCount > 61 ? excelReader[61].ToString() : "";
            Salida0Buffer.Columna63 = excelReader.FieldCount > 62 ? excelReader[62].ToString() : "";
            Salida0Buffer.Columna64 = excelReader.FieldCount > 63 ? excelReader[63].ToString() : "";
            Salida0Buffer.Columna65 = excelReader.FieldCount > 64 ? excelReader[64].ToString() : "";
            Salida0Buffer.Columna66 = excelReader.FieldCount > 65 ? excelReader[65].ToString() : "";
            Salida0Buffer.Columna67 = excelReader.FieldCount > 66 ? excelReader[66].ToString() : "";
            Salida0Buffer.Columna68 = excelReader.FieldCount > 67 ? excelReader[67].ToString() : "";
            Salida0Buffer.Columna69 = excelReader.FieldCount > 68 ? excelReader[68].ToString() : "";
            Salida0Buffer.Columna70 = excelReader.FieldCount > 69 ? excelReader[69].ToString() : "";
            Salida0Buffer.Columna71 = excelReader.FieldCount > 70 ? excelReader[70].ToString() : "";
            Salida0Buffer.Columna72 = excelReader.FieldCount > 71 ? excelReader[71].ToString() : "";
            Salida0Buffer.Columna73 = excelReader.FieldCount > 72 ? excelReader[72].ToString() : "";
            Salida0Buffer.Columna74 = excelReader.FieldCount > 73 ? excelReader[73].ToString() : "";
            Salida0Buffer.Columna75 = excelReader.FieldCount > 74 ? excelReader[74].ToString() : "";
            Salida0Buffer.Columna76 = excelReader.FieldCount > 75 ? excelReader[75].ToString() : "";
            Salida0Buffer.Columna77 = excelReader.FieldCount > 76 ? excelReader[76].ToString() : "";
        }

    }

    private void OpenReadAndCloseExcel()
    {
        OleDbDataReader excelReader = null;
        OleDbConnection excelConnection = null;

        //System.Windows.Forms.MessageBox.Show("hola mundo");

        try
        {
            excelConnection = new OleDbConnection(GetConnectionString(Variables.Ruta));
            excelConnection.Open();
            OleDbCommand excelCommand = excelConnection.CreateCommand();
            //excelCommand.CommandText = "SELECT * FROM [" + Variables.Hoja_Excel_Rango.ToString() + "]";
            excelCommand.CommandText = "SELECT * FROM [" + Variables.Variablev + "]";
            excelCommand.CommandType = CommandType.Text;
            excelReader = excelCommand.ExecuteReader();

            //System.Windows.Forms.MessageBox.Show("hola mundo2");

            ReadExcelData(excelReader);
        }
        finally
        {
            if (excelReader != null) excelReader.Close();
            if (excelConnection != null) excelConnection.Close();
        }
    }


}

When loading the data in the destination table, only the first character is loaded.

Can you help me?

Thanks :)

    
asked by alm 16.01.2018 в 18:23
source

1 answer

1

I have already solved it!

You just have to modify the following:

Output0Buffer.Column.AddBlobData ( Encoding.GetEncoding (1252) . GetBytes (excelReader.FieldCount> 0? excelReader [0] .ToString (): ""));

: D

    
answered by 17.01.2018 / 17:36
source