I have inherited a SSRS setup and am trying to do some clean up. Problem is I cannot see the Data Sources Folder. I am setup as SA and SU on both the RS Server and SQL Server. When I go check the catalog in SQL db it does truly exist as well as a number of data sources in the folder that reports are currently using. How can I see this folder?
View 7 RepliesHi
I need to use .txt files as data source, those files are in same folder. Now I connect to them with ODBC, and allow client select which file they want to view by input the name of file as a parameter.
The problem is all those file are tabdelimited and the default of ODBC driver is CSV delimited. So once new file is added in the folder, we must refresh the ODBC otherwise the drive will deal all the data as one column.
Are there any better solution for this case?
I have a question about ssrs security. In report manager I have set a list of users as browsers, and the builtin administrators are content manager in the parent folder where reports are. A user that is not an administrator and neither a user I added, has access to reports.
I'm thinking this person is having access because the data source of the reports use a administrator account to connect to the analysis services  using the option Credentials stored securely in the report server with the 2 checkboxes marked (use as windows credentials when connecting to the data source and impersonate the authenticated user).
I simply marked this option because SSRS is in 1 server and SSAS in other and I think kerberos need to be configured and I haven't looked into it, but I think the report access is separated from the data source.
I wanted to demonstrate how SSIS can easily read an Excel file into a database. I started up the wizard (dtswizard) looked and looked by couldn't find "Excel" in the list of datasources. Next I started up the SSIS IDE, found an Excel Destination, and created a flow that errored out on the copy to Excel step.
>>>
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at Package, Connection manager "Excel Connection Manager": An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
Error: 0xC020801C at Data Flow Task, Excel Destination [666]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.
Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "Excel Destination" (666) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS package "Package.dtsx" finished: Failure.
>>>
I don't understand that message. I suspect the problem is that Excel is NOT installed on the box where I'm running the package. Does that seem right to you? Also, if Excel is needed on the box, how can I develop such a package on a laptop (in the airport) that doesn't have Excel?
Confused, but good!
Barkingdog
P.S. As the saying goes, "I never had this much trrouble importingexporting an Excel spreadsheet in sql 2000 DTS."
I need to see inside a SSIS 2012 project a new SSIS installed component, but in the SSDT 2010 I cannot see the SSIS Data Flow Items tab for adding data source/data destination respect to the choose toolbox items pane.
View 4 Replies View RelatedI've got a report that is using a cube as a data source and I can't get the report to show all the data. Only data at the lowest level of the cube is displayed. The problem is that most of the data I'm concerned with is at higher levels. There's no problem with the MDX. I get the correct results when I run the query.
I'm using a table to show the results. I've also tried a matrix, but I get the same results. I'm using SSRS 2005 and SSAS 2000.
Anyone have experience with this? Am I missing something simple?
Is there a limit on the number of fields that can be displayed in a table object in a data source view in SSAS 05?
One of the tables (the fact table) in my data source view is displaying only 50 fields. The table actually has many more than that. One of the fields that is not displaying is a foreign key that I need to link to a new dimension table. I have tried refreshing the view, but it doesn't bring in any additional fields.
When exporting SSRS 2008 R2 report  to an csv or excel file not all the rows are being exported. The number of rows is less then 500 and it seems to be a pretty straight forward export.
View 3 Replies View RelatedWas wondering if there was a best practice minimum permissions for creating a SQL login to use when setting up a new shared Data source for SSRS report manager?
Something along the lines of them being a data read for the DB and permissions to update tempdb?
Would have thought it not advisable to have the login be able to update the main db...
I have a SSRS 2012 tablix that contains some charts which are by default set to hidden until such time as the corresponding field is clicked. What I want to do is that when the chart is hidden shrink the row height to the height of the text in the rest of the row and when it is made visible expand the row height to accommodate the chart height in the row.
Is this possible and if so how?
The 'Data Driven Subscription Button' is missing from the Subscription option for any report developed in Microsoft SQL Server 2012 SSDT...
Following version is running on SQL Server : Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
 Oct 19 2012 13:38:57
 Copyright (c) Microsoft Corporation
 Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
And Report Server Log is saying: resourceutilities!WindowsService_15!a64!06/23/2015-10:05:08:: i INFO: Reporting Services starting SKU: Enterprise
I am having all SSRS roles including Content Manager, Â etc..
This is a question regarding the use of an XML data source within an SSRS sub-report. This is a two server configuration consisting of SSRS 2K5 and a separate file server exposing xml files over http, IIS 5.
Is it possible, and if yes how, to configure the xml data source to use the application pool identity instead of the current caller identity? I can not turn off impersonation within SSRS. I need to revert to use the W3WC, app pool, process identity within the xml data source. I do not want to configure Kerberos delegation and the file server€™s IIS virtual directory must be set for windows security.
I have a vision of a beautifully interlocked Business Intelligence system in which I load dimensional fact tables through SSIS, build aggregations in SSAS cubes, and quickly and easily generate SSRS reports using the cubes as data marts.
I am now a month into trying to implement this vision and Reality is stomping all over it. Primarily, I am running into issues with the "cube as a data mart" idea. Each cube is taking up an additional gig or two of space, report queries against them seem to take longer than a straight SQL query against my fact tables, and I am running into serious problems trying to pass time-related calculations such as Year-To-Date to my reports.
Has anyone else tried setting up a BI system that uses cubes as the primary drivers for their reports? Have you seen any benefits to doing it that way? Or should I generate most of my reports through the SQL tables?
I'm nearing the point-of-no-return in choosing my final methodology, and any feedback would be greatly appreciated.
Thanks!
-- Steve
I have few reports under different folders in Report Manager. Is there a way to set the data source for the whole folder rather than each report individually? There is also a folder where reports generate dynamically. Since the report project doesn't exist anymore, I can't set the data source through code. How can I set the data source for the whole folder?
View 7 Replies View RelatedHi,
I'm new to SSRS but have a problem connecting to Oracle. I have placed my reports upon a reporting server but the shared data connection isn't working and I'm confused as to why. I have specified the name, connection type as "Oracle", set the correct user id and password for the credentials and the connection string as for example 'Data Source=oracleExample;Unicode="True"'. I also have set the correct entry in the tnsnames.ora file for this datasource, example:
"oracleExample =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 12.23.45.23)(PORT = 3425))
)
(CONNECT_DATA =
(SID = oracle)
)
)"
Unfortunately I receive the following error when trying to connect.
"
An error has occurred during report processing.
Cannot create a connection to data source 'oracleDataSource'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors
"
I'm mystified as to what the issue is. Any ideas? Would restarting the report server pick up the tnsnames.ora file? I have this working locally using report designer.
Any help, much appreciated.
Is there a command that can be used to set the account and password of an SSRS data source? I want to use a datasource that uses the option "(windows) credentials stored securely in the report server". The password for the account to be used can not be known by any humans (or we have to kill them after) so we want to set it programmatically. Is this possible via a command?
View 4 Replies View RelatedI'm attempting to use SSRS (2005) with my Oracle RDB database (old DEC RDB), using an ODBC driver. I am able to create a report, and view data, but I need to add some report parameters to filter the data. When I attempt to use parameters in the query, I either get a syntax error, or a message that my odbc connection cannot use "named" parameters, and that I should use "unnamed" parameters.
Here is a snippet of my sql query:
SELECT Application_Notes.Lease_Application, Application_Notes.Followup_Code,
<...>
FROM
Application_Notes Join
<...>
WHERE
(Application_Notes.Tickler_date >= {ts '2008-02-08 00:00:00'} AND
Application_Notes.Tickler_Date < {ts '2008-02-09 00:00:00'} )
The "Tickler_Date" field is a binary date field. The above syntax is pulled from a Crystal Report, which I am considering moving over to SSRS. Ideally, I would like the user to enter a single RunDate parameter, and have the query do the following:
Where (Application_Notes.Tickler_Date >= @RunDate and
Application_Notes.Tickler_Date < (@RunDate + 1 day) )
If the user has to enter two dates, that will be fine, but I can't seem to get past the errors.
Thanks,
Paul
I'm new to SSRS 2005, and having a very hard time getting something I thought would be trivial to work. It would appear that if you want to report from XML data, there are a variety of ways to do so.
I would just like to use a simple document that was created by serializing to disk. I don't even have a fancy query. Can someone help me with an start-to-end from which I can build upon and add complexity? Thanks.
C:XmlDataCustomers.xml contains a single Customers table; 3 Customer rows with an ID, FirstName, LastName. (I'll try pasting it as a follow-up).
My DataSource type is 'XML' and the ConnectionString is the simplest of URLs "C:XmlDataCustomers.xml". Credentials are set to 'none'. My suspision is that I am not even succeding at creating a valid DataSource. I don't know if it has to do with the XML, the location of the file, the syntax of the ConnectionString or what.
Assuming I get this right, I want all rows and all columns, so I assumed an empty query on the DataSet would be appropriate.
I've found a bunch of examples - but none seam to be this trivial - they all involve using a web service, embedded data in the query, or creating a Customer Data Extension (CDE) to provide the document. I've tried converting these samples to use my DataSource, but so far no luck.
Your assistance is appreciated.
See above...
Apologies for the duplicate post!
I have installed SQL Server 2005 on Windows Server 2003 Standard SP1. THe SAMPLES folder within the C:/Program Files/Microsoft SQL Server/90 folder is missing. I installed SQL Server 2005 SP2 thinking that this might create the SAMPLES folder, but it didn't get created.
Please let me know what should I do to create it. Thanks in advance.
I want to run SSRS report directly by connecting Excel as a data source. The report runs properly from SSDT, but when it is run from Report manager, some users cannot access the Excel connection and the report execution fails. I am using SSRS 2012 and Excel 2010.I have created the ODBC data connection in SSRS and when I use 'Windows Integrated Security' option for credentials, the connection gets successful. But the problem is that all users does not have permissions to access the Excel inline, but they should get the report working.How can I store the credentials to access the Excel in SSRS with a specific username and password?
View 4 Replies View RelatedI cannot get this error resolve either for myself or any users, and I'm actually part of the Admin group! Furthermore, a user with whom I'm tring allow to view this report keeps getting prompted for their windows username and password. I thought that since this datasource was set to Windows Authentification that it would just pass it through?
I'm pulling my hair out at this point:
Screen Shots:
http://www.webfound.net/datasource_connection.jpg
http://www.webfound.net/datasource_connection2.jpg
http://www.webfound.net/datasource_connection3.jpg
Also As far as I know, I've given sufficient permissions to the right logins and right users on my SQL Databases and related stored procs that the datasets (not datasource) run
An error has occurred during report processing.
Cannot create a connection to data source 'datasourcename'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors
Hi All,
It's been a while since I post here. Anyway, I'm trying to call a SSIS package as my data source for SQL reporting service but I keep getting the error "Cannot create a connection to data source 'DsSSIS'.
My DsSSIS consists of the following in the connection string.
="/file c:FarmBillTesting.dtsx /Set Package.Variables[User::FilterValue].Properties[Value];" & Parameters!FilterValue.Value.ToString()
I have tried everything that I know to make this work but I have been able to do so. Any help from the group would be great.
Thanks
Ham
Hi,
I'm am writing a report in SSRS 2005 on a local PC that uses cubes in AS 2000 on another server as a datasource. I am using the Microsoft SQL Server Analysis Services Extension (OLAP9) to connect to the cubes using windows authentication. I can save and preview the report in visual studio and publish the reports on IIS on my local PC with no problems but when I go to view the report in IE, I get the following error:
An error has occurred during report processing.
Query execution failed for data set 'dataset'.
Unable to recognize the requested property ID 'ReturnCellProperties'.
Have read various articles on the net with no conclusive explanation.
I have seen another post on this forum that suggests using OLEDB for Analysis Services instead but if I do this it doesn't allow me to use the MDX visual query designer. Creating the report using OLAP9 then changing to OLEDB and pasting in the query code doesn't work so well either.
I went and read http://technet.microsoft.com/en-us/library/ms159219.aspx about data sources supported by SSRS 2005 and it should be ok.
I am using sql server 2005 with the latest service pack 2.
Any ideas?
Hi,
We are in the process of upgrading a SQL Server 2000 database to 2005. For the mean time, we're trying it out on SQL Server Express 2005. My question is, can reports in SSRS 2005 pull data from SQL Server 2000 in case both are installed side by side in the same machine and the 2000 version is the default instance while the 2005 express edition is the named instance?
Thanks a lot!
C:Program FilesMicrosoft SQL Server90DTS folder missing. Why is this folder missing on my XP machine. I installed DTS backward compatibility components, SQL SP1 and Hot fixes. Im installing it on a non-server. Is this the reason?
View 1 Replies View RelatedI'm learning how to use audits in SQL Server 2014, but when I opened the Security folder there was no Audits folder inside. I did a bit of research and I found out that auditing is a feature in all editions of SQL Server 2012 onwards so I should have the Audits folder in order to create a new audit. There doesn't seem to be many posts like this online because the only posts I found about missing audit folders were about SQL Server 2008 (which doesn't include auditing in the standard version).
View 8 Replies View RelatedI am building a webapp that calls an SSRS instance to display a report based on another servers web service.I can make a call from the ssrs server using this in the RDL:
<Query>
<Method Namespace="http://machine/webservices" Name="HelloUser">
               <Parameters>
                               <Parameter Name="user">
                               <DefaultValue>Craig</DefaultValue>
                               </Parameter>
               </Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">*</ElementPath>
</Query>
This works no problem. but.. I want to have the user parameter "Craig" be a parameter passed in from the web app. If it were a regular SQL data source you would put @user in the query. How do you do it with XML text queries?
I am looking command (cmd/powershell/c#) for setting/updating credential for SSRS data source.
View 2 Replies View RelatedWe have 1000s of SSRS reports hosted on the SQL 2008 R2. All reports are supposed to have all parameters "hidden". Random reports have been reported to expose the parameter when developers accidently deploy to test servers. Is there a way to identify the reports and the parameter property (Hidden, Visible, Internal) from the Report server Database?
I checked executionlog2 but can not find the parameter property.
I've seen a few threads on this which seem to suppose the problem is on Express edition rather than Standard edition. Not so here.
This is definitely Standard edition, SP3 as seen by running
SELECT CONVERT(char(20), SERVERPROPERTY('Edition'))
SELECT CONVERT(char(20), SERVERPROPERTY('ProductLevel'))
In SSMS
Under the Management folder there is no "Maintenance Plans" folder.
There IS a "Maintenance Plans" folder under the Legacy Folder.
I have another instance which is Standard and SP1 which looks just fine.
I have windows 2003 reporting services 2005 that has been working fine. Now all of a sudden when I go to http://localhost/reports I am unable to assign security to folders(the link is hidden) the New data source button is hidden, the new folder button is hidden, upload folder and report builder are also hidden. I am logged in as aministrator.
Also when I connect to reporting services through SQL Mgmt Studio I have the same issue, there are no options for permissions only a general option.
I'm sure it's a permissions issue but what do i do to resolve?
Thx
I'm working through the SSIS tutorial, and am on lesson 2.
This says
"Double-click Foreach File in Folder to reopen the Foreach Loop Editor.
Click Collection.
On the Collection page, select Foreach File Enumerator."
The drop down only contains
foreach ado enumerator
foreach ado.net schema_rowset.enumerator
foreach from variable.enumerator
foreach nodelist.enumerator
foreach smo.enumerator
How do I get the "foreach file in folder" to appear in the drop down list?
Gary