Hello, The below peice of code works fine when am executing it as a stand alone .net app. But when I copy paste the same code into a script task (SSIS) , The excel refreshing doesn't happen. public class RefreshPivotTables { public string DestinationFileName { get; set; } public string SourceTemplate { get; set; } public string ReprotName { get; set; } public RefreshPivotTables(string destinationFileName, string sourceTemplate, string reprotName) { this.DestinationFileName = destinationFileName; this.SourceTemplate = sourceTemplate; this.ReprotName = reprotName; } public void Refresh() { Microsoft.Office.Interop.Excel.Application xlApp = null; xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook wb = null; SqlDataReader dr; System.Data.DataTable dt = new System.Data.DataTable(); try { // Returns a data reader dr = this.ExecuteStoredProc(this.ReprotName); if (dr != null) { wb = xlApp.Workbooks.Open(this.SourceTemplate, 0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, false, 1, 0); dt.Load(dr); foreach (Microsoft.Office.Interop.Excel.Worksheet wss in wb.Worksheets) { this.RefreshAllQueryTables(wss, dt); } // Save the copy of the template to a different location // after refreshing the data. wb.SaveCopyAs(this.DestinationFileName); } } catch (Exception ex) { throw ex; } finally { xlApp = null; wb.Close(false, false, false); wb = null; } } private void RefreshAllQueryTables(Microsoft.Office.Interop.Excel.Worksheet wss, System.Data.DataTable dt) { int cnt; DataRow[] drFilter; string mdxQuery; int lstObjsCount = wss.ListObjects.Count; try { for (cnt = 1; cnt <= lstObjsCount; cnt++) { drFilter = dt.Select("(TabName='" + wss.Name.ToString() + "' AND PivotName='Pivot" + cnt.ToString() + "')"); mdxQuery = drFilter[0]["MdxQuery"].ToString(); wss.ListObjects.get_Item(cnt).QueryTable.CommandText = mdxQuery; // Refreshes the list obejct with the new MDX Query. wss.ListObjects.get_Item(cnt).QueryTable.Refresh(true); } } catch (Exception ex) { throw ex; } finally { } } private SqlDataReader ExecuteStoredProc(string reportName) { SqlDataReader dr; try { SqlConnection cnn = new SqlConnection(myConStr); SqlCommand cmd = new SqlCommand(); cnn.Open(); cmd.Connection = cnn; cmd.CommandText = "GET_PIVOT_MDXQUERIES"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@Report_Name", SqlDbType.VarChar).Value = reportName; dr = cmd.ExecuteReader(); return dr; } catch (Exception ex) { throw ex; } } } Regards Man

When you say "refresh does not happen": do you mean you do not get errors and the task appears finished in green color?Arthur My Blog

Hi Man, Please try to add MessageBox in the scripts to check what happen in the code. Thanks, Jin ChenJin Chen - MSFT

Hello Arthurz, It just copies the excel template from the source folder to the destination containing the same data. i.e. even if am changing the MDX query during the runtime. Now even when using the same code .net ( not thru SSIS), I get an exception "Call was rejected by callee.(Exception from HRESULT:0x80010001(RPC_E_CALL_REJECTED)) " . Iam not getting this error whne i execute the .Net app for the first time. But keeps on getting this error on subsequent execution. The below line gives the exception. The funniest part is that , I don't get this exception when i execute the app after restart , but do get this on subsequent execution. // Refreshes the list obejct with the new MDX Query. wss.ListObjects.get_Item(cnt).QueryTable.Refresh(true); Regards Man

Hi Jin Chen, I tried that. The MDX query is getting retrieved correctly, message box confirms that. Regards Man

Hi Man, So, the code was executed correctly in the SSIS package? If I am right, we can isolate the issue to be a code issue regarding refreshing a querytable using Excel Primary Interop Assembly. I am not aware of Excel Primary Interop Assembly, but I would also like to suggest you that: Check in the QueryTable is existing before calling the refresh method. Not very sure, but from the Books Online, the refresh requires a Object parameter "BackgroundQuery" instead of a boolean value. You can re-post the question in http://social.msdn.microsoft.com/Forums/en-US/category/officedev to get more help about the code issue. Thanks, Jin ChenJin Chen - MSFT

Hello All, This is solved. The script component uses the .net framework 2.0 by default. It working when I changed it to 3.5. Now, regarding the HRESULT:0x80010001(RPC_E_CALL_REJECTED) exception, this error stopped when i used false instead of true. // Refreshes the list obejct with the new MDX Query. wss.ListObjects.get_Item(cnt).QueryTable.Refresh(false); Regards Man