Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Cannot use excel sheet (on Sharepoint site) as SSIS destination.

Unanswered Cannot use excel sheet (on Sharepoint site) as SSIS destination.

  • Wednesday, February 20, 2013 3:07 AM
     
     
    Sign In to Vote
    0

    Hi,

    Here is the issue i am struggling with,

    I have to populate an excel sheet using SSIS, but the excel sheet is on Sharepoint Site.

    So, how do i configure the package or the excel connection manager so as to make the Excel document (sitting on Sharepoint site) as my destination.

    In the package what exactly happens is,

    1. The OLE DB source executes an SQL query from SQL Server.

    2. A data conversion task converts some fields' datatypes.

    3. Load data into an excel sheet on the Sharepoint site.

    OLEDB Source    ---------->   Data Conversion   ------------>   Excel sheet on Sharepoint site.

    I am good until data conversion, but i have no clue how to configure the third step above.

    I tried to give the link in the excel connection manager, but it doesn't work.

    Is there any other approach to fix this task/issue?

    Any help is really appreciated.

    Thank you.

     

     

All Replies

  • Wednesday, February 20, 2013 7:10 AM
    Moderator
     
     
    Sign In to Vote
    0
    How do you access the excel file? Via http or via a share/network drive?

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

     
  • Wednesday, February 20, 2013 2:52 PM
     
     
    Sign In to Vote
    0

    I approached in 2 different ways.

    1. I mapped the http link to a folder and used the file(excel sheet) in that folder as my connection to excel manager.

    2. I directly gave the http link to the excel connection manager.

    None of the above works.

    I can see the file on SharePoint, but using SSIS i am not able to write records directly into the excel sheet (sitting on SharePoint Site), nor provide that connection to the excel connection manager.

    Please help me in approaching this situation. Let me know if there is any other approach to accomplish this task.

     
  • Wednesday, February 20, 2013 2:56 PM
    Moderator
     
     
    Sign In to Vote
    0

    Did you have any error messages when trying that could help?

    If you have mapped the http link to a drive like s:\folder\subfolder\ are you able to open and save an excel file on that drive?

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

     
  • Wednesday, February 20, 2013 3:18 PM
     
     
    Sign In to Vote
    0

    i could open the files on the mapped folder.

    Its not the error but the warning " Failure creating file ", i am getting.

    But the file is already there on the link.

    I tried to create a table on the excel sheet connection manager to see if it works for me. After creating the table it asks me to pick the table from the list and where the created table never appears in the list, in fact no table shows up in the list(The tables structure ia already there on the excel sheet in my case). But that was just another trial which could not work.

     
  • Wednesday, February 20, 2013 3:22 PM
     
     
    Sign In to Vote
    0

    Delete the Excel destination and redrag try to set your DFT on empty excel and create a table on that excel then delete that....when you will try it would work with the correct excel, make sure use the same table name as your actual excel have..

    Thanks

    Sumit

     
  • Wednesday, February 20, 2013 3:30 PM
     
     
    Sign In to Vote
    0

    @SumitKumarDua:

    Apparently, no tables or views could be loaded with your process. Which is the scenario, i am having since the beginning.

    If the excel sheet is in the SharePoint site and i have to load the excel sheet using an SSIS package. Can someone help me how to configure the package?