Export SQL to Excel (xlsx) using SSIS?

Question

I am SSIS noob (less than a week), so please bear with me. I am running a stored procedure to export its result to an Excel file.

From my research, I found that Excel SSIS assignment does not play well with .xlsx files (cannot be xls, as the result is more than ~ 65K lines), but I found that I can use OLE DB Destination to write to a file excel.

The problem I am seeing is the error message that appears on startup, which says:

OLE DB Destination [212]] Error: 
An error occurred while setting up a binding for the "Main Job Notes" column. 
The binding status was "DT_NTEXT"."

Fields that are errors come in the form of text streams ([DT_TEXT]), and since I received the error, unable to convert between unicode and un-unicode, I use Data Conversion to convert it to Unicode Text Stream ([DT_NTEXT] )

If that helps at all, my setup looks like this:

Any help would be awesome. Thank.

Answer 1

You should think about it with the script component, keep in mind that when you cannot debug directly during the data stream, but you can use mbox to check the results. Also keep in mind that excel will always try to automatically use the column data types, for example, when you try to import a file from Excel, so that one of its columns starts with a number, and in line 3455 a character, it will import the column as a number, and you lose the char value, you find it as null in your database.

, , , . ( , , excel csv.

/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.IO;
using System.Linq;
using System.Text;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    #region Variables
    private string _jumexDailyData;
    private string[] _jumexValues;
    private string[] _jumexWidthValues;      
    #endregion

    /// <summary>
    /// Default constructor
    /// </summary>
    public ScriptMain()
    {        
        this._jumexValues = new string[22];        
    }

    public override void PreExecute()
    {
        base.PreExecute();
        /*
          Add your code here for preprocessing or remove if not needed
        */
    }

    public override void PostExecute()
    {
        base.PostExecute();
        /*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */
    }

    public override void JumexDailyData_ProcessInput(JumexDailyDataBuffer Buffer)
    {        
        while (Buffer.NextRow())
            JumexDailyData_ProcessInputRow(Buffer);        
    }

    public override void JumexDailyData_ProcessInputRow(JumexDailyDataBuffer Row)
    {
        this._jumexDailyData = Row.JumexDailyData;
        if (this._jumexDailyData != null)
        {
            this._jumexWidthValues = this.Variables.JUMEXLOADSALESATTACHMENTFILEWIDTHVALUES.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
            if (this._jumexWidthValues != null && this._jumexWidthValues.Count() > 0)
                for (int i = 0; i < this._jumexWidthValues.Count(); i++)
                {
                    this._jumexValues[i] = this._jumexDailyData.Substring(0, int.Parse(this._jumexWidthValues[i])).Trim();
                    this._jumexDailyData = this._jumexDailyData.Substring(int.Parse(this._jumexWidthValues[i]), (this._jumexDailyData.Length - int.Parse(this._jumexWidthValues[i])));
                }

            if (string.IsNullOrEmpty(this._jumexValues[3].Trim()) == false &&
                string.IsNullOrEmpty(this._jumexValues[17].Trim()) == false &&
                !this._jumexValues[3].Contains("---") &&
                !this._jumexValues[17].Contains("---") &&
                !this._jumexValues[3].Trim().ToUpper().Contains("FACTURA") &&
                !this._jumexValues[17].Trim().ToUpper().Contains("PEDIDO"))                
                using (StreamWriter streamWriter = new StreamWriter(this.Variables.JUMEXFULLQUALIFIEDLOADSALESATTACHMENTFILENAME.Replace(".TXT", ".CSV"), true, Encoding.Default))
                {
                    streamWriter.WriteLine(string.Join("|", this._jumexValues));
                }
        }        
    }

}