Need to add this to the imports section of SSIS:

Imports Microsoft.Office.Interop.Excel

'SSIS didn;t like SET commands, thus we updated to remove them...

' Create an Excel instance
   Dim oExcel
   Dim oWorkbook ' added this
   oExcel = CreateObject("Excel.Application") 

 ' Disable Excel UI elements
   oExcel.Visible = True
   oExcel.DisplayAlerts = False
   oExcel.AskToUpdateLinks = False
   oExcel.AlertBeforeOverwriting = False

   oWorkbook = oExcel.Workbooks.Open("absolute path to your file")
   oWorkbook.RefreshAll
   oWorkbook.Save

   oExcel.Quit
   oWorkbook = Nothing
   oExcel = Nothing

Need to add this to the imports section of SSIS:

Imports Microsoft.Office.Interop.Excel

'SSIS didn;t like SET commands, thus we updated to remove them...

' Create an Excel instance
   Dim oExcel
   Dim oWorkbook ' added this
   oExcel = CreateObject("Excel.Application") 

 ' Disable Excel UI elements
   oExcel.Visible = True
   oExcel.DisplayAlerts = False
   oExcel.AskToUpdateLinks = False
   oExcel.AlertBeforeOverwriting = False

   oWorkbook = oExcel.Workbooks.Open("absolute path to your file")
   oWorkbook.EnableConnections = true ' added!
   oWorkbook.RefreshAll
   oWorkbook.Save

   oExcel.Quit
   oWorkbook = Nothing
   oExcel = Nothing