Possible To Use A Sharepoint List As A Datasource?
Jun 13, 2006In Reporting Services 2005, is it possible to create a data connection to a sharepoint list? If so, what connection type and connection string do i use for this?
View 21 Replies
ADVERTISEMENT
Sharepoint List As Datasource In Reporting Services ?
Oct 25, 2007
Hi everybody,
I was wondering if it's possible to use a sharepoint list as datasource in reporting services ? If yes, what's your trick ?
Is it possible to use a directory as datasource in reporting services ?
Thanks for your response,
Have a nice day,
Aurore
Trouble With DataSource And Sharepoint Integration
Jan 30, 2007I have setup Sharepoint Integration and built a report that connects to a SSAS cube to present data. The report was build in VS2005 before I installed SQL SP2 and the Sharepoint Add-in. I am able to upload the report (rdl) and the datasource (rds) , but when I try to view the report I get the following error.
The report server cannot process the report. The data source connection information has been deleted. (rsInvalidDataSourceReference).
The report and the datasource are in the same folder in a document library. When I try to deploy the reports using VS2005 I get a reporting services prompt when the deploy starts. Tried a couple of account to no avail.
One thing that I noticed is that the datasource icon is a blank page vs the icon I see in the IDE, but the report has the report icon in the document library.
Thanks for any help.
Add OLE DB Driver To Datasource List
Jan 29, 2008Hello all,
I upgraded my DB2 client from v8 (32 bit) to v9 (64 bit) and the IBM OLE DB DB2 Driver is not showing up in the data source list in Import and Export Wizard. I need to use the IBM OLE DB Driver because the Microsoft driver doesn't work (db2 login).
Can anyone tell me how to add the IBM OLE DB Driver into the list. Is is a PATH/CLASSPATH issue??
Thanks
Brian
SSIS And Sharepoint List
Apr 28, 2007i would like to know whether there is anyway to load the content of a Sharepoint(2007) List from a SQL server database.
have tried as a workaround to make Access database(.acc db) as a source for the List so that when changes made to the .accdb, automatically it reflectes the sharepoint list.
so i tried to make use of ssis 2005 to dts data from SQLserver db(Source 1) to .accdb(Destn1), then from Destn1 the SPList would receive data.
the Object that accdb supports to SPList is of Sharepoint List type i am not able to DTS data from sql server to accdb.
Any suggestions wld be appreciated!!!
Query SharePoint List
Jan 31, 2008
Does anyone have any way to run a query against Sharepoint to return the content of a list through transact SQL?. Perferably without having to go throught the "back door" and access the Sharepoint database directly?
Thanks
Problem With DataSource Gridview And Bulleted List
May 9, 2008 protected void BulletedList1_Click(object sender, BulletedListEventArgs e)
{
TabContainer1.Visible = true;
pnlClientChosen.Visible = true;
pnlNewClient.Visible = false;
ListItem li = BulletedList1.Items[e.Index];
} I have a bulleted list tied to a table, and on click the selectedValue of the bulleted list populates a datagrid. Heres my code:
<asp:BulletedList ID="BulletedList1" runat="server" BulletStyle="Disc" DataSourceID="SqlDataSource1"
DataTextField="LNAME" DataValueField="DEM_ID" DisplayMode="LinkButton" OnClick="BulletedList1_Click">
</asp:BulletedList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:csAHECData %>"SelectCommand="SELECT [LNAME], [FNAME], [MNAME], [DEM_ID] FROM [Demog_TOB] ORDER BY [LNAME], [FNAME], [MNAME]"></asp:SqlDataSource>
And then for the datagrid:
<asp:Panel ID="pnlClientChosen" runat="server">
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource2">
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:csAHECData %>"
SelectCommand="SELECT [DEM_ID], [LNAME], [FNAME], [MNAME], [ADDR1], [ADDR2], [CITY], [STATE], [ZIPCODE], [COUNTY], [Gender] FROM [Demog_TOB] WHERE ([DEM_ID] = @DEM_ID)">
<SelectParameters>
<asp:ControlParameter ControlID="BulletedList1" Name="DEM_ID" PropertyName="SelectedValue"
Type="Int64" />
</SelectParameters>
</asp:SqlDataSource>
</asp:Panel>
On Bulleted list select I have this code:
protected void BulletedList1_Click(object sender, BulletedListEventArgs e)
{
TabContainer1.Visible = true;
pnlClientChosen.Visible = true;
ListItem li = BulletedList1.Items[e.Index];
}
I have no idea whats wrong but when I click on the bulleted list , the pnlClientChosen becomes visible (which contains the data gridview); but there is no gridview available. There isn't even column names. One thing that struck me as odd is when I created the datasource and I created the Where statement, when I selected control as ""Where Dem_ID = a control" and I set the control to bulletedlist1, there were three listings for bulletedlist1. In fact, there were three listings for each control on this page.
Thanks in advance, I hope I explained this well but I'm in a hurry right now.
How To Query A Sharepoint List From SSIS
Jan 2, 2008I have been searching for a GOOD example of using SSIS to query MOSS 2007 list data. I just want to return the data from a list in Sharepoint and use that data in SSIS. It should be as simple as defining a data source to connect to a specific list and return the rows like a SQL query.
Can anyone provide a good example of doing this in SSIS (and by the way, I'm not looking for how to write a C# program) - I want to be able to do this directly from SSIS.
Any help would be greatly appreciated.
SharePoint Query On List - RsCompilerErrorInExpression
Aug 14, 2007I have been working on this all day. First day using SQL Reporting Services.
Software being used -
VS2005 - SQLServer Business Intelligence Studio - with SharePoint Windows Services 2.0 Web Services
1. I have created a Data Source - XML - Which is a SharePoint List
http://xxx.xxx.com/sites/OpRiskSP/OpRiskTechPM/MMUpdate07/_vti_bin/lists.asmx
Passed
2. Next I go to create a DataSet - ds
<Query>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems"/> <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
</Query>
Parameters
1. Name = listName, Value = MaturityModelWorkingCopy
Next I Run the query and boom the data returns
Then I click the refresh button to populate the Reprots Datasets - called ds
Passed
3. Layout
I am assuming you can drag and drop the data fields onto the designer which that does work
I drag the first dataField and I get a textbox =
=First(Fields!ows_ID.Value)
Not sure what the First stands for
4. Preview
FAILED
[rsCompilerErrorInExpression] The Value expression for the query parameter €˜listName€™ contains an error: [BC30451] Name 'MaturityModelWorkingCopy' is not declared.
Build complete -- 1 errors, 0 warnings
Any help would be grand.
<moojjoo />
HOW To Extract Data From A SharePoint List Using SSIS
Apr 9, 2008Has anyone figured out how to extract the data from a SharePoint List into a SQL Table or ssis variable
using SSIS?
Reporting Against SharePoint List Or InfoPath Form(s)?
Dec 19, 2006Hello,
Anyone have any solid solutions for using Reporting Services 2005 to report against a SharePoint List and/or InfoPath Forms Document Library?
This seems to be a popular one for reporting against the SharePoint List, but it looks like there are some issues with it, such as getting it to work with Subscriptions...
http://www.teuntostring.net/blog/2005/09/reporting-over-sharepoint-lists-with.html
TIA!!
Sharepoint 2003 List Data Provider
Mar 20, 2007
Why isn't there a Sharepoint 2003 List Data Provider similiar to the one in Access 2003 and 2007 for SSIS?
Dave
Sharepoint List Reporting Services Extension
Sep 17, 2007Is Microsoft planning to include reporting services extensionsion for usering Sharepoint lists as datasource in MS SQL Server 2008?
And what about reporting services extension for building semantic model on Sharepoint lists?
-- Thanks,
Filtering Report Data From A Sharepoint List
Mar 16, 2008
Hello,
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?
Thanks.
'unable To Obtain A List Of Tables From The Datasource' Error From Excel
Oct 12, 2007Hi,
This is a very detailed question, I hope this is the best forum to address it. It is more related to general ODBC access, and less to SQL Server data access.
Calling all ODBC experts!
I am a developer using an ODBC toolkit to connect to my companies metadata management product, which in turn communicates to SQL Server, Oracle, DB2 and other databases. The ODBC toolkit we are using is very old, and has been very stable in accessing data with clients such as Access, Excel, Visio and others. It is a read-only ODBC driver that does not support a lot of advanced query and data manipulation features, such as catalog.
I'm using Visual Studio 2005 to debug the ODBC calls that Excel is making when attempting to load data through the External Data Wizard. The Excel version I am using is 2003. The method that I am using to attempt to load data is with 'Data | Import External Data | Import Data'. When that dialog appears, I select 'New Source...' then 'ODBC DSN'. I then select my DSN and click Next.
At this point, I get back the error 'unable to obtain a list of tables from the datasource'.
What is frustrating is, if I do 'Data | New Database Query' and use MSQuery to load the data, everything works fine.
As I mentioned I am using VC2005 and debugging, I believe the problem has to do with the capabilities of our driver and the columns that Excel is binding to return data. In this call, Excel is binding two columns - Table Qualifer (
TABLE_QUALIFIER (1)) and Remarks (TABLE_REMARKS(5)). Our driver does not support qualifiers, so we return a NULL for that value:
case TABLE_QUALIFIER:
fSqlTypeIn = SQL_CHAR;
rgbValueIn = NULL;
cbValueIn = SQL_NULL_DATA;
Excel makes the SQLTables() call, which successfully returns the entire list of tables from our server product. It then attempts to SQLBind the two columns I mentioned above. The first call to SQLFetch returns with SQL_SUCCESS but the ODBCGetData call fills the QUALFIER column with a NULL. So obviously, Excel doesn't like this value and then ends up quiting and displaying the message above (I think....) This is confirmed by looking at the ODBC trace calls:
EXCEL 1500-1b18 ENTER SQLTablesW
HSTMT 01F120E0
WCHAR * 0x4DE3B000 [ 1] "%"
SWORD 1
WCHAR * 0x4DE272E4
SWORD 0
WCHAR * 0x4DE272E4
SWORD 0
WCHAR * 0x00000000
SWORD 0
EXCEL 1500-1b18 EXIT SQLTablesW with return code 0 (SQL_SUCCESS)
HSTMT 01F120E0
WCHAR * 0x4DE3B000 [ 1] "%"
SWORD 1
WCHAR * 0x4DE272E4
SWORD 0
WCHAR * 0x4DE272E4
SWORD 0
WCHAR * 0x00000000
SWORD 0
EXCEL 1500-1b18 ENTER SQLBindCol
HSTMT 01F120E0
UWORD 1
SWORD 1 <SQL_C_CHAR>
PTR 0x01DD74C0
SQLLEN 256
SQLLEN * 0x01DD74BC
EXCEL 1500-1b18 EXIT SQLBindCol with return code 0 (SQL_SUCCESS)
HSTMT 01F120E0
UWORD 1
SWORD 1 <SQL_C_CHAR>
PTR 0x01DD74C0
SQLLEN 256
SQLLEN * 0x01DD74BC (-1163005939)
EXCEL 1500-1b18 ENTER SQLBindCol
HSTMT 01F120E0
UWORD 5
SWORD 1 <SQL_C_CHAR>
PTR 0x01DD75C8
SQLLEN 510
SQLLEN * 0x01DD75C4
EXCEL 1500-1b18 EXIT SQLBindCol with return code 0 (SQL_SUCCESS)
HSTMT 01F120E0
UWORD 5
SWORD 1 <SQL_C_CHAR>
PTR 0x01DD75C8
SQLLEN 510
SQLLEN * 0x01DD75C4 (-1163005939)
EXCEL 1500-1b18 ENTER SQLFetch
HSTMT 01F120E0
EXCEL 1500-1b18 EXIT SQLFetch with return code 0 (SQL_SUCCESS)
HSTMT 01F120E0
EXCEL 1500-1b18 ENTER SQLGetDiagRecW
SQLSMALLINT 3
SQLHANDLE 01F120E0
SQLSMALLINT 1
SQLWCHAR * 0x0013531C (NYI)
SQLINTEGER * 0x00134F0C
SQLWCHAR * 0x00134F1C (NYI)
SQLSMALLINT 512
SQLSMALLINT * 0x00134F04
EXCEL 1500-1b18 EXIT SQLGetDiagRecW with return code 100 (SQL_NO_DATA_FOUND)
SQLSMALLINT 3
SQLHANDLE 01F120E0
SQLSMALLINT 1
SQLWCHAR * 0x0013531C (NYI)
SQLINTEGER * 0x00134F0C
SQLWCHAR * 0x00134F1C (NYI)
SQLSMALLINT 512
SQLSMALLINT * 0x00134F04
So I began to examine the GetInfo calls. There are probably 100 or so that Excel makes. Anything related to qualifiers or cataloging seems to indicate (according to MSDN) that we do not support it. So my thinking was, if Excel properly identifies that we don't support qualifers, why does it attempt to bind the column? Is this a flaw in Excel, or are there some other GetInfo properties that I am not properly setting?
The attributes I am setting (that seem related) are:
SQL_QUALIFIER_LOCATION = 0
SQL_QUALIFIER_USAGE = 0
SQL_QUALIFER_TERM = ""
SQL_CATALOG_LOCATION = 0
SQL_CATALOG_NAME = "N"
SQL_CATALOG_TERM=""
SQL_CATALOG_USAGE=0
Looking at ODBC trace logs, the only GetInfo values that Excel is quering is SQL_QUALIFIER_LOCATION, SQL_QUALIFIER_TERM and SQL_QUALIFIER_USAGE. According to the MSDN documentation, an application is supposed to query SQL_CATALOG_NAME to determine if catalogs are supported. I know it is not, because I don't see it in the ODBC trace log, or my breakpoints in VC2005 are never hit.
I realize this is a HUGE question. I hope that I came across clear and that my question is understandable. I guess what it comes down to is - (maybe some Excel engineers can answer this one) - is this method of loading data into Excel require a Table Qualifier? If not, how can I configure my settings so that it asks for Table Name (as MsQuery does) instead?
Thank you so much for your time and consideration!
SQL Server 2014 :: SSIS SharePoint List Destination?
Mar 24, 2015We are just looking to move to SSIS 2014 from 2008R2 however we have a number of packages which write to Sharepoint lists. The SHarepointDestination doesn't seem to work in VS 2013, any solution other than buying a third party connector.
View 1 Replies View RelatedIntegration Services :: Best Way To Pull The Data From SharePoint List?
Jun 15, 2015I 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?
Generating Reports From SharePoint List Using Reporting Service
Feb 29, 2008Hello Everybody,
I am presently working on a project which handles much larger amount of data. The application demands extensive reporting from the SharePoint data. I'd like to know how I can generate reports from the SharePoint lists using Reporting Services.
Planning to install in SQL Server Integrated mode
Thank you,
Arun
Filtering Sharepoint List Data Using Report Parameters
Mar 27, 2008
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?
Any help would be much appreciated.
Power Pivot :: Filter Sharepoint List Data In It?
Mar 20, 2015I 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.
View 2 Replies View RelatedUse Business Objects (generic List Object) As Datasource To Generate A Report??
Apr 2, 2008I was wondering if it is possible to use a generic list object to use as the datasource/data for a report? Right now we use these business objects to display information on our website and would be great it we could re-use those objects and create reports based on them. We are doing some calculations etc in the business layer to build these objects, so if we could re-use, that would be best.
Thanks!
Integration Services :: Connect SharePoint List View To SSMS
Aug 31, 2015I need to install SSIS/SSRS in SSMS. I have connected the SharePoint list view to excel for reporting purpose but due to data size the performances is very slow in excel and now I would like to connect the SharePoint List to SSIS/SSRS
I have windows 8, 64 bit system
I have installed SQL Server Management studio 2012 but I am unable to enable the Integration and Reporting Services
May be I have missed to choose the option during installation.
Integration Services :: Load Data From Sharepoint List To Database
Apr 15, 2015I 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.
Integration Services :: Cannot Find SharePoint List Source And Destination
Nov 6, 2015I have installed the SharePoint adapters from codeplex and they show OK in SSIS 2008R2. But in SSIS 2012, I can't find them and their is no SSIS component tab to pick it and add it to the toolbox.
View 2 Replies View RelatedIntegration Services :: Clear SharePoint List Before Running SSIS Data Flow
Oct 19, 2015I 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?
Integration Services :: Source Script Component To Read Data From Sharepoint List?
Apr 29, 2015All 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.
my code looks like this:
Webweb = cont.Web;
cont.Load(web);
cont.ExecuteQuery();
Listsstest = web.Lists.GetByTitle("T-SQL
Code Review Tracking");
//CamlQuery query = CamlQuery.CreateAllItemsQuery();
[code]....
SQL Tools :: Datazen - Query String Format For Microsoft SharePoint List Data Connection
Sep 17, 2015I 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:
?View={B9FF1729-AB8E-4C55-B17C-C660480334BB}&FilterField1=Status&FilterValue1=Closed&FilterField2=Request%5Fx0020%5FType&FilterValue2=Project
Just the filters:
?FilterField1=Status&FilterValue1=Closed&FilterField2=Request%5Fx0020%5FType&FilterValue2=Project
And even:
?ThisWillDoNothing
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?
Integration Services :: SSIS 2014 Sharepoint List Source And Destination Missing From Toolbox
Jun 22, 2015I am using SSIS 2014 and installed adapter for sharepoint list source and destination and when I refresh the toolbox I don't see them. Is there a way to manually add them?
View 4 Replies View RelatedIntegration Services :: Insert To Data From SSIS Package To SharePoint List People Or Group Column
Dec 13, 2013When 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.
View 8 Replies View RelatedOLAP Datasource: Second Parameter List To Be Filtered Based On The First Parameter
Apr 17, 2008Does anyone know if this is possible right out of the box in SSRS 2005 against an OLAP data source?
I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do I do this against an OLAP data source? Lets say I have param1 and param2 in a dataset. I want Param2 to show the locations only based on what I select in Param1.
Same but a little different: I have Parameter1 and then my second parameter (Param2) is a boolean (True/False). I want to show Parameter 3/Paramater 4 based on selection of Param2 (So, if true, show Param3, if false, show Param 4) and remember we are doing this in a sequence.
Can you do this thru SSRS? Any help would be great.
Thanks for your time in advance.
Kent
Reporting Services And Sharepoint Integration - Sharepoint Alternate Access Mappings (Zones)
Mar 2, 2007I 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?
Many thanks to anyone who can help!
Reporting Services :: Count Values In A Column Based Upon Distinct Values In Another Column In SharePoint List
Sep 7, 2015We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
          red   blue  green
  pen       2    0    1
  marker    0    1    0
  pencil      1    3    0
  highlighter  1    1    1Â
We tried Sum but not able to display in single row.
Sharepoint Integration Microsoft.ReportingServices.SharePoint.UI.WebParts.dll
Feb 2, 2007Does 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?
Thanks!
Erik