none
SSIS Excel File Refresh.

    Question

  • Hello all, brand new to the forums and i need some help :-).

    So i am new to SSIS and trying to use it to update some Excel files on a sharepoint.  The script seems to be working but it does not save the file.  I think what is going on is the SSIS script is opening it in Read Only.  Because when i open the file from my computer i always have to hit the yellow button at the top to "Edit Workbook".  Can anyone tell me if there is a command i can use to scipt clicking that button?


    Yo.
    Monday, January 09, 2012 6:06 AM

Answers

  • As this works in BIDS but not management studio, I would guess it is one of the following:

    1: you don't actually have MSOffice on the server that the package has been deployed to

    2: permissions - it may be that you need to use alternative permissions to access the excel file but that the permissions do not have access to run the query in excel - how is that set up? windows authentication or a hardcoded username/password?


    Rgds Geoff
    • Marked as answer by Eileen Zhao Monday, January 23, 2012 2:38 AM
    Tuesday, January 17, 2012 9:44 PM

All replies

  • Hi,
    Try to check you file properties if it set to read only or not if read only change it.
    To write file you must use it as destination in your data flow.


    I hope this is helpful.
    Elmozamil Elamir

     

    MyBlog


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid
    http://elmozamil.blogspot.com
    Monday, January 09, 2012 6:41 AM
  • It is not set to read only.  If i click "Edit Workbook" i can immediatly start working on and saving the document.

    The document is also set as the destination in the data flow.  I can paste the code i have if u would like.

    Monday, January 09, 2012 7:26 AM
  • Here is my code....

    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;

    namespace ST_b8fb6c970baf48ff8e2a5ed94ff13584.csproj
    {
        [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
        public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        {

            #region VSTA generated code
            enum ScriptResults
            {
                Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
                Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
            };
            #endregion

            public void Main()
            {
                ExcelRefresh(@"\\AUSSMBSSOBI\Files\Public\Reports\APOS_Dashboard.xlsm");
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            private void ExcelRefresh(string FileName)
            {
                object NullValue = System.Reflection.Missing.Value;
                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                excelApp.DisplayAlerts = false;
                Microsoft.Office.Interop.Excel.Workbook Workbook = excelApp.Workbooks.Open(
                    FileName, NullValue, NullValue, NullValue, NullValue,
                    NullValue, NullValue, NullValue, NullValue, NullValue,
                    NullValue, NullValue, NullValue, NullValue, NullValue);
                Workbook.RefreshAll();
                Workbook.Save();
                Workbook.Close(false, FileName, null);
                excelApp.Quit();
                Workbook = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

            }
        }
    }

     

    Monday, January 09, 2012 7:33 AM
  • What operation is it you wish to perform? Can you please describe the task in hand in greater detail. There may be an alternate way to perform this task.
    Monday, January 09, 2012 11:08 AM
  • I want to open the file, refresh the pivot table, save the file, then close the file.
    Monday, January 09, 2012 2:13 PM
  • I want to open the file, refresh the pivot table, save the file, then close the file.

    You don't refresh the pivot. The pivot model is already created. The underlying data is updated. Perhaps, if you can elaborate more.
    Cheers, Raunak Jhawar | t:@raunakjhawar
    Monday, January 09, 2012 5:01 PM
  • Im not sure how i could elaborate any further.  This document is updated daily and is pulling from a SQL DB.  All i want to do is open this file, refresh the pivot data to pull in the new stuff and then save and close it.
    Monday, January 09, 2012 8:21 PM
  • Hi Fanatic1074,

    The feature of force refresh(if this is what you mean) is not available. Please see here

    Please vote the post as helpful, or mark as answer if it helps


    Cheers, Raunak Jhawar | t:@raunakjhawar
    Tuesday, January 10, 2012 6:57 AM
  • Ok nevermind i figured it out.  For anyone that wants to know, the issue was that it was saving the file before it was done refreshing.  I simply put a system wait command before saving the file.
    Wednesday, January 11, 2012 7:02 AM
  • Ok while i got the entire SSIS package to run in BIDS now, it will not run in Management studio. 

    The issue seems to be with the Script task that refreshes the Excel pivot data.  If i take out that sript task it runs fine. 

    I have tried setting up a proxy with several differnt sets of credentials to no resolution. 

    Anyone have any idea why the script task is causing my DTSX package not to complete in management studio?  It will just sit at processing the entire time.

    Monday, January 16, 2012 12:29 AM
  • Do you have MS Office installed or at least the dlls on the server that SSIS package exists on?

     

    If not, you cannot interact with MS Office files...


    Rgds Geoff
    Monday, January 16, 2012 1:56 AM
  • Yes sir i do.
    Monday, January 16, 2012 7:54 PM
  • Hello,

     

    Try this one. Works like charm. This will simply refrshe all sheets within your excel and save the file. The file will be ready with latest data to use in SSIS.

     

    http://jessicammoss.blogspot.com/2008/10/manipulating-excel-spreadsheets-in-ssis.html

     

    hope this helps


    Cheers, Ashish
    Tuesday, January 17, 2012 4:34 AM
  • Im not sure if you have noticed but i did fix the refreshing part but now the Package will not run in SQL Server 2008 Management Studio.  I tried to set it up as an Agent Job but it will not work with the Refresh script in, if i take out the Refresh script it will run fine. 

     

    I have already tried using a proxy with my credentials and the credentials used to run start the SQL Agent on the server itself.

     

    I have actually tried that script.  However i dont fully understand it,  I try using that and i keep getting underlining red squiglies. 

    Please forgive me because i am BRAND new to SSIS.  Im sorry if it seems like im an idiot but this is not my area of expertise.

    Do i need to add more references?

    Also, what the difference between "Imports" vs "Using"?  If i put Imports then it seems not to work. 

    Tuesday, January 17, 2012 6:38 AM
  • As this works in BIDS but not management studio, I would guess it is one of the following:

    1: you don't actually have MSOffice on the server that the package has been deployed to

    2: permissions - it may be that you need to use alternative permissions to access the excel file but that the permissions do not have access to run the query in excel - how is that set up? windows authentication or a hardcoded username/password?


    Rgds Geoff
    • Marked as answer by Eileen Zhao Monday, January 23, 2012 2:38 AM
    Tuesday, January 17, 2012 9:44 PM
  • Hi,

    Did you get that working when scheduling this SSIS package in SQL Server Agent ?

    Thanks for the ansewer.

    Amine

    Thursday, March 01, 2012 2:39 PM