I'm trying to get data from WSS lists by web services. I think there are two ways:
- using SSIS standard components (Web Service Task) and
- creating a custom source adapter.
My problem with the Web Service Task is, that the WSS method GetListItems has some non-standard parameters (see part from wsdl-file below). Thus, after selecting the method in the editor an clicking on "Ok", I get the error message "The selected Web method contains unsupported arguments". I think the problem is the missing type-attribute.
is there any example how to extract values from a SharePoint list? I searched in Google but I only found something about that someone failed using the web service task for it... If possible I would like to get around any custom .net code (Scripts would be OK), but if I can't get around that, I have to follow that path...
I`ve created a table in a list that is grouped by years. Result is a Report, that consists of several years (List grouped by years, each containing the table with the data of the year).
Now I want to aggregate some numbers of the table on each page (year) of the list.
The sum()-function - doesn't matter which scpoe I use - always aggregates only the values of the actual list (Year), not considering the years before.
I'm writing a Data Processing Extension for SSRS 2005 that allows you to use a WSS 3.0 List as a dataset. This works for one list per dataset. Now I am trying to extend the extension to allow multiple lists in a dataset because I want to join lists on a common field (INNER JOIN). The problem is that I can only return one DataTable to the Report Server with the DataReader. Does anyone know if it is possible to return multiple DataTables (without the join)?
I'm a student, working on this project. I know Visual Basic.net but only started last month with Data.
I need to do data transfer from three tables in SQL server 2005 to the single Excel sheet as a report. Even If I create a Lists or named range, I couldn't able to transfer the data into the Lists. The idea is, whenever the data is transfered into the Lists, the three Lists (or Range) having data should grow separately.
Currently the data is transfered out of the Lists.
I setup this package to import data from a Sharepoint list to a SQL Server data table. The primary key of my SQL table is mapped to the Title column of my Sharepoint list. There is a possibility that duplicate values will be entered in the Title field of the Sharepoint list. So when importing data into my table via SSIS, my package always error-out when there it comes across duplicate values. how you others have managed data integrity when importing from a Sharepoint list with the Title column being mapped to the primary key of a table.
I get the following error when I try to navigate to a report/model/data source, stored in a Sharepoint Document Library using a Sharepoint URL based on an Extranet or Intranet zone - but it works OK using the url for the Default zone:
System.Web.Services.Protocols.SoapException: The specified path refers to a SharePoint zone that is not supported. The default zone path must be used. ---> Microsoft.ReportingServices.Diagnostics.Utilities.SecurityZoneNotSupportedException: The specified path refers to a SharePoint zone that is not supported. The default zone path must be used.
Is this a limitation of the SQLRS-WSS3 integration or is there a workaround?
Does anyone know the location of Microsoft.ReportingServices.SharePoint.UI.WebParts.dll after you install the web part? I am wondering if I can inherit from this web part to wrap some other functionality into it. I know that i see it in the GAC buyt can't seem to find it so I can look at the assembly using reflector.
Also, how easy is it to pass in parameters to the Report Viewer?
Since I wasn't getting any replies with answers or suggestions to my initial posting, I figured I would continue to list the problems and solutions I've encountered while trying to setup and utilize SharePoint integration mode, and read data from SharePoint with SSRS reports (developed in VS2005). Hopefully this is helpful to anyone that is also trying to learn all this stuff and figure it out.
(Single server - W2K3R2_SP2 as: DC / SQL2005 Standard / MOSS2007 Enterprise - see the bottom of this posting for the steps I took to set that up)
Note: After I get this to work, I intend to post my experiences for setting up SharePoint Integration with SQL and SP on separate boxes.
1. In the new reporting section under SharePoint application management in the Central Admin Console, the Grant database AND Set Server Defaults work when using the domain admin account, but not when using the SQLSPS service account I used to setup both SQL and SharePoint. Why can't I use the service account? Is the domain admin account now going to be used as a service account for accessing the sharepoint database?
This question remains unanswered as of yet.
2. Reporting Services configuration tool shows Sharepoint integration as red x when I create a new database in integration mode. (the service account SQLSPS and the Server object are members of the group WSS_WPG). If I switch back to native mode (the original reportserver database), the red x goes to a blue exclamation instead. Using Service Credentials or Windows credentials (as either the service account SQLSPS or as domain admin) doesn't change anything.
RESOLVED by using a domain user account for the ReportServer WebApp. Even though everything is installed on the same machine, it seems using the default of NETWORK SERVICE wasn't acceptable. Possibly because SharePoint is configured to allow additional machines in the farm?
3. http://servername:8080/reportserver will display Report Server information as it did before the integration on port 80, but http://servername/reportserver doesn't display anything through SharePoint.
Resolved. I needed to define a repository before I could view it (go figure). When I created a sharepoint site based on a report center template, created a data connection library and report library, and then referenced that by http://servername/sites/myReportCenterSite/myReportLibrary, I was able to see the equivalent of http://servername/reports (which is now disabled due to integration mode).
4. Unable to deploy to new report center sharepoint site I mention above using Visual Studio. I kept getting "A connection could not be made to the report server http://servername/sites/ReportCenter. Server was unable to process request. The request failed with HTTP status 503: Service unavailable (System.Web.Services). In Visual Studio, the project properties were: TargetDataSourceFolder = http://servername/sites/myReportCenterSite/myDataConnectionLibrary, TargetReportFolder = http://servername/sites/myReportCenterSite/myReportLibrary, TargetServerURL = http://servername/sites/myReportCenterSite/
Found these errors in the event logs on the server.
Event Type: Warning Event Source: W3SVC Event ID: 1021 Description: The identity of application pool, 'ReportServer' is invalid. If it remains invalid when the first request for the application pool is processed, the application pool will be disabled. The data field contains the error number.
Event Type: Warning Event Source: W3SVC Event ID: 1057 Description: The identity of application pool 'ReportServer' is invalid, so the World Wide Web Publishing Service can not create a worker process to serve the application pool. Therefore, the application pool has been disabled.
Event Type: Error Event Source: W3SVC Event ID: 1059 Description: A failure was encountered while launching the process serving application pool 'ReportServer'. The application pool has been disabled.
RESOLVED the issue by changing the Application Pool used by reporting services from ReportServer to DefaultAppPool and changing the account DefaultAppPool used from NETWORK SERVICE to my service account SQLSPS. I have no idea why ReportServer is invalid, it was set to use the service account SQLSPS the same as DefaultAppPool is. Perhaps the reporting services add-on makes a change to invalidate this?
5. Attempting to read a SharePoint list using a SSRS report. This should be fun.
First, created a new custom list in the ReportCenter SharePoint sites I've been using and called it myCustomList. I added one item with a title of myCustomListItem1. I don't know if I need to do more than that for a simple test of reporting or not yet.
Second, configured new shared data source. Name = myCustomList1DataSource, type=XML, Connection String = http://servername/_vti_bin/lists.asmx (this URL is viewable in IE), Using Windows authenticaion.
Third, in VS2005 on a workstation in the domain, create new report item (report1.rdl). New dataset for report; Name=Report1DataSet, Data Source=myCustomList1DataSource (Shared), Command type = text, Query String = [See Queries and associated errors below]:
Initial query obtained from: Connecting SQL Reporting Services to a SharePoint List (David Wise) http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx
Note: Queries when executing the dataset (which errors out) and when executing a query in CAML Viewer for the same information (which does work just fine) both initially return 401.1 and 401.2 errors (as viewed in Fiddler). Since the CAML query works, I have not tracked this down yet.
Parameter; Name => listName, Value => =myCustomList
Was NOT prompted to define the query parameters
- Gets this error: "An error occurred while setting the command text property of the data extension command. The XmlDP query is invalid. (Microsoft.ReportingServices.DataExtensions)."
- Advanced information from the error => 'http' is an unexpected token. The expected token is '"' or '''. Line 2, position 19. (System.Xml)
B. Changed: Placed the namespace URL in quotes (also tried with and without / in namespace after /soap)
Parameter; Name => listName, Value => =myCustomList (also tried Value => myCustomList) (no equal sign)
Clicked "!" to execute, and got prompted to define the query parameters Entered Name => listName, Value => myCustomList
- Gets this error: "failed to execute web request for the specified URL (Microsoft.ReportingServices.DataExtensions)."
- Advanced information: <faultstring>Server did not recognize the value of HTTP Header SOAPAction: http://schemas.microsoft.com/sharepoint/soap/GetListItems/.</faultstring>
C. Try the query string that seems to work for Stramit CAML Viewer to read myCustomList
Also tried <Query xmlns="http://schemas.microsoft.com/sharepoint/soap/"> </Query>
And tried entering no query...
Parameter; Name => listName, Value => =myCustomList (also tried Value => myCustomList) (no equal sign)
Clicked "!" to execute, and got prompted to define the query parameters Entered Name => listName, Value => myCustomList
- Gets this error: "failed to execute web request for the specified URL (Microsoft.ReportingServices.DataExtensions)."
- Advanced information: <soap:Fault><soap:Code><soap:Value>soap:Receiver</soap:Value></soap:Code><soap:Reason><soap:Text xml:lang="en">Server was unable to process request. ---> Data at the root level is invalid. Line 1, position 1.</soap:Text></soap:Reason><soap: Detail /></soap:Fault>
RESOLVED. Went back to the query used in B above, and mysteriously it works now. This could be the "funny things happen" symptom I've been reading about. We'll see if it continues to function.
Here's what I've learned so far on Issue #5:
1. XML is confusing. XmlDP is even more confusing...whatever that is. Apparently it's new to/with Reporting Services in SharePoint integration mode? 2. There is far too little documentation regarding XmlDP and querying SharePoint lists from SQL Reporting Services. 3. According to http://msdn2.microsoft.com/en-us/library/ms159741.aspx: You must use the generic query designer to create the query. The query is not analyzed to identify parameters; therefore you must create parameters through the Parameter tab on the Dataset dialog box.
Tried this query:
<Query> <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction> <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems" /> <Parameters> <Parameter Name="listName"> <DefaultValue>myCustomReport2</DefaultValue> </Parameter> <OrderBy> <FieldRef name="ows_Date" /> </OrderBy> </Parameters> </Query> I was still prompted for the listName and the list returned with ows_Date still out of order. So maybe that proves this statement is true? <translation: why make it convenient when it's already [somewhat] functional?> 4. When building the dataset, anything I place in the "Query string" box has to be enclosed in <query></query> tags or it errors out. I am trying to understand how to translate the CAML viewer query (which does work but isn't wrapped in query tags) into something that fits between query tags, and it hasn't gone well so far. <translation: To be at one with your reports Grasshopper, you must understand xml> 5. According to http://srv1/sites/ReportCenter/_vti_bin/lists.asmx, "GetListItems" operation is supported <translation: you're getting warmer...> 6. According to the soap output of my site http://srv1/sites/ReportCenter/_vti_bin/lists.asmx?op=GetListItems, I am only allowed to specify the following parameters: listName, viewName, query, viewFields, rowLimit, queryOptions and webID. <translation: that and $3.00 will get you a cup of coffee> 7. According to http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx, only listName, viewName and rowLimit parameters are usable(?) (query and queryOptions may not be working properly with SSRS). <translation: that kind of sucks> 8. When this started mysteriously working again, I tested using the "/" at the end of the namespace (wouldn't work without) and the "=" sign in front of the parameter value as defined on the parameter tab of the dataset (turns out to be optional).
As long as I can query SharePoint and get my list information back, I'm going to consider this issue closed and move on to actually using this information in a report. I'm sure that won't work right either, but why stop now?!
6. Ok, now that I can read SharePoint list data with SSRS, I need to try and actually do something with it.
A. Begin by setting up new data to report on
1. Create a new custom list (myCustomList2) and add a test record. This list will simulate the data we intend to submit to SharePoint using InfoPath, so it has a number of custom/created columns in it
2. Modify the working information from #5 above; add a new shared data source (myCustomList2DataSource), tell the dataset to use the new shared data source, change the information set in the parameter tab (Name => listName, Value => =myCustomList2) and test the dataset. Lucky me, it actually returned the record I entered on the first try.
B. Begin to build the report with the dataset returned
1. So, as I'm feeling good that it's actually reading the list in Step A without me threatening to destroy the machine, I look to the left in the Dataset results navigation pane in VS2005 and realize there are NO fields for me to choose to report on. 2. At this point, I have no clue what to do about getting this XML data into a report. Not that I have a large clue about SSRS otherwise, but I have at least been able to build SSRS reports when using a SQL database as the datasource. Fyi, http://msdn2.microsoft.com/en-us/library/ms159741.aspx states: "Reports can use data from XML documents and Web services, or embed XML in the query. There is no built-in support for retrieving XML documents from a SQL Server database.", in case anyone is trying to do that....
RESOLVED. Since the fields were not appearing automatically, I had started to add new fields to the dataset manually (if it doesn't work, beat it with a hammer, right?). I entered half a dozen or so fields, each one with the exact name as displayed in the query results, and then decided it would be fun to hit the refresh fields icon. As soon as I did that, all the fields shown in the query immediately appeared in the dataset and I was able to add them to a report. I don't know if I hit the refresh fields when working in issue #5, so I can't say if they are there now because of that, or because of adding some fields manually first, a combination of the two, or if it's just one of those mystery items I'm reading about with sharepoint reporting.
7. Rows are being returned even though they have NULL values (so far anyway), and most data is returning nicely for the bulk of the columns I created in the list I'm reporting on. Here are the current issues:
A. I have one column in my SharePoint list that is multi-lined text. If the text type is either rich text or enhanced rich text, the value returned in the query is formatted like this: "<div class=ExternalClassyadayada<div>My notes display here". If I set the column to be plain text, this doesn't happen. I will need to figure out how to filter that for my report.
RESOLVED. David Wise had some code listed at http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx that addressed this issue somewhat. I modified it to match my circumstances. Disclaimer: I'm not a programmer, so use this at your own risk.
function ExtractMultiLineText(myField as string) as string dim strBegin as string dim strEnd as string dim myBegin as integer dim myEnd as integer dim myTextLength as integer strBegin = "<div>" strEnd = "</div>" if myField = nothing then return "" if myField = "" then return "" myBegin = Instr(myField, strBegin) + Len(strBegin) myEnd = Instr(myField, strEnd) myTextLength = myEnd - myBegin
if myBegin < 1 then return myField return mid(myField, myBegin, myTextLength) end function
B. I have two columns in the SharePoint list that are set as number values with 0 decimal places. When I query those however in SSRS, they return with the number plus like 12 zeroes behind the decimal. Not a major issue, but I'll put it on the list anyway.
RESOLVED. Used the Int() function; =Int(Fields!ows_myProblemNumber)
C. If I go to "/_vti_bin/lists.asmx?op=GetListItems", it appears from the sample soap output that I am allowed to ask GetListItems for is: listName, viewName, query, viewFields, rowLimit, queryOptions and webID. As I mentioned in issue #5, according to http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx, only listName, viewName and rowLimit parameters are usable(?) and query and queryOptions may not be working properly with SSRS.
So, if I can't put anything other than this in my query string;
<Query> <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems"/> <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction> </Query> and I only have a couple parameters to pass on top of that, then it appears to me that my options for filtering the amount of data returned from the SharePoint list is to figure out a way to do it with expressions in the parameters or in the report itself. Am I missing something? Is this really all I get to work with out-of-the-box?
More to come...
================================== Installation steps taken to build the server VM: ==================================
(Using VirtualPC 2007) 1. Build OS (2003R2SP2) and promote to DC
1a. Create domain user service account SQLSPS 2. Prep for SQL
3a. Verify SSRS works 3b. RS config tool shows SP integration as blue exclamation 3c. Use service account SQLSPS for anything requiring Windows credentials 4. Install SQL2005 SP2
4a. Verify SSRS works 4b. RS config tool shows SP integration as blue exclamation 5. Prep for SharePoint
5a. Install .NET 3.0 6. Install MOSS2007 Enterprise and configure
6a. Webapps, SSP and anything else use svc acct SQLSPS 6b. Move Default website to port 8080 and re-activate (SharePoint stopped the site) 6c. Verify SSRS works on 8080 7. Install Reporting Services Add-in 8. Create new RS database in integration mode (with service credentials)
8a. RS config tool shows SP integration as red x 9. SP central admin /application management / manage integration settings
9a. reports server = http://srv1:8080/reportserver (since default port has been moved to 8080) 10. SP central admin /application management/grant database access
10a. using the FQDN, confirm servername and default instance - click OK 10b. Prompted for an account to use to retrieve information; svc acct SQLSPS did not work, domain admin did though 11. SP central admin / application management/set server defaults
11a. Accepted all defaults 12. Verify reportserver answers at http://srv1:8080/reportserver (via reportserver AppPool and DefaultAppPool) 13. Changed the application pool identity for the ReportServer AppPool from Network Service to the domain user service account I used for all SQL and SharePoint configurations (SQLSPS) and rebooted. The red x for sharepoint integration in the RS configuration tool is now a green checkmark.
hi, I am not sure whether this is the place for this post. I am developing a Vb.net search functionality which is accessing data from the SQL SERVER sharepoint portal database. I have troubles in connecting to the particular server and retrieving each table from the database.I have succes in importing tables into Access. I want to query the portal sql database itself and need to provide a connection string which will made the access easier. Any suggestion or help to do would be much apprecited. thanks in Advance. Barti
We are pulling data from a sharepoint list and we have created a report using business development studio in sql 2005 and have created a table with data that we want to display. We are trying to filter this data so that we can get the report to show entries for the day or for a week, but im not sure how to achieve this? Thanks in advance.
We have set up reporting in sql 2005 so that we can report from a MOSS 2007 list. At the moment the report is created with all the data from within the list. How could i create a filters so that the user can generate a report by name and date?
I already tried SharePoint List Source and found some bugs in it. Ex. Duplication of records, sometimes its throw an error: A possible reason might be you are trying to retrieve too many items at a time (Batch size).
I also used SharePoint WebServices and store the records into XML file and used those files as a source to load the data into Sql Server Table.Â
Is there any other approach to pull the data from SharePoint List?
I have SharePoint 2010, which I have uploaded a PowerPivot model onto.
Currently it doesn't seem like I could setup the Data Refresh service to refresh my model more frequent than once a day. The Data Refresh configuration page looks like this:
Which doesn't show an option for anything more frequent than daily.
I have also tried to refresh the model's database directly on the Tabular SSAS instance (which SharePoint is using to store PowerPivot models) via SSIS or XMLA, but I get an error saying the tabular model is in "ReadOnly" mode, which I could potentially bypass (by detaching and re-attaching the model), but thats starting to sound abit too hacky.
Is there any way I could refresh my SharePoint uploaded PowerPivot model more than once daily?
We are using xml to pull data from a custom sharepoint list into sql 2005. We have set a parameter that allows the user to filter the data by surname, however when the user tries to filter the list the drop down box brings up a list of every record, so there are duplicate entries for each surname.
Is there a way of filtering this so that there is only one instance of the users surname instead of it showing all the records?
I am trying to import the data from SharePoint into my PowerPivot window as a Datafeed. I am able to successfully import the whole data from SharePoint list. But, now i would like to apply a filter (Where Clause) before importing the data.
There is a requirement to insert if there is a new record/update the existing record to Sharepoint. Am able to do Insert/Update to SharePoint if the data quantity is minimum, i.e, records in hundreds.
link: [URL] ....
if the records/data is more(in thousands), while updating to sharepoint, am getting below error. I tried to keep the minimal batch size, but still getting the same error.Is there any setting on Sharepoint that can be set to increase updation capability?
Error details:
 System.ServiceModel.ProtocolException: The content type text/html; charset=utf-8 of the response message does not match the content type of the binding (text/xml; charset=utf-8). If using a custom encoder, be sure that the IsContentTypeSupported method is implemented properly. The first 1024 bytes of the response were: ' <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
I am trying to create a Data Connection Library linking an SQL Database to SP2013. I have been unable to locate instruction or where in SP Central Administration to create the library. The option to Create Data Connection is not available.I am creating an InfoPath form to create-read-write-update data in the SQL database.
I am trying to load data from sharepoint to Database. WhenI try to execute the package, am getting below error.
[SHAREPOINT_SRC_SLTS_FIELDS [286]] Error: Microsoft.Samples.SqlServer.SSIS.SharePointUtility.SharePointUnhandledException: Unspecified SharePoint Error. A possible reason might be you are trying to retrieve too many items at a time (Batch size) ---> System.ServiceModel.FaultException: Exception of type 'Microsoft.SharePoint.SoapServer.SoapServerException' was thrown.
I am looking for a good tutorial on how to set up Data Driven subscriptions through the SharePoint 2012 version of SSRS. I am needing to set up a Data Driven subscription that uses one report, but sends out the specific portion of the report to the appropriate recipients. I have used the information located at URL.... but it doesn't seem to provide all the steps needed to schedule the reports needed. It focuses around setting up the reports to go to shared folder locations. I need something around setting it up to go to email recipients.Â
i never used SharePoint before, only for update some Excel files. But now, I want to extract the Excel Data from SharePoint 2013 to my SQL database. I'm thinking use the Excel Source to do that, however I'm having very errors to achieve that.
I want to do some ETL on data from the Excel files.
Through SharePoint you can upload an RDS file and then click manage data sources to hook upthe report to a proper shared data source. Does anyone know if you can do this through the SharePoint object model? We don't deploy our reports using the designer and we don't want to upload the reports using SharePoint. We want to add the reports to a document library using the object model, when we do this we need to manage the data source so that it will run.
I am using the SharePoint adapters from Codeplex that allow me to use SharePoint source and destination tasks in SSIS for SQL Server 2008 and SharePoint 2010. I am able to pull the data from the SQL Server and insert it into the SharePoint List.
However, I prefer to just have fresh data every time, so I'd like to add a step to delete all the items in the list before inserting the new ones. Is there a way I can configure the SharePoint SSIS destination task to clear all the items before I insert new ones?
All examples I found refer to classes under Microsoft.SharePoint namespace. However, I have the SharePoint CSOM that only gives me the Microsoft.Sharepoint.Client namespace.
I need to read the selected values of a multichoice field, but not sure how to do it with classes in the namespace above.
everthing works, exept the TSQL_x0020_Reference_x0020_Numbe field.
I have published SSRS reports to sharepoint and used the Reporting Services Report viewer to present the reports on a Page.  Everything works fine for the day, however in the morning, when I go to view the page, I get an error messageÂ
The report server cannot process the report. The data source connection information has been deleted. (rsInvalidDataSourceReference)
I am trying to create a Datazen query against a Microsoft SharePoint List. The Data View successfully returns records from the list; however, it appears that the Query String parameter is entirely ignored. I want to use the query string to grab only records with a certain status value.
So far I have attempted specifying a View and Filters:
All return the complete set of records in the list. I do not see a single example or comment online other than the claim that SharePoint List sources are supported. How I can specify a Query String value that will filter the list records?
When I am trying to insert to data from SQL ssis package to SharePoint list people or group column I am getting below error.[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "SharePoint List Destination" (25) failed with error code 0x80131500 while processing input "Component Input" (34). 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.
I'm want to be able to choose(including multiple selections) names from a drop down list that are fetched from the database(from table "Employees") and add them to table "Biotechnology".
