I figured the search engines would do a good job of delivering the answers to these questions, but as I have seen this theme reoccur many times over the past few weeks in the MSDN forums, I am going to write out the steps for everyone (if nothing else, at least I'll just be pasting a link to this versus copying the steps all over again in forum responses).
For many people installing the SSRS plug in for SharePoint/WSS (both SSRS 2005, but more likely 2008), you can get these annoying errors (this error can be found in Root:\Users\YourLogin\AppData\Local\Temp and will be prefixed with RS_SP):
For some of you, this error may become a common nuisance when installing the SSRS plug in for MOSS/WSS 3.0 for both SSRS 2005 and 2008:
******* User does not have permission to add feature to site collection: http://<SharePoint URL here>:36076.
This can be particularly frustrating if you are logged in to the box under the farm admin account and have local admin privileges.
To work around this error, follow these steps:
1. Double check that you have a site collection set up on the farm, not just the web app
2. Log into the server you are installing the Reporting Services plug in to with credentials that are the farm admin for the MOSS install
3. Run the CMD tool with Administrator privileges (also called elevated permission)
4. Navigate to where you have the sharepointrs.msi and type sharepointrs.msi SKIPCA=1
5. Navigate to your Temp% directory to find the unpacked rsCustomAction.exe file
6. In the CMD tool, execute rsCustomAction.exe /i
7. After the install, you will probably need to navigate to Central Administration > Site Actions > Site Settings and under the heading Site Collection Administration click Site Collection Features.
8. From there, click activate on Report Server Integration Feature
9. The settings for SSRS will now have their sub section on the Application Management tab in Central Admin
References:
Tags: reporting services,
ssrs,
sharepoint,
sharepoint integration,
business intelligence
Categories: SSRS |
SharePoint
We kicked off the first BI User group meeting for Austin last night and had a very decent turn out for our first meeting.
For our first topic, I covered the sticky points of implementing Kerberos in order to support Reporting Services 2008 running in integrated mode in a MOSS farm. As doing the entire implementation would take longer than the time allotted for our meeting, I will be posting a write-up for anyone to download and see all the steps.
Based on feedback from the group, I will expand this to show a four server farm and all the necessary steps there-in; I had on my laptop an AD server, Web and App and database, but as I can live with slogging performance for the sake of writing a walk through, I will add the last machine to the line up so that it represents the way many in the BI User group and our customers have their MOSS farms implemented.
Also in this mix is a few of us waiting with bated breath so see the SharePoint Kerberos Configuration Utility, something put together by Spencer Harbar, but that no one has seen released yet – if I can get a copy, or find where he is eventually going to post it, I will be sure to update that reference here.
Tags:
Categories: SSRS
If you wish to upgrade to SSRS Report Builder 2.0 and you are on a 64-bit environment, you may run into this issue when running the April 7, 2009 download:
Text (so it is search-able): Reporting Services Add-in for SharePoint products and technologies is not installed. It must be installed before you can install the SQL Server 2008 Report Builder 2.0 for SharePoint.
If you are like me when you got this error, you first thought reaction is: "but I did! What is going on? I know I have everything installed!".
Relax, it is not your fault. This time (provided you are like me and on a 64 bit platform).
To confirm, check out this thread on Microsoft Connect to confirm your sanity and also vote this issue as a priority. In addition to other users adding their comments about, some have posted work-arounds to the issue. I find a couple of them rather complicated and opted for another method that has thus far suited me very well. Below are the steps:
-
-
Go to your site's Central Admin and click on the Set server defaults link
-
-
Publish a Report Data Source or Report Model content type to your site, then from the Create actions on the list, select new report builder report. If you get the 2.0 version, you have installed it and set the Central Admin settings correctly.
-
Once in Report Builder, there is one last setting. Click the round start button in the upper left corner and then select Options. The Report Builder Options will pop open and you will need to fill in the top option with a value so that you can access the data sources from the lists on your SharePoint site.
While this works, it is not the most ideal in my mind as the user has to enter the SharePoint site URL, making usage of Report Builder not entirely intuitive. I did find a Report Builder config file in the install directory with an option to set this value - -hopefully making it effective for any launch of the app, however I did not find this to be the case. Any value inputted there was ignored by the application URL, however only recognized by a direct launch of the executable from the install directory.
If I find how to modify this setting server side, I will be sure to post, but for the time being, here is a simple work around for enabling Report Builder 2.0 in 64 bit SharePoint integrated environments.
Tags: sql 2008,
sharepoint integration,
reporting services,
64 bit
Categories: SSRS |
SharePoint
SharePoint and WSS are fantastic in that almost everything is driven by Web Services. For the data and reporting gurus, our job is a lot easier when it comes to extracting data from the mini-applications and workflows that the business community creates inside a WSS or SharePoint Portal framework – though not completely straight forward, so that is what we’ll discuss today: using SSIS to extract data from WSS lists using the out of the box WSS web services.
In this example, we have a user community that has data in a WSS list that needs to be extracted so it can be reported on. The data in this list represents a collection of information about a business process.
One of our major limitations in using out of the box SSIS features is the Web Service Task. If you look at Books Online, article: http://msdn2.microsoft.com/en-us/library/ms140114.aspx, the first note indicates that the Web Service task supports primitive types only. If you look at the WSDL for our particular method we’ll be using, GetListItems, you can see from the WSDL below that we will have a problem:
To get around this we’ll use a proxy since the Web Service task is the only item currently that can use a web reference. For our first step, we will use the same tool the VS 2005 uses when you add a web reference to a web or windows project: wsdl.exe. We’ll create our class by executing the following from the command line (wsdl.exe should be located in your C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin directory):
Wsdl.exe /language:VB /out:SharePointListService.vb http://SharePointURL/TargetSite/_vti_bin/lists.asmx?wsdl Note that you will need to do this again for any sub-site that you are targeting for your import as part of the WSDL description is the location of the service.
Our SSIS package will have three simple control flow steps:
- Script Task
- XML Task
- Data Flow Task
Script Task
After you run this command, open your newly created vb file (I normally code C#, but the SSIS script task only uses VB). In your script task, we are going to make a new class reference. Right click on the solution, go to Add > Add Class. You can use the same name as newly created file from wsdl.exe or something that will more appropriately fit given your project. Once you create the new class file, erase any text/code automatically generated and then paste the content of your wsdl.exe outputted file into this code file in your project. Add references to the System.XML and System.Web.Services.
After you have completed this step, you are ready to consume your new class and mimic the Web Service SSIS task.
Here is sample code from the default ScriptMain file. My code in this sample uses the proxy class Lists (generated by wsdl.exe) to call the service, pass the necessary parameters and then save the output to an xml file. Note that for the view name for a list, you have to pass the GUID. It seems that the method was not set to return values using the common view name.
One of the easiest ways to grab the GUID is to go to modify the view you want to use to pull the data from and look in the URL, the portion of the query string after View= has the GUID for this view.
Another way to do this is to use the Web Service Task against the Views web service in WSS/SharePoint, call the method GetViewCollection, pass the List name as a parameter and you can get an XML output with all the views and GUIDs associated with a list item.
After implementing the code above, your Script Task is complete and your output will be an XML file of all the data from the list given the view you hit. An important point to note is to set your rowLimit parameter. The others can be blank or null, but if the rowLimit is not set, then by default you will only get the data that displays in the default view paging size, which is usually 100 records. By setting it to a figure larger than the collection, you will get the entire collection of records.
XML Task
The SSIS XML Source in the Data Flow section of your project does not like XML files that have multiple namespaces, which is almost a guarantee when you are retrieving data from a SharePoint List.
The XML Task is pretty basic in that it strips the extraneous namespaces from the XML file. I got this from Ashvini Sharma’s blog when trying to solve the same problem in hitting RSS feeds for a real estate project. Here is the link to his post:
http://sqljunkies.com/WebLog/ashvinis/archive/2005/05/25/15654.aspx
Right click on the XML Task, choose edit, and then we will set the properties on the task as such:
Under the SecondOperand property, paste the following XSLT:
<?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="no" />
<xsl:template match="/|comment()|processing-instruction()">
<xsl:copy>
<xsl:apply-templates />
</xsl:copy>
</xsl:template>
<xsl:template match="*">
<xsl:element name="{local-name()}">
<xsl:apply-templates select="@*|node()" />
</xsl:element>
</xsl:template>
<xsl:template match="@*">
<xsl:attribute name="{local-name()}">
<xsl:value-of select="." />
</xsl:attribute>
</xsl:template>
</xsl:stylesheet>
I have the FileConnection set to the file that my first task outputted, and the operation saves the results so that I end up with a clean version of my outputted WSS/SharePoint list data.
Data Flow Task
Setting up the import can be one of the more painful tasks, depending on the number of columns you have on the view and list your are hitting. The XML that you will have after pulling the data will have GUIDs for all of the user defined columns in your list, which probably is where most of your data is.
To know exactly how the GUIDs in your XML export map up to columns on your list, you can export the schema for SharePoint. In the Lists web service, hit the method GetList and pass it the name of the List you are using. Your output will have the schema of the list. Here is a sample for one column:
<Field ReadOnly="TRUE" DisplayName="TestColumn" Name="xd__x007b_8CEEC711_x002d_0B7C_x002d_4C6A_x002d_9F82_x002d_5584303A997F_x007d_" XName="{8CEEC711-0B7C-4C6A-9F82-5584303A997F}" Node="/my:myFields/my:TestList/my:TestColumn" Type="Text" ColName="nvarchar18" />
The XName element has the GUID for the column that will show up in the data export. You can use the output from the GetList method to build mappings for your export.
Your Data Flow Task at a minimum will have two objects: an XML Source and your OLE DB Destination. I would recommend putting a Data Conversion object between the two – that way you can save on space on the insert into the database and have a way to redirect failed rows for analysis should you get something really strange in your incoming dataset.
That rounds it out; please feel free to post any questions.
Tags:
Categories: SSRS |
SharePoint |
SSIS