SQL Server Developer

Pareto Charts in SSRS

The purpose of a Pareto chart is to highlight the most important amongst a set of factors. For example, in quality control for a manufacturer, a Pareto chart can highlight the most common sources of defects and the highest occurring type of defect. The Pareto principle is also known as 80-20 rule, so for quality […]

Design A Hybrid Report in SSRS

Hybrid reports are the reports which have two types of graphs using different axes within the same graph . For example. In the above report, Product cost is on the right axis while Sales Amount Percentage is shown on the left axis, so that viewers can easily compare Product Cost vs Sales Amount Percentage. Below […]

How to unzip a File in SSIS?

For SSIS packages, most of the times you will get zip, rar or tar etc files and need to extract them during the extraction operation in SSIS. Let us see how we can do this directly from SSIS. You can use the Execute Process Task in the Control Flow task. In BIDS Simply drag and […]

SQL Server Reporting Server (SSRS) service is failing to start

After a server reboots the SQL Server Reporting Server (SSRS) service may fail to start and the following errors are logged in the event log. The SQL Server Reporting Services (MSSQLSERVER) service failed to start due to the following error: The service did not respond to the start or control request in a timely fashion. Timeout (30000 milliseconds) waiting […]

How to Import Excel 2007 Version to SQL Server using SSIS

Importing Excel files to SQL Server is a frequent task you need to carry out in your day to day life. Most probably you are using SSIS to import Excel files to SQL Server. If you are asked to import an Excel file, you can use Excel Source from the Data Flow Sources SQL Server […]

Users are able to connect to report manager but not able to connect to report server.

You need to register IIS before installing SQL Server Reporting Services. However, there can be situations where you need to re-register it.  In order to re-register ISI follow the steps below: 1. Open the Command Prompt 2. Change directory as follows: For 32-bit machines change to: <WindowsDir>Microsoft.NETFramework<version number> For 64-bit machines change to: <WindowsDirMicrosoft.NETFramework64<version number> […]

How to Display Server Name or IP Address in a Reporting Services Report

It is possible that users can receive miss-leading data when you are running several reporting severs. You may have a test and a production environment and you do not know from which server a report has been generated. An obvious way you can tackle this issue is by displaying the report server name in the report. […]

How to schedule SSAS Database backup?

SQL Server Analysis Services (SSAS) database backups can be taken using SQL Server Management Studio (SSMS). However, you cannot schedule a SSAS database backup from the SSAS backup option. To achieve this, you can create SQL Server Agent Job and schedule that job as per your requirement. 1.  Right click the Job folder of the SQL […]

SSIS Lookups are Case Sensitive

Lookup’s are a key component in SQL Server Integration Services (SSIS). Let’s say we want to lookup data from the personalookup table. In this example, we assume that we have a record in the personlookup table with the name jim. In our source we have JIM, jim and JiM. Ideally all of these records should match […]

Convert Number to Words in SSRS

A question that is often asked is how to convert a number to words in SSRS. For example 1 => One. Let’s take a look at an example: SELECT Sales.SalesPerson.SalesPersonID AS SalesPerson, SUM(Sales.SalesOrderDetail.OrderQty * Sales.SalesOrderDetail.UnitPrice) AS Amount FROM Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID INNER JOIN Sales.SalesPerson ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.SalesPersonID GROUP BY […]