Short answer: there is no way do to exactly that (at least without a pain).
You can use Script Task to do that.
To save more than 255 characters to Excel file in SSIS, don’t use Excel Destination. It won't work except a few edge cases. Instead, use combination of SQL Script Task + Script Task:
The general idea is to store your dataset in a package variable and save this dataset using a library that could save data in Excel format. I am using the MiniExcel assembly that you can get using NuGet. However, there is a caveat: scripting tasks cannot use NuGet packages directly, instead you have to install the assembly into Global Assembly Cache (GAC). To do that, your library of choice must be signed and compatible with .net Framework installed on the machine and compatible with VS 2019. MiniExcel is compatible with .net Framework 4.5 which is installed on my server, so I used exactly that.
Open https://nuget.info/packages/MiniExcel/1.31.3 (my solution is tested with version 1.31.3) and double-click on lib\net45\MiniExcel.dll
This will download the assembly dll file.
Save the file and open the terminal window from the Visual Studio menu: Tools - Command Line - Developer Command Prompt. In the command prompt, navigate to the folder where yo have stored MiniExcel.dll:
cd c:\temp\miniexcel\
Now, install the assembly:
gacutil.exe -i miniexcel.dll
You should see "Assembly successfully added to the cache". If any error occurs, this could mean you need to start the console with elevated permissions.
You have to create a package variable of type Object to transfer the dataset from SQL Task to Script Task. For this, right-click on empty space inside the package canvas and select Variables. In the Variables window, click on the first button in the toolbar, give a name to the variable, and choose Data Type - Object:
Open the editor of Execute SQL Task, select your OLE DB Connection and enter your select statement into SQLStatement. ResultSet property must be set to "Full result set":
Now, go to the Result Set pane (select it in the left part of the window), put "0" into the Result Name field and choose your Variable created previously:
Close window by pressing OK.
Put your Script Task on the canvas, and double-click on it to open the task editor, then click on "…" next to ReadOnlyVariables property. Select your dataset variable and close windows by pressing OK:
Open Visual Studio by clicking on the Edit Script… button.
In the Visual Studio window, go to project properties by right-clicking on the project name:
In Project Properties, select .NET Framework 4.5 as the target framework:
Save settings and switch to ScriptMain.cs code editor window. Now, go to Solution Explorer and right-click on References:
In Reference Manager, click on the "Browse" button:
Navigate to C:\Windows\Microsoft.NET\assembly\GAC_MSIL\MiniExcel\v4.0_1.31.3.0__e7310002a53eac39 and select MiniExcel.dll then click the Add button and then the OK button.
In the code, expand the namespaces region and add two references:
using System.Data.OleDb;
using MiniExcelLibs;Add the following code to your Main method:
public void Main()
{
// You can use
// String outFile = Dts.Variables["User::yourOutputFile"].Value.ToString();
// to pass output file name in yourOutputFile user variable (datatype = String)
String outFile = "c:\temp\export.xlsx";
// This fills DataTable ds from _ds variable
OleDbDataAdapter da = new OleDbDataAdapter();
DataTable ds = new DataTable();
da.Fill(ds, Dts.Variables["User::_ds"].Value);
// This static method saves DataTable using the provided filename, in worksheet
// named "Export Data", overwriting the file if it exists already
// (last parameter = true)
MiniExcel.SaveAs(outFile, ds, true, "Export Data", ExcelType.XLSX, null, true);
Dts.TaskResult = (int)ScriptResults.Success;
}Save, close and run the package.
NB!: This method probably wouldn't work for a huge amount of data as it requires memory to be allocated to store the dataset in the variable. Watch your counters then.