Create Excel File Dynamically for Stored Procedure Results in SSIS Package by using Script Task-P165

i am retriving data from SQL Query it has some joins with different tables how can i use this in script task

Excellent video! What version of VS are you using?

trying to create this one.but, why suddenly errorand log.txt send the result with "System.NullReferenceException: Object reference not set to an instance of an object.
at ST_b9d46565e3754868b3b7ff43bdfde09c.ScriptMain.Main()"

what's that mean ? please help

Very useful, nicely presented.👍

Hi sir. Very helpful video. Can you show how to export from multiple tables to different sheets in the same workbook?

Great format and simple to follow with plenty of details - Thanks. I am new to this so I tried to follow along and I am getting the follow error. any suggestions? System.InvalidOperationException: Fill: SelectCommand.Connection property has not been initialized.
at System.Data.Common.DbDataAdapter.GetConnection3(DbDataAdapter adapter, IDbCommand command, String method)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at ST_b9b0ce053b4249359fdbd25b9a37dfbf.ScriptMain.Main()

It was very helpful indeed. Thanks for sharing this.

I am looking to do this but gives me following errors:
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

Script build has no error and I did change the connection name.

i'm using 2017 version of Visual Studio, I can't seem to find neither user folder path nor user excel

Great tutorial, but I am having timeout error in script task, running SP that runs for 6min, but it stops after 30s I tried adding this line to script, but it did not work.
DataSet ds = new DataSet();
adapter.Fill(ds);
adapter.SelectCommand.CommandTimeout = 1800;
Do you know how to upgrade this script to not have timeout error

Another excellent tutorial. Thanks.

Good Job :)

This is very helpful video, however the export data type are all text, could you please advise how do convert all datatype from text to neccessary format?

could you please tell me how to use parameters of stored procedures in script task?

I find your post very relevant and easy to understand. I have used some of them in solving my problems. I have found one to read a CSV file and produce an Excel file, which I found very useful. One I have not found in your series is the formatting of an existing Excel file using a script task , by coloring some column headers; and re-saving the same Excel file. I have found some scripts in MSDN Microsoft pages where a Foreach loop is used along with a script task, but it has not worked for me. Do you have a SSIS example to reformat Excel files? Or a script task t do? Thanks in advance