Script task failed to generate dynamic Excel workbook

Question

I want to load sql query(Number of column change every time)data in Excel workbook that create column header dynamically.

First i have create four variable

Table Customer_NA have some entries

And create Ado.net connection and configure server-name and database name

Drag Script task and and assign all 4 variable in ReadOnlyVariable.

Edit Script task and write code to generate excel sheet column dynamically

public void Main()
    {
        // TODO: Add your code here
        string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
        try
        {
            //Declare Variables
            string ExcelFileName = Dts.Variables["User::ExcelFileName"].Value.ToString();
            string FolderPath = Dts.Variables["User::FolderPath"].Value.ToString();
            string TableName = Dts.Variables["User::TableName"].Value.ToString();
            string SheetName = Dts.Variables["User::SheetName"].Value.ToString();
            ExcelFileName = ExcelFileName + "_" + datetime;

            OleDbConnection Excel_OLE_Con = new OleDbConnection();
            OleDbCommand Excel_OLE_Cmd = new OleDbCommand();

            //Construct ConnectionString for Excel
            string connstring = "Provider=Microsoft.ACE.OLEDB.16.0;" + "Data Source=" + FolderPath + ExcelFileName
                + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";

            //drop Excel file if exists
            File.Delete(FolderPath + "\\" + ExcelFileName + ".xlsx");

            //USE ADO.NET Connection from SSIS Package to get data from table
            SqlConnection myADONETConnection = new SqlConnection();
            myADONETConnection = (SqlConnection)(Dts.Connections["Ado_Conn"].AcquireConnection(Dts.Transaction) as SqlConnection);

            //Load Data into DataTable from SQL ServerTable
            // Assumes that connection is a valid SqlConnection object.
            string queryString =
              "SELECT * from " + TableName;
            SqlDataAdapter adapter = new SqlDataAdapter(queryString, myADONETConnection);
            DataSet ds = new DataSet();
            adapter.Fill(ds);


            //Get Header Columns
            string TableColumns = "";

            // Get the Column List from Data Table so can create Excel Sheet with Header
            foreach (DataTable table in ds.Tables)
            {
                foreach (DataColumn column in table.Columns)
                {
                    TableColumns += column + "],[";
                }
            }

            // Replace most right comma from Columnlist
            TableColumns = ("[" + TableColumns.Replace(",", " Text,").TrimEnd(','));
            TableColumns = TableColumns.Remove(TableColumns.Length - 2);
            //MessageBox.Show(TableColumns);


            //Use OLE DB Connection and Create Excel Sheet
            Excel_OLE_Con.ConnectionString = connstring;
            Excel_OLE_Con.Open();
            Excel_OLE_Cmd.Connection = Excel_OLE_Con;
            Excel_OLE_Cmd.CommandText = "Create table " + SheetName + " (" + TableColumns + ")";
            Excel_OLE_Cmd.ExecuteNonQuery();


            //Write Data to Excel Sheet from DataTable dynamically
            foreach (DataTable table in ds.Tables)
            {
                String sqlCommandInsert = "";
                String sqlCommandValue = "";
                foreach (DataColumn dataColumn in table.Columns)
                {
                    sqlCommandValue += dataColumn + "],[";
                }

                sqlCommandValue = "[" + sqlCommandValue.TrimEnd(',');
                sqlCommandValue = sqlCommandValue.Remove(sqlCommandValue.Length - 2);
                sqlCommandInsert = "INSERT into " + SheetName + "(" + sqlCommandValue.TrimEnd(',') + ") VALUES(";

                int columnCount = table.Columns.Count;
                foreach (DataRow row in table.Rows)
                {
                    string columnvalues = "";
                    for (int i = 0; i < columnCount; i++)
                    {
                        int index = table.Rows.IndexOf(row);
                        columnvalues += "'" + table.Rows[index].ItemArray[i] + "',";

                    }
                    columnvalues = columnvalues.TrimEnd(',');
                    var command = sqlCommandInsert + columnvalues + ")";
                    Excel_OLE_Cmd.CommandText = command;
                    Excel_OLE_Cmd.ExecuteNonQuery();
                }

            }
            Excel_OLE_Con.Close();
            Dts.TaskResult = (int)ScriptResults.Success;
        }
        catch (Exception exception)
        {

            // Create Log File for Errors
            using (StreamWriter sw = File.CreateText(Dts.Variables["User::FolderPath"].Value.ToString() + "\\" +
                Dts.Variables["User::ExcelFileName"].Value.ToString() + datetime + ".log"))
            {
                sw.WriteLine(exception.ToString());
                Dts.TaskResult = (int)ScriptResults.Failure;

            }
        }
    }

Same Ado_Conn connection name i have write in c# script still facing error after successfully buid script and run package and got error

Please help me out

I hope i Explain the scnerio

Answer 1

The alert shows a generic error message, therefore it is not possible to pinpoint the cause of the error.

Consider to add this line into a CATCH block:

Dts.Events.FireError(0, "Script Task Example", exception.Message + "\r" + exception.StackTrace, String.Empty, 0);

Also, perhaps it makes sense, for now, temporarily disable writing logs into a custom log file since this operation by itself can cause errors and they will not be captured by try..catch...

So the edited version will look this way:

catch (Exception exception)
        {
           Dts.Events.FireError(0, "Script Task", exception.Message + "\r" + exception.StackTrace, String.Empty, 0);
        // Create Log File for Errors
        //    using (StreamWriter sw = File.CreateText(Dts.Variables["User::FolderPath"].Value.ToString() + "\\" +
        //        Dts.Variables["User::ExcelFileName"].Value.ToString() + datetime + ".log"))
        //    {
        //        sw.WriteLine(exception.ToString());
        //        Dts.TaskResult = (int)ScriptResults.Failure;

        //    }
        }

The real reason of the exception will be routed to SSIS logs and can be tracked via SSDT output window or native SSIS Catalog logging in case if the package is deployed to a server