Exporting SQL to Excel (xlsx) using SSIS?

Question

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

From my research I have found that SSIS's Excel Destination does not play nicely with .xlsx files (can't be xls since I have more than the ~65K rows in the result), but I found that I can use a OLE DB Destination to write to an excel file.

The issue I am seeing is an error message that occurs on run that 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"."

The fields that are erroring are coming in as Text Streams ([DT_TEXT]), and since I was getting an error around not being able to convert between unicode and non-unicode, I use a Data Conversion to transform it into a Unicode text stream ([DT_NTEXT])

If it helps at all, my setup is as follows:

Any help would be amazing. Thank you.

Answer 1

You should consider doing this using a script component, keep in mind that when in data flow task you cannot debug directly but you can use mbox snipped to check results. Also keep in mind that excel will always try to suppose your column data types automatically, for example when you try to import a file from excel that one of its columns starts with a number but in the row 3455 there's a character, it will import the column as a number and you will lose the char value, you will find it as null in your database.

I will give you some code to construct the file you need programmatically, maybe it can give you an idea. (This example reads a file as one column, then it will split in as if you chose fixed with delimited values in excel and will output in a csv file.

/* 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));
                }
        }        
    }

}