Configure a Report Portal in SSRS

Refer Source Video: Configure a Report Portal in SSRS

Configure a Report Portal in SSRS

Refer Source Video: Configure a Report Portal in SSRS

1.

All Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> Reporting Services Configuration Manager

2.Next screen:

Reporting Services Configuration Connection.

Microsoft SQL Server 2008  Reporting Services 

Connect to a report Server Instance.

Server Name:  sv123

Report Server Instance: MSSQLSERVER

                                       REPORTSQLDEMO ->choose

3.Next Screen.

Reporting Services Configuration Manager: sv123\REPORTSQLDEMO

Microsoft SQL Server 2008 Report Services

Reporting Services Configuration Manager

Connect

sv123\REPORTSQLDEMO

Service Account

Web Service URL

Database

Report Manager URL

Email Settings

Execution Account

Encryption Keys

Scale-out Deployment

--Current Report Server--

SQL Server Instance : REPORTSQLDEMO

InstanceID: MSRS10.REPORTSQLDEMO

Edition: ENTERPRISE EDITION

Product version: 10.0.1600.22

Report Server Database Name:

Report Server Mode:

Report Service Status: Started

--Report Server Status --

-Use  Reporting Services Configuration Manager tool to define or modify Settings for the Report Server and Report Manager. If you

installed Reporting Services in files only mode, you must configure

the Web Service URL, the database, and Report Manager URL.

***
Service Account:
-Use built-in account: Network Service

Web Service URL:

Configure a URL used to access the Report Server.
Click advance to define multiple URLs for a single Report Server instance, or to specify additional parameters on the URL.

-Report Server Web Service is not configured. Default values have been provided to you. ...

Report Server Web Service Virtual Directory

Virtual Directory: ReportServer_REPORTSQLDEMO

Report Server Web Service Site Identification

IP Address: All Assigned(Recommended)

TCP Port: 80

SSL Certificate:

SSL Port:

Report Server Web Service URLS

URLS: http://sv123:80/ReportSer...

***
Database:

Report Server Database:

Reporting Services stores all report server content and application data in a database. Use this page to create or change the report server database or update database Connection Credentials.

Current Report Server Database

Click change database to select a different database or create a new database in native or SharePoint integrated mode.

SQL Server Name:
Database     Name:
Report Server Mode:

-Change Database

***
Current Report Server Database

Credential

The following credentials are used by the report server to connect to the report server database. Use the options below to choose a different account or update a password.

Credential:

Login:  

Password:

Change Credentials.

***
Report Server Database Configuration Wizard

-Create a new report server database
-Choose an existing report server database

Change Database ->Click on the button.

Choose a local or remote instance of a SQL Server Database Engine & specify credentials that have permission to connect to that Server.

Connect to the Database Server:

Server Name: sv123

Authentication Type: Current User - Integrated Security

Username: usr1234\Administrator

Password:

Test Connection <- 

***
Next Screen:

Database Name: ReportServer ( change: ReportServerDemo)

Temp Database Name: ReportServerDemoTemp

Language: English ( United States)

Report Server Mode: 

                                   Native Mode <- choose 
                                   Sharepoint Integrated Mode

***
Next Screen:

Specify the credentials of an existing account that the report server
will use to connect to the report server database. Permission to access the report server database will be automatically granted to
the account you specify.

Credentials:

Authentication Type: Service Credentials

Username: NT Authority\NetworkService

Password:

***
Next Screen: Summary

***
Next Screen: Progress & Finish

SQL: Top 3 records

SQL: Select Top 3 Records + Sum of Quantity

SQL-select top 3 values per group WITH CONDITION

How to select TOP N rows from a table for each group?

SELECT Pr1.model, Pr1.type, COUNT(*) num FROM Product Pr1 JOIN Product Pr2 ON Pr1.type = Pr2.type AND Pr1.model >= Pr2.model GROUP BY Pr1.type, Pr1.model HAVING COUNT (*) <= 3 ORDER BY type, model

How to get a list/result of best selling items

Assuming SQL Server, I might use:

SELECT TOP(5) ProductID, SUM(Quantity) AS TotalQuantity
FROM order_items
GROUP BY ProductID
ORDER BY SUM(Quantity) DESC;

This returns the top 5 best-selling Products.

---

 In (Select Top 3 [UnitsInStock] From Products Where _

[CategoryID]=[Categories].[CategoryID] Order By [UnitsInStock] Desc)

---

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Many to Many Relationships – Day 8 of 35

http://support.microsoft.com/kb/153747://support.microsoft.com/kb/153747CC: ACC: How to Create a Top N Values per Group QueryHow to Create a Top N Values per Group Query

CDC -Sql Server 2008

Introduction to Change Data Capture (CDC) in SQL Server 2008

SSIS - Invoking / Running / Controlling one SSIS package from another

Refer Source: Invoking / Running / Controlling one SSIS package from another

Individual packages: Flat File Source - > Ole DB Destination

To get the Parent Package Variable value: 

Configurations:

i.Create a variable var_dir

ii.Right Click: Choose "Package Configuration" : choose : Parent Package Variable: map to  var_dir

iii.Expression: concatenate var_dir + file name

Note:

i.In Parent Package: a variable is defined like: var_file_dir  c:\data\

where all the text files are there that need to be loaded

The Parent Package: Which is calling these 3 Packages.

i.3 variables are declared for 3 packages which is boolean and value "True"

This is used to: make the individual package "Disabled" in the beginning.

Configuration to disable: Expression below "Disable"

ii.Package first runs a script task and read the variable values containing just directory information.

iii.File Exist task checks if  file are there or not , if there then set the  boolean value "True" , False

, that way enables the packages to run.

Interactive SSIS package

Refer Source:Interactive SSIS package

Script Task:

i. Take user input of  Table Name. Assign it a variable defined.

Execute SQL Task:

i.

DECLARE @TableName VARCHAR(100)

SET @TableName = ?

Select ? = Count (*) FROM INFORMATION_SCHEMA_TABLES  WHERE TABLE_NAME = @TableName

GO

Execute SQL Task:

Parameter Mapping:

Variable Name         Direction   Data Type          Parameter  

User:TableName      Input         VARCHAR        0

User:ValidateName Output       LARGE_INT...   1

Constraint:

Validation done on the variable.

DECLARE @TableName VARCHAR(100)

SET @TableName = ?

DECLARE @Count  INT

DECLARE @SqlString NVARCHAR(1000)

   SET  @SqlString =  '  SELECT   @OutCount = COUNT(*) FROM  ' =@TableName

EXEC  SP_EXECUTESQL 

                             @SqlString

                             , N'@OutCount INT OUTPUT'

                             ,@OutCount = @ Count OUTPUT 

                             Select ? = @Count

SSIS Performance Tuning Tips and Tricks

SQL Server Integration Services SSIS Best Practices