none
Como cargar archivo CSV a SQL Server con SSIS RRS feed

  • Pregunta

  • Estimados hola, estoy elaborando un flujo de trabajo en SSIS para cargar archivos Excel 2010 a una tabla en SQL Server 2008-R2, muchos de los campos en Excel tienen texto de mas de 255 caracteres y a veces se truncan en dicho valor, la solución que se recomienda según he investigado es hacer 01 paso adicional y pasar de Excel a CSV y luego hacer la carga. Los 02 inconvenientes que surgen son los siguientes:

    1. Luego de buscar en la red he encontrado y adaptado un script que me sirve para la conversión, pero en algunos casos genera saltos de linea malogrando la fila, generando una fila recortada o en blanco, esto a pesar de que veo que hace la salvedad con "\n" y ahí se detiene el flujo de trabajo (al parecer ese es el problema).

    2. He batallado mucho para tratar de identificar la codificación que tiene el archivo CSV generado ya que a veces no representa las "ñ" o los acentos o separa las comas en un solo campo (esto dentro de la cadena de conexión generada) y la que se adecua mas es "437 (OEM - Estados Unidos)".

    • Alguien conoce algún otro Script que pueda utilizar para convertir a CSV.
    • Como identificar la codificación correcta del archivo CSV para que me represente los caracteres tal cual son.

    Adjunto Script que estoy utilizando para conversión, está en C# y yo uso VB si por ahí lo tienen en ese lenguaje lo entendería mejor.

    /*
           Microsoft SQL Server Integration Services Script Task
           Write scripts using Microsoft Visual C# 2008.
           The ScriptMain is the entry point class of the script.
        */
        using System;
        using System.Data;
        using Microsoft.SqlServer.Dts.Runtime;
        using System.Windows.Forms;
        using Excel = Microsoft.Office.Interop.Excel;
        using Microsoft.Office.Interop.Excel;
        namespace ST_6dc747ff29cf41c6ac11b7c0bca33d19.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.
         */
                private static Workbook mWorkBook;
                private static Sheets mWorkSheets;
                private static Worksheet mWSheet1;
                private static Excel.Application oXL;
                private static string ErrorMessage = string.Empty;
                public void Main()
                {
                    try
                    {
                        string sourceExcelPathAndName = @"ruta\aaa.xlsx";
                        string targetCSVPathAndName = @"ruta\aaa.csv";
                        string excelSheetName = @"Sheet0";
                        string columnDelimeter = @"|#|";
                        int headerRowsToSkip = 2;
                        if (ConvertExcelToCSV(sourceExcelPathAndName, targetCSVPathAndName, excelSheetName, columnDelimeter, headerRowsToSkip) == true)
                        {
                            Dts.TaskResult = (int)ScriptResults.Success;
                        }
                        else
                        {
                            Dts.TaskResult = (int)ScriptResults.Failure;
                        }
                    }
                    catch (Exception ex)
                    {
                        Dts.TaskResult = (int)ScriptResults.Failure;
                    }
                }
                public static bool ConvertExcelToCSV(string sourceExcelPathAndName, string targetCSVPathAndName, string excelSheetName, string columnDelimeter, int headerRowsToSkip)
                {
                    try
                    {
                        oXL = new Excel.Application();
                        oXL.Visible = false;
                        oXL.DisplayAlerts = false;
                        Excel.Workbooks workbooks = oXL.Workbooks;
                        mWorkBook = workbooks.Open(sourceExcelPathAndName, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);
                        //Get all the sheets in the workbook
                        mWorkSheets = mWorkBook.Worksheets;
                        //Get the specified sheet
                        mWSheet1 = (Worksheet)mWorkSheets.get_Item(excelSheetName);
                        Excel.Range range = mWSheet1.UsedRange;
                        //deleting the specified number of rows from the top
                        Excel.Range rngCurrentRow;
                        for (int i = 0; i < headerRowsToSkip; i++)
                        {
                            rngCurrentRow = range.get_Range("A1", Type.Missing).EntireRow;
                            rngCurrentRow.Delete(XlDeleteShiftDirection.xlShiftUp);
                        }
                        //replacing ENTER with a space
                        range.Replace("\n", " ", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                        //replacing COMMA with the column delimeter
                        range.Replace(",", columnDelimeter, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                        mWorkBook.SaveAs(targetCSVPathAndName, XlFileFormat.xlCSVMSDOS,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
                        Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, false);
                        return true;
                    }
                    catch (Exception ex)
                    {
                        ErrorMessage = ex.ToString();
                        return false;
                    }
                    finally
                    {
                        if (mWSheet1 != null) mWSheet1 = null;
                        if (mWorkBook != null) mWorkBook.Close(Type.Missing, Type.Missing, Type.Missing);
                        if (mWorkBook != null) mWorkBook = null;
                        if (oXL != null) oXL.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
                        if (oXL != null) oXL = null;
                        GC.WaitForPendingFinalizers();
                        GC.Collect();
                        GC.WaitForPendingFinalizers();
                        GC.Collect();
                    }
                }
            }
        }

    miércoles, 7 de noviembre de 2018 15:20

Respuestas