Deploy reports files in SSRS (Report Server) using GUI tools

There are many ways to deploy the SSRS reports in Report Server. The most convenient methods are 

Using GUI tools

• using Solution Explorer (SSDT)
• Report Manager
• Report Builder

Using Command Line utilities

•  Power-Shell Scripts
• Rs.exe Scripter (in house tool )  

Using Solution Explorer : 

It easy deploy the SSRS Report file using the solution explorer.

Right click on the project as shown below.

Click on Properties. Under the Deployment Section there are several properties.

Overwrite Data Sources : If this property is set to true then it will overwrite the existing data source in the report server. If the report need to use shared data source which is already available in report server then it need to set to false.

TargetDataSourceFolder : This property is used for setting the Target data source folder. 

TargetReportFolder : This property is used for setting the Target report folder. 

TargetReportServerURL : This property is used for setting the Report server URL. It is the URL used by report manager for browsing the reports and executing reports.

TargetReportServerVersion: This property is used for setting the report server database used to connect through reports.  if unsure about version then click on detect version then it will automatically set the respective SQL Server version.




Once all setting done click apply and OK.

Select the report that need to deploy. Right Click on the Report. Click on Deploy.

If the user has sufficient permission to deploy the report then it will successfully deploy the report.

Note : Make sure that when deploying the report run the visual studio (Data tools) as administrator.

Verify the report deployed in report server.

StudentMarks report deployed successfully.  Click on the report to view the data.

The Second way to deploy the SSRS report is using the report manager. For test purpose am deleting the same report and deploy through the report manager.

Deploy using Report Manager : 

It's easy to deploy the reports using the report manager.  Click on Upload File

Browse the report (rdl) path and click OK.

if the check box Overwrite item if exists is checked then the report will replace the same existing report.




Report deployed successfully.


Deploy using Report Builder : 

SSRS Reports can also be deployed using Report Builder.

Open/Create the report in Report Builder which is similar to data tools.

Go to Menu tab and Publish the report parts.

Click on the Publish Report Parts. It will show two options. If report settings already done properly select the option publish all report parts with default settings.Click on that and verify in report manager whether report deployed successfully. 

SSRS ReportServer Database Internals

SQL Server creates two Reporting databases when reporting services installed on report server/machine. user should connect the server using database engine services.

Under database there will be two databases that are used by reporting services.

1) ReportServer

2)ReportServerTempDB

Report server is the main database and contain all information about reports,subscriptions,schedules,report data source ,report snapshots, report history, roles,data source and data sets.

The tables shown above are created by default. These all tables are useful to identify the all the report service details.

I will illustrate few tables and its usage.

 dbo.Subscriptions : 

When user subscribe a report one row will be added to the table. This table used for general subscriptions as well as data driven subscriptions.

It stores information about last status (when the report ran and its status (fail/passed) and stores error if any. It also stores report parameters (input/output),Event type ,Delivery type(email,windows share).

dbo.Catalog : 

This table stores the information report server folder path (include folder hierarchy).  

dbo.DataSource :

This table store the information related to SSRS Data source. It contains connection strings,username ,password (encrypted) etc.

dbo.ExecutionLogStorage :

When ever a report executed in the report server a row will be inserted into the report server. Its stores reportid,timestart ,timeend,status ,rowcount and username etc.

dbo.Roles : 

Roles table stores the predefined roles as well as custom roles. below are the default role stored in roles tables. if any new custom role created it will add row in a table.

• Browser
• Content Manager
• Model Item Browser
• My Reports
• Publisher
• Report Builder
• System Administrator
• System User

dbo.users :

user table is used to store the system users to connect the report server database.

There are system view also available that is very useful for error information.

The ExecutionLog views is used for getting report execution information. Its also returns the execution status and additional information include system default values.

There are plenty system stored procedures available in SSRS used for creating subscriptions,snapshots,schedules etc. using the system stored procedures SSRS can be customized through pro-grammatically.

There is additional database role available in report server apart from the predefined fixed server roles.

RSEXECRole : 

This Role is used for executing the reports in SSRS Server. User must have associated with this role if reports needs to execute in the SSRS  report manager.

Create Custom Role in SSRS (SQL Server Reporting Server)

Its easy to create custom role in SSRS using SQL Server Management studio.

First SSMS need to open with run as administrator. Right click on SSMS  >>> Run as adminstrator

The windows operating system i used here is Windows 8.

Then select Reporting services and click on connect

Make sure report services are running on the system. To check go to services.msc

Check SQL Server Reporting Services are running .if it is stopped. Right Click on the service and click on start.

Once we connect the SQL Server. under security folder there are two folders called roles and system roles.

This means there are two level permission 

•   Item level (Content Level) :  This permission are managed under roles folder. There are some default level permission available in SSRS.

• Browser :  This role is used to view the reports and folders and its hierarchies. It also used to manage the individual subscriptions. Browser permissions can be viewed by using right click on browser role. Click on Properties.

These are the browser permission for SSRS report manager. Similarly every role permissions can be able to view by right click on respective role and click on properties.

Content Manager : 

Content manager has the highest level permissions under the Roles folder . For Content manager all the check boxes are  checked in the above picture . The means user with content manager can able to view ,delete ,modify and publish the reports and subscriptions.

Publisher: 

Publisher role is used for publishing the reports. This role also can be used for managing data sources. There are several ways to publish reports.

Click here to know the different way to publish the SSRS reports. 

My Reports :

My reports role is generally used to manage the report created by individual user.

Report Builder : 

This role is generally used to manage the report using report builder. User can able to view the reports using the report builder.

     To create the Item level custom role Right click on role and click on new role.

Select the list of permissions form the below permissions.

• 
  
• 
  
• 
  
• 
  
• 
  
• 
  
• 
  
• 
  
• 
  
•   Click on OK. New role has been created.

• System Level (System roles)
• System Administrator : 

S     System Administrator role is predefined role which is mainly used managing the reports. Job scheduling, Report execution (RSEXEC role under Reportserver database) subscriptions on system level and also able to execute the role without publishing the reports into report server. The below diagram shows the list of permission that will be available to System administrator by default.

  

S    Similarly System user permission also can view by right click on the System user.

S   Custom roles also can be created from the list of above permissions. Its similar to the above item level customer roles.

    Report server database and system table details found here

 

Import Flat file using Import an Export Wizard in SSIS(SCREEN SHOTS)

Import Flat file data into SQL Server using Import and Export Wizard in SSIS.

Right Click on SSIS Packages

Click on SSIS Import and Export Wizard

Click on next. Select Data source as flat file source. Click on browse and select the flat file path.

Select columns.

Select Advance. You can rename the columns, data type, output column width...

Select preview to preview the data

Choose destination as SQLserver native Client. Select database .default database is master.

Click on next

Click on next and Click Finish

Click on control flow .By default it will create table using Execute SQL task.if table already exist remove the CONTROL.

Click on dataflow.

Execute the package.

Verify in database whether data loaded successfully