.NET (Core and Framework)

Error: 0xC0049064 at Populate Stage, Derived Column [97]: An error occurred while attempting to perform a type cast.
Error: 0xC0209029 at Populate Stage, Derived Column [97]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[Payment Due Date]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
Error: 0xC0047022 at Populate Stage, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Derived Column" (97) 
failed with error code 0xC0209029 while processing input "Derived Column Input" (98). The identified component returned an error from the ProcessInput method. 

The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

(DT_DATE)(SUBSTRING([Date Posted],1,4) + "-" + SUBSTRING([Date Posted],5,2) + "-" + SUBSTRING([Date Posted],7,2))

        static void ReadExcel(String strFileName, string strDelimiter) 
        {
            int RowCount = 0, ColCount = 0;

            //Write to text
            StreamWriter sw = new StreamWriter(@"C:\Test Files\Test.txt");
            String strCell = "";
            int col;

            Object Opt = Missing.Value;
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); //= new Excel.Application();
                                                                                                               //Declare Workbook
            Microsoft.Office.Interop.Excel.Workbook book;

            //Excel.Worksheet sheet;
            Microsoft.Office.Interop.Excel.Worksheet xlsSheet;
            Microsoft.Office.Interop.Excel.Range oRng;

            //Open Spreadsheet
            book = app.Workbooks.Open(strFileName, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt);

            //Take the first sheet
            xlsSheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Sheets[1];

            if (xlsSheet != null)
            {
                //Read the first cell
                oRng = (Microsoft.Office.Interop.Excel.Range)xlsSheet.Cells[1, 1];

                ColCount = xlsSheet.Columns.Count;

                RowCount = xlsSheet.Rows.Count;
            }

            for (int row = 0; row < RowCount; row++)
            {
                strCell = "";
                for (col = 0; col < ColCount; col++)
                {
                    oRng = (Microsoft.Office.Interop.Excel.Range)xlsSheet.Cells[row + 1, col + 1];
                    if (col < ColCount - 1)
                    {
                        strCell = strCell + oRng.Text.ToString() + strDelimiter;
                    }
                    else
                    {
                        strCell = strCell + oRng.Text.ToString();
                    }
                }
                //Write to text file
                sw.WriteLine(strCell);
            }

            Console.WriteLine(strCell);

            sw.Close();

            book.Close(false, false, Missing.Value);
        }