locked
Update Sharepoint from SQL? RRS feed

  • Question

  • Hello,

    I have a SQL Databases SQL Server 2008 and I would like to update an existing Sharepoint 2007 List with the data from SQL to populate the Sharepoint SQL Database and use only SP later on.

    How to do it?

    Thanks,
    Dom


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager
    • Moved by Mike Walsh FIN Tuesday, July 19, 2011 6:12 AM admin q (From:SharePoint - General Question and Answers and Discussion (pre-SharePoint 2010))
    Monday, July 18, 2011 10:34 PM

Answers

  • Sometimes even the easy things can be hard...This post assumes you have read the SharePoint List Adapter Readme and the MSDN arsource code and documented on MSDN Iticle..

    This example uses two Calendars; Calendar is the source and Calendar.MSDN is the destination http://msmvps.com/blogs/ivansanders/archive/2011/07/24/ssis-sharepoint-list-adapters.aspx. After Installation and Configuration of VS2008 you should be able to complete the following:

    1. Drag a SharePoint List Source & a SharePoint List Destination from your toolbox

    2. Fill in the Properties of the SharePoint List Source and drag the green connector to the Destination

    • SiteURL - The URL for the primary site on which the list is found. Do not include any other subfolders or list paths, or the location of an .asmxfile.
    • ListName - The name of the SharePoint list, as seen on the SharePoint list page.
    • ViewName - The name of the SharePoint list view from which you want to retrieve data. You can use a list view to pre-filter rows and eliminate the need to add dynamic CAML queries to your package.

    3. Next Map the Columns on the List Destination but do NOT Include ID if you want the Source to Create a Row on the Destination, Include an ID if you want the Source to Update the Destination..

    The rules are:

    • Create a Row (Modification Batch Type). A row is created on the server when you select this batch type and do NOT specify an ID value for a given row of data. Any columns that are not included receive the default values or a NULL value on the server.
    • Update a Row (Modification Batch Type). A row is updated on the server when you select this batch type and You MUST specify the ID of an existing row. Only the columns contained in the data flow are modified. If you specify this batch type, but the data flow does not contain any updated rows, an error is raised.
    • Delete a Row (Deletion Batch Type). A row is deleted on the server when you select this batch type and specify the ID of an existing row. If you specify this batch type, but the data flow does not contain any deleted rows, an error is raised.

    Execute the Task and if its green your good to go. However, the rules above mean that you will probably need more than one Data Flow to handle the different scenarios

    The MSDN Article By Kevin Idzi and the Readme should pretty much explain all you need to know so I have to wonder why I am explaining it again, for everyone who did not know the references…

     

    -Ivan


    Ivan Sanders My LinkedIn Profile, My Blog, @iasanders.
    • Marked as answer by Felyjos Monday, July 25, 2011 5:05 AM
    Monday, July 25, 2011 2:11 AM

All replies

  • HI,

    By using the SSIS SharePoint List Adapters on codeplex http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652

    MSDN

    Tutorial
    Extensive Validation: Validation for this component actively goes against the SharePoint Site to verify the properties are valid.
    Values from Expressions: This component supports external variables, which can be expressions, and can be attached to the source component to customize the query. Similar to the CommandText for the other Sql Components
    Linq: The Component has been written using Linq with .net 3.5 and shows how elements such as the metadata and columns can be combined to create a readable usage in a Linq format.
    Custom properties: The component keeps its configuration in custom properties on itself, inputs, and input columns.
    FAQ: http://sqlsrvintegrationsrv.codeplex.com/wikipage?title=SharePoint%20List%20Adapters&referringTitle=Documentation

     

    -Ivan


    Ivan Sanders My LinkedIn Profile, My Blog, @iasanders.
    Tuesday, July 19, 2011 4:56 AM
  • Hello,

    In progress but I have still an error:

    SSIS package "Package.dtsx" starting.
    
    Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
    
    Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
    
    Warning: 0x80049304 at Data Flow Task, SSIS.Pipeline: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console.
    
    Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning.
    
    Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is beginning.
    
    Information: 0x4004300C at Data Flow Task, SSIS.Pipeline: Execute phase is beginning.
    
    Information: 0x0 at Data Flow Task, SharePoint List Destination: Affected 0 records in list 'Servers Inventory SQL' at 'http://teams.com/server/ServerDBs/'. Elapsed time is 1638ms
    
    Error: 0x0 at Data Flow Task, SharePoint List Destination: Error on row ID="1": 0x81020016 - List item referred to in the request does not exist. 
    
    Error: 0xC0047062 at Data Flow Task, SharePoint List Destination [173]: Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.PipelineProcessException: Errors detected in this component - see SSIS Errors
    
     at Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListDestination.ProcessInput(Int32 inputID, PipelineBuffer buffer)
    
     at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)
    
    Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "SharePoint List Destination" (173) failed with error code 0x80131500 while processing input "Component Input" (181). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
    
    Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.
    
    Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "component "SharePoint List Destination" (173)" wrote 0 rows.
    
    Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.
    
    Task failed: Data Flow Task
    
    Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
    
    SSIS package "Package.dtsx" finished: Failure.
    
    

    the list Servers Inventory SQL exists in Sharepoint under http://teams.com/server/ServerDBs/default.aspx

    Why the package is not able to access it?

    If I try from a browser:

    http://teams.com/server/ServerDBs/default.aspx/Servers Inventory SQL it fails

    http://teams.com/server/ServerDBs/default.aspx/Lists/Servers Inventory SQL it works

    but if I place http://teams.com/server/ServerDBs/Lists in the SiteURL in the Advanced Editor for SharePoint List destination I am getting an error:

    Error at Data Flow Task [SharePoint List destination [173]] System.Service.Model.EndpointNotFoundException: There was no endpoint listening at http://teams.com/server/serverdbs/lists/_vti_bin/lists.asmx that could accept the message. This is often caused by an incorrect address or SOAP action. ...

    So I am in a loop .. any idea comments

    Thanks,
    DOm


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager
    Thursday, July 21, 2011 1:09 AM
  • HI,

    You can try the BDC functionality for this.

    You can refer this for more info:

    http://social.msdn.microsoft.com/Forums/en/sharepointdevelopment/thread/8dd925d9-1a46-451e-a3ba-9a97f399fb1b

    I hope this will help you out.

    Thanks,

    Rahul Rashu

    Thursday, July 21, 2011 6:13 AM
  • Hello,

    From the link I noticed:

    You can think of both the BDC and SSIS job for this scenario. If you want to get the data from SQL, then do some transformation and update the sharepoint list, then you can think of SSIS. Because SSIS would help you to define the orchestration steps like a workflow and do your custom processing of the data.

    If you are happy with just surfacing data from SQL to sharepoint list, then BDC would be the  best choice.

    It all depends on what you want to do


    So for it is SSIS which is the solution not BDC.

    So I need to fix SSIS...

    Thanks,

    Dom


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager
    Thursday, July 21, 2011 3:18 PM
  • Hello,

    Could it be an access issue?

    When i click on the link in the error 'http://teams.ad/server/ServerDBs/' it requests a login, so is it possible the package is not able to loggin and send out this error? or is it another issue?:

    Information: 0x0 at Data Flow Task, SharePoint List Destination: Affected 0 records in list 'Servers Inventory SQL' at 'http://teams.ad/server/ServerDBs/'. Elapsed time is 2769ms

    Error: 0x0 at Data Flow Task, SharePoint List Destination: Error on row ID="1": 0x81020016 - List item referred to in the request does not exist.

    Error: 0xC0047062 at Data Flow Task, SharePoint List Destination [173]: Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.PipelineProcessException: Errors detected in this component - see SSIS Errors

    at Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListDestination.ProcessInput(Int32 inputID, PipelineBuffer buffer)

    at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)

    Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "SharePoint List Destination" (173) failed with error code 0x80131500 while processing input "Component Input" (181). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

    Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.

    Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "component "SharePoint List Destination" (173)" wrote 0 rows.

    Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.

    Task failed: Data Flow Task

     

    If it is an access/login issue where to specify the login to be used?

    Thanks,

    DOm

     


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager
    Friday, July 22, 2011 1:16 AM
  • Hello,

    Anyway to set the login automatically in SSIS between the SQL where it is working so far and SharePoint where apparently the package is not able to find the site may because it could not login... is it possible?

    http://geekswithblogs.net/juanlarios/archive/2010/06/23/ssis-and-sharepoint---moving-sharepoint-content-to-sql-2008.aspx

    Apparently I need to add Microsoft.Sharepoint.dll as a reference but as I am developping my package on the SQL Server it is not present here... should i point it to the SharePoint server or should the development done on the Sharepoint server instead of the SQL Server...??

    I have two servers one SQL with the Database I would like to transfer to Sharepoint and a farm with two front-end, one index and two SQL for the Sharepoint Environment.

    Thanks,

    Dom


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

    Friday, July 22, 2011 2:05 PM
  • Hello,

    trying to rebuild everything from scratch and now in the Sharepoint List destination I have Input Column but not Column mappings, is a reference missing?

    A refresh and exit reload BIDS and it works again... Strange..

    Still having the same original issue not able to find the list on the site...

    Which rights and permissuionsthe account running the "execute package" task should have on the SharePoint Server? Windows? SQL? Server roles is set to public for now... on WSSxxx Databases is it sufficient? only the account sa is sysadmin on this environment, is it correct?

    Thanks,

    If i connect straight to SharePoint with my account I am able to create a list and update it, delete it... full control works... but any restrictions when doing it with the same account from SSIS?

    Dom


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager


    Friday, July 22, 2011 8:58 PM
  • Hello,

    I tried both ways:

    Creating the package with BIDS on the SQL server with the Data Flow to SharePoint.

    Creating the package with BIDS on the SharePoint Server with the Data Flow to the SQL Server.

    both are failing with the same issue not finding the LIST.... (S) as I treid even with several lists, existing, new, etc...

    Any idea?

    I created a new Package with SharePoint List Shource and SharePoint List Destination and the executrion failed both Data Flow are red... could not find the List !!!! but the output is:

    Information: 0x0 at Data Flow Task, SharePoint List Source: Loaded 1158 records from list 'Server Inventory' at 'http://teams.ad/Server/ServerDBs'. Elapsed time is 3219ms

    Information: 0x0 at Data Flow Task, SharePoint List Destination: Affected 0 records in list 'Servers Inventory' at 'http://teams.ad/server/ServerdBs'. Elapsed time is 818ms

    Error: 0x0 at Data Flow Task, SharePoint List Destination: Error on row ID="1": 0x81020016 - List item referred to in the request does not exist.

    which means SSIS is able to read the List ... Server Inventory but not to write to Servers Inventory...

     

    Thanks,

    Dom


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

    Saturday, July 23, 2011 12:22 AM
  • Hi

    This may not be intuitive but its asking for the View Name Like All Links or All Items NOT allitems.aspx. You have to populate either using advanced Settings or Properties of the source First, choose the columns you want for Output then set the Input Columns of your Destination..

     

    -Ivan


    Ivan Sanders My LinkedIn Profile, My Blog, @iasanders.
    Sunday, July 24, 2011 11:54 AM
  • Hi,

    I added the View Name All Items as I have in the Servers Inventory List on the site

    http://teams.ad/server/ServerDBs/Lists/Servers%20Inventory/AllItems.aspx

    but still erroring

    SSIS package "ServersInvPkg.dtsx" starting.

    Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.

    Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.

    Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning.

    Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is beginning.

    Information: 0x4004300C at Data Flow Task, SSIS.Pipeline: Execute phase is beginning.

    Information: 0x0 at Data Flow Task, SharePoint List Destination: Affected 0 records in list 'Servers Inventory' at 'http://teams.ad/server/ServerDBs'. Elapsed time is 3911ms

    Error: 0x0 at Data Flow Task, SharePoint List Destination: Error on row ID="1": 0x81020016 - List item referred to in the request does not exist.

    Error: 0xC0047062 at Data Flow Task, SharePoint List Destination [139]: Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.PipelineProcessException: Errors detected in this component - see SSIS Errors

    at Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListDestination.ProcessInput(Int32 inputID, PipelineBuffer buffer)

    at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)

    Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "SharePoint List Destination" (139) failed with error code 0x80131500 while processing input "Component Input" (147). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

    Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.

    Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "component "SharePoint List Destination" (139)" wrote 0 rows.

    Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.

    Task failed: Data Flow Task

    Warning: 0x80019002 at ServersInvPkg: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    SSIS package "ServersInvPkg.dtsx" finished: Failure.

    In progress...

    Interresting fact if I input "All Items" in the SiteListViewName I click ok it is fine and it will fails on excution.

    but if I input "Test1", a view I just created, or "AllItems" with no space between All and Items in the SiteListViewName I click ok it is failing in the Advanced Editor:

    - Error at Data Flow task [SharePoint List Destination]: Failed to get list data from SharePoint Web Service - Site http://teams,ad/server/ServerDBs/, List: Servers Inventory...

    it seems it is able to get the view "All Items" but maybe from another site/page?

    Which user is used to login the Destination SharePoint List? is it the windows logging? is it automatic? I have to loggin to the Sharepoint site anytime I launch Internet Explorer how the task do manage this?

    Any permissions on the SharePoint List site to allow SQL to create the items and or

    Source output columns: ID, Assetname, AssetType

    Destination input columns: ID, Assetname, AssetType

    but I noticed a mismatch on the Destination Column mapping Assetname is not linked to AssetName could it be an issue? so I changed <ignore> to assetname and it is linked to AssetName ... but still failing...

    Id is linked to ID and AssetType is linked to AssetType

    Thanks,


    Dom

    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager








    Sunday, July 24, 2011 9:36 PM
  • Sometimes even the easy things can be hard...This post assumes you have read the SharePoint List Adapter Readme and the MSDN arsource code and documented on MSDN Iticle..

    This example uses two Calendars; Calendar is the source and Calendar.MSDN is the destination http://msmvps.com/blogs/ivansanders/archive/2011/07/24/ssis-sharepoint-list-adapters.aspx. After Installation and Configuration of VS2008 you should be able to complete the following:

    1. Drag a SharePoint List Source & a SharePoint List Destination from your toolbox

    2. Fill in the Properties of the SharePoint List Source and drag the green connector to the Destination

    • SiteURL - The URL for the primary site on which the list is found. Do not include any other subfolders or list paths, or the location of an .asmxfile.
    • ListName - The name of the SharePoint list, as seen on the SharePoint list page.
    • ViewName - The name of the SharePoint list view from which you want to retrieve data. You can use a list view to pre-filter rows and eliminate the need to add dynamic CAML queries to your package.

    3. Next Map the Columns on the List Destination but do NOT Include ID if you want the Source to Create a Row on the Destination, Include an ID if you want the Source to Update the Destination..

    The rules are:

    • Create a Row (Modification Batch Type). A row is created on the server when you select this batch type and do NOT specify an ID value for a given row of data. Any columns that are not included receive the default values or a NULL value on the server.
    • Update a Row (Modification Batch Type). A row is updated on the server when you select this batch type and You MUST specify the ID of an existing row. Only the columns contained in the data flow are modified. If you specify this batch type, but the data flow does not contain any updated rows, an error is raised.
    • Delete a Row (Deletion Batch Type). A row is deleted on the server when you select this batch type and specify the ID of an existing row. If you specify this batch type, but the data flow does not contain any deleted rows, an error is raised.

    Execute the Task and if its green your good to go. However, the rules above mean that you will probably need more than one Data Flow to handle the different scenarios

    The MSDN Article By Kevin Idzi and the Readme should pretty much explain all you need to know so I have to wonder why I am explaining it again, for everyone who did not know the references…

     

    -Ivan


    Ivan Sanders My LinkedIn Profile, My Blog, @iasanders.
    • Marked as answer by Felyjos Monday, July 25, 2011 5:05 AM
    Monday, July 25, 2011 2:11 AM
  • Thanks again Ivan as I made the creation in test with two columns working as said earlier it is really not intuitive and not what iit has been sold for :) ...

    Three data flows explained as one it is really confusing...

    Now working on how to get all the columns populated identically as the origin... in the sharepoint list...

    Thanks

    Dom


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager
    Monday, July 25, 2011 5:06 AM