Refresh Excel using SSIS script task is very slow

Vote count:

0

I am refreshing 6-7 pivots in a excel sheet calling a Script task in SSIS. I have to refresh 100 excel workbooks, looping them one by one. I have already created a code to refresh all the sheets of each workbook one by one.

To refresh a workbook it is taking 3 to 5 mins approx due to server connectivity. But the problem is, after around 30 to 40 files are refreshed, the task is going into a steady mode. I have seen that the for hours no file is refreshed and also package is in running mode.No failure notification.

Can you suggest what could be the issue. Is it due to memory overflow?? or anything else.

Dim oExcel

Set oExcel = CreateObject("Excel.Application")

oExcel.Visible = True

oExcel.DisplayAlerts = False

oExcel.AskToUpdateLinks = False

oExcel.AlertBeforeOverwriting = False

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

oWorkbook.RefreshAll

oWorkbook.Save

oExcel.Quit

Set oWorkbook = Nothing

Set oExcel = Nothing

Similar code used

asked 1 min ago

Nim Nagpal

11

Refresh Excel using SSIS script task is very slow