Error attaching MdxStepByStep.mdf for SQL Server 2008 R2

In This article I will show how to attach MDXStepByStep database. I have seen many people face problem when attaching the MDXStepByStep database using the attach_db.sql script.

When you execute attach_db.sql script it gives the below error.

Msg 5120, Level 16, State 101, Line 1

Unable to open the physical file "C:\Microsoft Press\MDX SBS\Setup\SQL Server\MdxStepByStep.mdf". Operating system error 5: "5(Access is denied.)".

Please follow the below steps to attach the MDXStepByStep database:

Step 1: Copy only MDXStepByStep.mdf file from “C:\Microsoft Press\MDX SBS\Setup\SQL Server” to “C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA”.

Step 2: Login to your SQL Server with your appropriate credentials, make sure your account must have permission to create database.

Step 3: Right Click on the Database and click on Attach, as shown below.

Step 4: Once you click on attach button it will open a new window. You have to click on add button and then select the MDXStepByStep.mdf file and click ok. You can find below the screenshot.

Step 5: You have added the MDXStepByStep.mdf file but MDXStepByStep.ldf file will be missing for this database because you didn’t copy that file in setp 1. In this step you have to remove the missing file.

Select MDXStepByStep.ldf file and click on remove button and click Ok as shown below.

 

Congratulations you have successfully attached the MDXStepByStep Database.

I hope you enjoyed the article don't forget to hit the Facebook Page Like Button. Please leave your comments below if there are any questions.

Sql Server Reporting Services (SSRS) Architecture

Reporting Services is one of the core component of MS SQL Server. Reporting Services is a server based reporting platform that provides reporting functionality to the user.Report Server is central component of Reporting Services. In this article we will try to understand how SSRS works in native mode deployment.

Reporting Services Report Server runs in one of two deployment modes:

1.       Native Mode

2.       Sharepoint Mode

You can’t switch Report Server mode from one to another  so user has to decide in which mode Reporting Services should be configured at the time of Reporting Services installation. Native mode is the default mode for Report server.

In Native mode Report Server is a stand alone application server that provides all viewing, processing and delivering of the reports and report model. Report Server is implemented as a Microsoft Windows Service called as Report Server.

Native mode reporting services Report Server 3 tier architecture is given below.

Report Server consists of a pair of Processing Engines and five special purposes of extensions which handle Authentication, Report Processing, Rendering, Data Processing and Delivery operation.

Let’s discuss each component from the above architecture.

Tier 3 (Client Application in Presentation layer)

User sends request to report server for Report Processing, Report Scheduling and Delivery etc from any of the client application present in presentation layer i.e Report Manager, Report Builder, Report Designer.

Tier 2 (Report Server Components)

1- Programmatic Interface:

Programmatic interface process all the requests sent to Report Server from the client application present in presentation layer. This includes requests from Report manager, Scheduling and Delivery Processor, Report Design Tools and Third party Tools. Programmatic Interface uses Internet Information Services (IIS) to receive the requests.

When a request arrives at the report server in the form of SOAP (Simple Object Access Protocol) and http request; Programmatic Interface interacts with the Report Server database in response to the request. When a report is requested the programmatic Interface initialize the Report Processor and when a Report Scheduling and Delivery is requested the programmatic Interface initialize the Scheduling and Delivery Processor.

2- Report Processor:

When a report is send from the presentation layer, it will reach to the programmatic layer. Programmatic layer will initialize the Report Processor. Report processor will retrieve the report definition or model information then combines layout information with the data from data processing extension and renders it in the requested format.

Authentication, Report Processing, Rendering , Data Processing and Delivery Extensions

The report server supports five types of extensions, authentication extensions, report processing extensions, rendering extensions, data processing extensions and delivery extensions. A report server requires at least one authentication extension, rendering extension and data processing extension. Delivery and custom report processing extensions are optional.

3- The Scheduling and Delivery Processor

The Scheduling and Delivery Processor processes reports triggered from a schedule, and delivers reports to target destinations.

Tier 1 (Data Layer)

1- Report Server Database:

Reporting Server Database is a SQL Server database. This is created at the time of Reporting Services configuration. It stores Reporting Services data such as Report Definitions, Report Metadata, Cached Reports and snapshots. It also stored security settings, encrypted data, scheduling -delivering and extension information and folder hierarchy. This data is accessed through Report Server. Report Server Database can provide internal storage information for a single reporting services or multiple report server.

2- Data Sources:

This is the data which user wants to represent in reports.

I hope you enjoyed the article don't forget to share on Facebook. Please leave your comments below if there are any questions.

Content reference is msdn.

Read and Export Excel data from nth Row in SSIS

This post shows how we can read and export data from excel starting from nth row.

In SSIS we can read excel data starting from any number of row. Considering a scenario we have a excel file like below screen and we need to read data of Row 2 to Row 15.

To do this I create an Excel Connection manager in my SSIS package. I created a Data Flow Task in the control tab. On Data Flow tab I have following components. 

Right Click on the Excel Source and select properties.

In the OpenRowset you will find the sheet name from which you are extracting the data. You have to make modification here to read the data from the nth row or range.

As you can see from the below Image I have already updated the OpenRowset value to

Sheet1$A2:A15

Now double click on the excel source and click on preview button, as you can see from the below Image data was read from the row 2 to row 15.

I hope you enjoyed the article don't forget to share on Facebook. Please leave your comments below if there are any questions.

Check IsNumeric() with Derived Column Transform in SSIS

In SSIS we don’t have ISNUMERIC () equivalent within the SSIS expression language. You can't use ISNUMERIC() in an SSIS transform, such as a Conditional Split Transform or a Derived Column Transform, that doesn't mean you can't check to see if a field is numeric using the SSIS expression language. Using script task we can check this, using SQL command in Excel ( SELECT F1 FROM [SHEET1$] WHERE ISNUMERIC(F1)=-1) source we can check this, but in this tutorial I will show how we can check if a field is numeric without script task.

Create a sample excel file as shown below

As you can see from the above data our column has both numeric and string data. Now I want to check which row is numeric.

As I said before, there is a way we can use a Derived Column Transform (or Conditional Split) to check if a field is numeric.

Steps:

1-      Create Excel source connection

2-      Drag a Derived Column

After dragging in a Derived Column Transform into your Data Flow Task, create a new column to be added as a new column to your data flow. Give it a meaningful name to the new column and use this expression:

(DT_I4)Column == (DT_I4) Column? 1 : 0

Then near the bottom of the Derived Column Transform Editor window, click Configure Error Output. You need to tell SSIS to ignore failure on Error, as seen here:

  

Optionally, you could choose to redirect rows that are not numeric to the Error output of the Derived Column and then handle those rows there.

3-      Add another derived column and add data viewer on it.

Package should look like this.

On Execution you should get the below output.

You can see that the rows that are not numeric have a NULL value for the IsNumeric field we created with the Derived Column. Rows that are numeric have a 1. This way it is easy for us to determine which rows of a certain field is numeric and which are not numeric by checking for NULL.

I hope you enjoyed the article don't forget to share on Facebook. Please leave your comments below if there are any questions.

My First Report in SSRS

In this article we will be creating our first SSRS report. I am using SSRS 2012 environment to walk you through the SSRS functionalities.

I will start from the very basics. Once we are comfortable with the tool we can skip these steps in coming articles.

Once you install SQL Server 2012 successfully, you get Microsoft SQL Server 2012 folder in your programs. You can find the snapshot below, Click on SQL Server Data Tools.

We will create a Solution and under that Solution we will create SQL Server Reporting Services Project. A Solution can have multiple different types of projects i.e. SSRS, SSIS and SSAS etc. Our Project will contain all the reports we create.

Please follow the below steps to create a solution and project.

 

Once you click on Project or you press Ctrl+Shift+N another window will open. In this new window you have to select your project type. As I am going to demonstrate SQL Server Reporting Services, so I will choose Reporting Services Template and then Report Server Project Business Intelligence. You have to give a proper Solution name with Project Name. You can find below the screen shot.

Once you successfully create your Solution and Project, by default it will be saved under C:\Users\YourUsername\Documents\Visual Studio 2010\Projects. In my case myusername is GG.

Solution file will have .sln file extension and project file will have .rptproj file extension.

Once you create a solution and project, you have to open Solution Explorer to create Report. If it is not open then you can open it either using ctrl+alt+L shortcut or go to View in the Menu bar and then click on Solution Explorer as shown below.

Once you follow any of the method, you will get Solution Explorer. As you can see from the below image our new SSRS project has three folders inside it.

Shared Data Sources –This folder contains list of all Shared Data Source Connections.

Shared Datasets – This folder contains list of all Shared Datasets.

Reports – This folder contains all SSRS reports.

In this article we will be creating a Report without Data source and Data sets. I will be creating another article in which we will first discuss about Data Sources, Data Sets and then create a report.

To add or create a new report into this project, please right click on Reports folder and then click on Add and then choose new item as shown below. You can also add a new item using Ctrl+Shift+A.

Once you follow any of the above method, you get a new window which gives you option to choose your new item. As we are creating a Report so we will choose Report from the available options. You can refer the below image. You have to give a proper Report name and click on add button present at the bottom.

Once you click on the add button your new report will be added to the project under Reports folder.

As you can see from the below image, a new report name as Report.rdl has been added into the SSRS Project (RHS). LHS we have Report Data and in the middle we have Report Body. Report body has two tabs Design and Preview. In the Design tab we design our report and preview is used to execute the report. 

To add an element in the report please right click on report body and then choose insert. Once your do mouse over on Insert, it gives you all available elements for reporting. You can refer below image for list of all available reporting elements.

Please Text Box from available reporting elements. Once you add Text Box it will be available in the report. You can drag & Drop, Increase and Decrease the Text Box size as per your need. This is the element which is used for labeling purpose. You can double click the Text Box and write whatever you want to present.

 To execute or preview the report please click on the Preview button.

You can export your report to any of the available option as shown below.

Congratulations you have successfully created and executed your first report in SSRS.