Import SharePoint Lists Into SQL Server 2005

  • Hello,

    We are trying to download SharePoint (Moss 2007) list information into SQL Server 2005 using SSIS. The Web Service Task will not recognize the wsdl so we are stuck with trying a work around. We have created a dll to get around directly tapping the web service, but we can't seem to import the data into a SSIS variable and then into a sql table. Does anyone know how to create a script task to do this? I'm new to VB.Net so detailed information is very much appreciated.

    Thanks!

    telaan

  • See my post SSIS: Calling SharePoint web services from the data flow which outlines how to get information from SharePoint and use it in the data flow.

  • I would also like to know of a process to import a list into SQL. The post you referred the reader to previously didn't address this task, but rather made a suggestion for something else. I'm not interested in something else....

    Thanks!

    Dude

  • Actually, if you bothered to look at the code, you would see that it gets SharePoint list data.

  • It does not address the task, but rather provides a different solution. Although I did find a solution that worked rather well.... I simply imported the list into Access, and then used the SQL Wizard to import the data from Access directly into SQL.

    Fortunately with the list I was working with, it was previously in access (I wasn't the one who turned it into a list), and the conversion was easily done without any data corruption.

    It's apparent you can't reply to a post without insulting others and playing badly; you would be better served lurking in a different forum.

    Dude

  • Nice! the trick is figuring out how to talk to sharepoint. I've filed away your code for future reference. I also liked the reference to the C# VB converter!

    Thanks

    bill

  • If you can use third-party solutions, check the commercial CozyRoc SharePoint integration. These are the relevant components:

    * SharePoint Source SSIS Data Flow Component

    * SharePoint Destination SSIS Data Flow Component

    * SharePoint SSIS Connection Manager

    Batch insert and update is also supported.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • ;-);-);-)

    IT is surely rsing at magnificent pace....

    Got to get my boots geared up.....

    Raunak J

  • Best way to load SharePoint Lists into SQL Server table is to use codeplex solution SharePointListAdaptersSetup.msi

    Click here to downloaded this.

    After running this msi, two SSIS Data Flow controls SharePoint List Source and SharePoint List Destination will be installed.

    For the first time user, You have to open any package --> goto Data Flow tab --> right click on Data Flow Sources --> select Chose Items... --> Click on SSIS Data Flow Items --> check both SharePoint List Source and SharePoint List Destination

    Pre-requisites: .NET Framework 3.5 or above

  • Hari.Sharma (6/11/2010)


    Best way to load SharePoint Lists into SQL Server table is to use codeplex solution SharePointListAdaptersSetup.msi

    Click here to downloaded this.

    After running this msi, two SSIS Data Flow controls SharePoint List Source and SharePoint List Destination will be installed.

    For the first time user, You have to open any package --> goto Data Flow tab --> right click on Data Flow Sources --> select Chose Items... --> Click on SSIS Data Flow Items --> check both SharePoint List Source and SharePoint List Destination

    Pre-requisites: .NET Framework 3.5 or above

    How do you know this is the best if you haven't tried other solutions? Have you tried CozyRoc's solution?

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • How do you know this is the best if you haven't tried other solutions? Have you tried CozyRoc's solution?

    There is reason why codeplex is best solution: We need to specify only SiteUrl and List Name in the SharePoint List Source. Then it automatically displays all the columns. We just need to map the columns with Destination Data Source.

    I am just wondering what else a developer need?

  • Hari.Sharma (6/11/2010)


    How do you know this is the best if you haven't tried other solutions? Have you tried CozyRoc's solution?

    There is reason why codeplex is best solution: We need to specify only SiteUrl and List Name in the SharePoint List Source. Then it automatically displays all the columns. We just need to map the columns with Destination Data Source.

    I am just wondering what else a developer need?

    Before we decided to implement CozyRoc's solution, we have inspected all existing solutions on the market (including the codeplex). CozyRoc provides these extra features:

    - connection manager with support for proxy settings. I know the codeplex solution has recently implemented support for connection manager, but that wasn't the case for the past 2 years.

    - List, View can be selected from drop down.

    - provides visual CAML query editor.

    - provides common installation for both SQL 2005 and 2008.

    - provides installation for 32bit and 64bit.

    - provides quality commercial support.

    - based on common runtime, which is shared by all CozyRoc's components. This runtime has been enhanced and developed for the past 4 years and brings advanced "already tested" features. Also it simplifies the creation of new components tremendously.

    Now there are different nuances when you a solution is the best. The codeplex solution is the best "free solution", but not necessarily the best "in general". And I'm not saying "CozyRoc is the best". But I definitely stand behind my words "it is better" solution.

    p.s.

    You don't need a license to develop with the CozyRoc component(s). You need a license if you want to execute packages under SQL Job Agent.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • OK. CozyRoc is best solution 🙂 But I didn't mentioned codeplex as a best solution against CozyRoc since I never tried. I wrote in general way.

    I am sorry if you felt otherwise.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply