SOAP Data Model
The CData SSIS Components for Microsoft SharePoint models Microsoft SharePoint entities in relational Tables, Views, and Stored Procedures. The table definitions are dynamically obtained based on your Microsoft SharePoint site. Any changes you make, such as adding a custom field or changing a field's data type, are automatically reflected when you connect.
Customizing the Data Model
The component sets defaults to facilitate the maximum number of integrations; however, the following connection properties allow a greater granularity of customization, which is useful in advanced integrations:
- CalculatedDataType: The data type to be used for calculated fields.
- CreateIDColumns: Indicates whether or not to create supplemental Id columns for SharePoint columns that use values from information stored in other Lists.
- FolderOption: An option to determine how to display folders in results. Enter either FilesOnly, FilesAndFolders, Recursive, or RecursiveAll.
- PseudoColumns: Indicates whether to report pseudo columns as columns in the table metadata.
Tables
Tables describes the available tables.The component can expose custom lists from Microsoft SharePoint that are not mentioned in the Tables. The data model illustrates a sample of what your SharePoint site might look like. The actual data model will be obtained dynamically based on your user credentials and SharePoint site.
Views
Typically, entities that cannot be modified are represented as Views, or read-only tables. You can also access custom views of a list as relational views.
To get data from a custom view of a list, you can set the ViewID pseudo column in the WHERE clause.
SELECT * FROM ListName WHERE ViewID='ID of the view'You can get the ID of the view from the Views list. You must specify the List pseudo column to get a list of views for that list. For instance:
SELECT * FROM Views WHERE List ='ListName'