How to create .odc file which can be utilized in Excel Services


  • How to create .odc file which can be utilized in Excel Services

    Issue is: In my Excel File I am not seeing the dialog box for creating the .odc file
    "Save Data Connection File and Finish"
     (under Data Connection Wizard when launched using
     "From Other Sources" --> button in Excel 2007 ribbon

    I had completed following steps for Excel Services setting and SSO configuration in SharePoint Server
    Excel Services Settings
    1. Trusted data providers - setting (Provider ID: TableConn, Provider type: ODBC)
    2. Trusted File location configured (Allow External data - Trusted data connection libraries and enbedded)
    3. Edit Excel Services settings - Unattended account configured (SQL Table access account)
    4. SSO Services enable on Excel Services server
    5. SSO configuration configured (This SSO authentication is working in my site using SharePoint Designer - I can see my table columns and pull data)
    6. Data Connection Library (created: when I created my Report Center Site)
    7. Added My DCL to Trusted List (under Trusted data connection libraries)
    8. Exposing the data connection library in the Office client - completed (Publishing links to Office client application under User Profiles and My Sites)
    9. MySite is enabled in the Farm

    10. Setting a registry key to surface a data connection library in the client - configured on the client machine
          --> not sure if this needs to be set on WFE, Excel Services machine

    Not sure what went wrong

    Current Desktop config: Excel 2002 and Excel 2007 coexist in separate folder structure

    Please help to create the .odc file

    Thank you
    Thursday, January 21, 2010 9:49 PM

All replies

  • Once you create your connection and you are using it in your Excel 2007 file then you can click on the Properties button in the Connections section of the Ribbon.  Once you have done this click on the Definition tab and you will see the button to 'Export Connection File...' button.  Click on that and you can enter in the SharePoint URL to your Data Connection Library and save the odc file to SharePoint.

    Here is a video that you can watch that will walk you through all of the steps to configure the service in SharePoint and also save a file and work with it in SharePoint.

    SharePoint Server 2007 Demo: Enable Excel Services

    You will want to approve the connection once you have saved it up to the DCL in SharePoint.

    • Edited by Mike Walsh FIN Friday, February 12, 2010 4:31 AM sig removed - flash
    Friday, January 22, 2010 2:12 AM
  • Dan

    Thank you for providing the video cast for confifuring the excel services and DCL Library and .odc file upload

    My issue is:
    When I am creating the DCL from my Report Center Site, I am running into 2 issues
    I don't get the following option
    Document Library option (where I can choose the Excel 2007 document type)

    Only fields comes on the new form are below

    Name and Description
    Data Connection Version History

    Not avalaible:
    Document Library :

    When I am creating the data connection file .odc file
    I don't see any Workbook Connection in the data conection wizard -
    -->  so I click Add - to create a connection - Data Connection Wizard starts

    Data Connection Wizard
    Server Name: provided the SQL Instance where my data table reside
    Log on credentials: I can't provide the right credentials as the Windows ID I l am logged in as is not permissioned to access the database table, but another windows ID has been permissioned -> this windows ID I want to utilize (I don't see any option to provide other Windows ID - when creating the .odc file in data connection wizard screen.

    Please help to create the .odc file in excel so I can upload it in trusted DCL 

    Thank you

    Wednesday, January 27, 2010 11:46 PM
  • When you create the Document Library that you will use for your Trusted Location to store your Excel workbooks the template that you can select for Excel 2007 would be 'Microsoft Office Excel Spreadsheet'.  Once you have created this you will see if you go into the Advanced Settings of the Document Library that the template being referenced will be '<Document Library Name>/Forms/template.xlsx'.

    As far as the Authentication goes the three options you have with Excel Services is Integrated Windows Authentication (current user), SSO (Single Sign-On), or None.  None would leverage the connection string within the ODC that you have stored and utilized the Unattended Service account.

    For more information on your options and configurations take a look at the following documentation references:

    • Edited by Mike Walsh FIN Friday, February 12, 2010 4:31 AM sig removed - flash
    Thursday, January 28, 2010 11:33 AM