SSIS Interview Questions - Part 4

Q:  How do you eliminate quotes from being uploaded from a flat file to SQL Server?
This can be done using Text Qualifier property. In the SSIS package on the Flat File Connection Manager Editor, enter quotes into the Text qualifier field then preview the data to ensure the quotes are not included.

Q: What is Data Viewer and what are the different types of Data Viewers in SSIS?
A Data Viewer allows viewing data at a point of time at run-time. If data viewer is placed before and after the Aggregate transform, we can see data flowing to the transformation at the run-time and how it looks like after the transformation occurred.
The different types of data viewers are:

• Grid
• Histogram
• Scatter Plot
• Column Chart.

Q: What are variables and what is variable scope?
A variable is used to store values. There are basically two types of variables:

• System Variable: (like ErrorCode, ErrorDescription, PackageName etc) whose values you can use but cannot change.
• User Variable: which you create, assign values and read as needed. A variable can hold a value of the data type you have chosen when you defined the variable.

Variables can have a different scope depending on where it was defined. For example you can have package level variables which are accessible to all the tasks in the package and there could also be container level variables which are accessible only to those tasks that are within the container.

Q: Difference between Union All and Merge Join?

• Merge transformation can accept only two Inputs whereas Union All can take more than two Inputs
• Data has to be sorted before Merge Transformation whereas Union All doesn't have any condition like that.

Q: Can we add our custom code in SSIS?
We can customize SSIS through code by using Script Task. The main purpose of this task is to control the flow of the package. This is very useful in the scenario where the functionality you want to implement is not available in existing control flow item.
To add your own code:-

• In Control Flow Tab, drag and drop Script Task from Toolbox.
• Double click on Script Task to open and select edit to open Script Task Editor.
• In Script Task Editor, there are 3 main properties
• General – Here you can specify name and description
• Script – through this we can add our code by clicking on Design Script button. The scripting language present is Net only.
• Expression

Q What is Ignore Failure option in SSIS?
In Ignore Failure option, the error will be ignored and the data row will be directed to continue on the next transformation. Let’s say you have some JUNK data(wrong type of data or JUNK data) flowing from source, then using this option in SSIS we can REDIRECT the junk data records to another transformation instead of FAILING the package. This helps to MOVE only valid data to destination and JUNK can be captured into separate file.

Q: What is a breakpoint in SSIS? How is it setup? How do you disable it?
A breakpoint is a stopping point in the code. The breakpoint can give the Developer\DBA an
opportunity to review the status of the data, variables and the overall status of the SSIS package. 10 unique conditions exist for each breakpoint.
Breakpoints are setup in BIDS. In BIDS, navigate to the control flow interface. Right click on the object where you want to set the breakpoint and select the ‘Edit Breakpoints…’ option.

Q: Error Handling in SSIS?
An error handler allows us to create flows to handle errors in the package in quite an easy way. Through event handler tab, we can name the event on which we want to handle errors and the task that needs to be performed when such an error arises. We can also add sending mail functionality in event of any error through SMTP Task in Event handler. This is quite useful in event of any failure in office non-working hours. In Data flow, we can handle errors for each connection through following failure path or red arrow.

Q: How to log SSIS Executions?
SSIS includes logging features that write log entries when run-time events occur and can also write custom messages. This is not enabled by default. Integration Services supports a diverse set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files. Logs are associated with packages and are configured at the package level. Each task or container in a package can log information to any package log. The tasks and containers in a package can be enabled for logging even if the package itself is not.

Q: Can you explain how to setup a checkpoint file in SSIS?
The following items need to be configured on the properties tab for SSIS package:
CheckpointFileName: Specify the full path to the Checkpoint file that the package uses to save the value of package variables and log completed tasks. Rather than using a hard-coded path as shown above, it’s a good idea to use an expression that concatenates a path defined in a package variable and the package name.

• CheckpointUsage: Determines if/how checkpoints are used. Choose from these options: Never(default), IfExists, or Always. Never indicates that you are not using Checkpoints. IfExists is the typical setting and implements the restart at the point of failure behavior. If a Checkpoint file is found it is used to restore package variable values and restart at the point of failure. If a Checkpoint file is not found the package starts execution with the first task. The Always choice raises an error if the Checkpoint file does not exist.
• SaveCheckpoints: Choose from these options: True or False (default). You must select True to implement the Checkpoint behavior.

Q: What are the different values you can set for CheckpointUsage property ?
There are three values, which describe how a checkpoint file is used during package execution:

• Never: The package will not use a checkpoint file and therefore will never restart.
• If Exists: If a checkpoint file exists in the place you specified for the CheckpointFilename property, then it will be used, and the package will restart according to the checkpoints written.
• Always: The package will always use a checkpoint file to restart, and if one does not exist, the package will fail.

Q: How would you restart package from previous failure point?What are Checkpoints and how can we implement in SSIS?
When a package is configured to use checkpoints, information about package execution is written to a checkpoint file. When the failed package is rerun, the checkpoint file is used to restart the package from the point of failure. If the package runs successfully, the checkpoint file is deleted, and then re-created the next time that the package is run.

Q: How do you deploy SSIS packages?

• Through Manifest
• Create deployment utility by setting its propery as true .
• It will be created in the bin folder of the solution as soon as package is build.
• Copy all the files in the utility and use manifest file to deply it on the Prod.
• Using DtsExec.exe utility
• Import Package directly in MSDB from SSMS by logging in Integration Services.

Q Where are SSIS package stored in the SQL Server?
MSDB.sysdtspackages90 stores the actual content and ssydtscategories, sysdtslog90, sysdtspackagefolders90, sysdtspackagelog, sysdtssteplog, and sysdtstasklog do the supporting roles.

Q How would you schedule a SSIS packages?
Using SQL Server Agent. Read about Scheduling a job on Sql server Agent

Q Can you name five of the Perfmon counters for SSIS and the value they provide?

• SQLServer:SSIS Service
• SSIS Package Instances
• SQLServer:SSIS Pipeline
• BLOB bytes read
• BLOB bytes written
• BLOB files in use
• Buffer memory
• Buffers in use
• Buffers spooled
• Flat buffer memory
• Flat buffers in use
• Private buffer memory
• Private buffers in use
• Rows read
• Rows written

<< Prev

SSIS Interview Questions - Part 3

Q: Explain Aggregate Transformation in SSIS?
It aggregates data, similar you do in applying T-SQL functions like Group By, Min, Max, Avg, and Count. For example you get total quantity and Total line item for each product in Aggregate Transformation Editor. First you determine input columns, then output column name in Output Alias table in data-grid, and also operations for each Output Alias in Operation columns of the same data-grid.
Some of operation functions listed below :

• Group By
• Average
• Count
• Count Distinct : count distinct and non null column value
• Min
• Max
• Sum

Step by Step Tutorial on Aggregate Transformation in SSIS 

Q: Explain Conditional Split transformation in SSIS?
This is just like IF condition which checks for the given condition and based on the condition evaluation, the output will be sent to the appropriate OUTPUT path. It has ONE input and MANY outputs. Conditional Split transformation is used to send paths to different outputs based on some conditions.

Step by Step Tutorial on Conditional Transformation in SSIS

Q: Explain Data Conversion Transformation in SSIS?
This component does conversion data type, similar to T-SQL function CAST or CONVERT. It is used to convert the data from one type to another.

Data Conversion Transformation in detail with Example

Q: Explain Derived column Transformation?
Derived column creates new column or put manipulation of several columns into new column. You can directly copy existing or create a new column using more than one column also.

Step by Step Tutorial on Derived Column Transformation in SSIS

Q: Explain Audit Transformation in SSIS?
It allows you to add auditing information as required in auditing world specified by HIPPA and Sarbanes-Oxley (SOX). Auditing options that you can add to transformed data through this transformation are :
Execution of Instance GUID: ID of execution instance of the package

• PackageID: ID of the package
• PackageName
• VersionID: GUID version of the package
• Execution StartTime
• MachineName
• UserName
• TaskName
• TaskID: uniqueidentifier type of the data flow task that contains audit transformation.

Q: Explain Character Map Transformation in SSIS?
It transforms some character. It gives options whether output result will override the existing column or add to new column. If you define it as new column, specify new column name. Operations available here are:

• Uppercase
• Lowercase
• Byte reversal
• Hiragana
• Katakana
• Half width
• Full width
• Linguistic casing
• Simplified Chinese
• Traditional Chinese

Q: Explain Copy column Transformation in SSIS?
This component simply copies a column to another new column. Just like ALIAS Column in T-SQL.

Q: Explain Merge Transformation in SSIS?
Merge transformation merges two paths into single path. It is useful when you want to break out data into path that handles errors after the errors are handled, the data are merge back into downstream or you want to merge 2 data sources. It is similar with Union All transformation, but Merge has some restrictions :

• Data should be in sorted order
• Data type , data length and other meta data attribute must be similar before merged.

Q: Explain Merge Join Transformation in SSIS?
Merge Join transformation will merge output from 2 inputs and doing INNER or OUTER join on the data. But if you the data come from 1 OLE DB data source, it is better you join through SQL query rather than using Merge Join transformation. Merge Join is intended to join 2 different data source.

Q: Explain Multicast Transformation in SSIS?
This transformation sends output to multiple output paths with no conditional as Conditional Split does. Takes ONE Input and makes the COPY of data and passes the same data through many outputs. In simple Give one input and take many outputs of the same data.

Q: Explain Percentage and row sampling Transformations in SSIS?
This transformation will take data from source and randomly sampling data. It gives you 2 outputs. First is selected data and second one is unselected data. It is used in situation where you train data mining model. These two are used to take the SAMPLE of data from the input data.

Q: Explain Sort Transformation in SSIS?
This component sorts the data similar to T-SQL command ORDER BY.

Q: Explain Union all Transformation in SSIS?
It works in opposite way to Merge transformation. It can take output from more than 2 input paths and combines into single output path.

<< Prev Next >>

SSIS Interview Questions - Part 2

Q: What is the function of Event handlers tab in SSIS?
On the Event handlers tab, workflows can be configured to respond to package events.
For example, we can configure Work Flow when any task fails, stops or starts ..

Q: What is the function of Package explorer tab in SSIS?
This tab provides an explorer view of the package. You can see what is happening in the package. The Package is a container at the top of the hierarchy.

Q:  Can you name 5 or more of the native SSIS connection managers?

• OLE DB connection – Used to connect to any data source requiring an OLE DB connection (i.e.,SQL Server 2000). OLE DB Connection Manager Example
• Flat file connection – Used to make a connection to a single file in the File System. Required for reading information from a File System flat file. Tutorial: Import Flat File to SQL Server
• ADO.Net connection – Uses the .Net Provider to make a connection to SQL Server 2005 or other connection exposed through managed code (like C#) in a custom task
• Analysis Services connection – Used to make a connection to an Analysis Services database or project. Required for the Analysis Services DDL Task and Analysis Services Processing Task
• File connection – Used to reference a file or folder. The options are to either use or create a file or folder
• Excel

Q: What are the different types of data sources available in SSIS?

• ADO NET Source
• Data Reader Source
• Excel Source
• Flat file Source: For Example, refer Step 3 in the tutorial
• ODBC Source
• OLE DB Source: For Example, refer Step 5 in the tutroial
• Raw File Source
• XML Source

Q: What are the different types of data destinations available in SSIS?

• ADO NET Destination
• Data Reader Destination
• Excel Destination
• Flat File Destination: For Example, refer Step 7 in the tutorial
• ODBC Destination
• OLE DB Destination
• Raw File Destination
• Recordset Destination
• SQL Server Destination: For Example, refer Step 4 in the tutorial

Q:What is a container and how many types of containers are there?
A container is a logical grouping of tasks which allows you to manage the scope of the tasks together. These are the types of containers in SSIS:

• Sequence Container - Used for grouping logically related tasks together
• For Loop Container - Used when you want to have repeating flow in package
• For Each Loop Container - Used for enumerating each object in a collection; for example a record set or a list of files.

Apart from the above mentioned containers, there is one more container called the Task Host Container which is not visible from the IDE, but every task is contained in it (the default container for all the tasks).

Q: What are the possible locations to save SSIS package?
You can save a package wherever you want.

• SQL Server
• Package Store
• File System

Q: How to provide security to packages?
We can provide security in two ways

• Package encryption
• Password protection.

Q:  How do we convert data type in SSIS?
The Data Conversion Transformation in SSIS converts the data type of an input column to a different data type.
Refer this tutorial where Data Conversion Transformation is used in a Real Time Example
Import Flat File to SQL Server

Q: What is the use of Bulk Insert Task in SSIS?
Bulk Insert Task is used to upload large amount of data from flat files into Sql Server. It supports only OLE DB connections for destination database.

Q: How to quickly load data into sql server table?
Fast Load option. This option is not set by default so most developers know this answer as otherwise the load is very slow.

Q: What are the Transformations available in SSIS?

• Aggregate: It applies aggregate functions to Record Sets to produce new output records from aggregated values. Example
• Conditional Split:  Separates available input into separate output pipelines based on Boolean Expressions configured for each output. Example
• Data Conversion: Converts columns data types from one to another type. It stands for Explicit Column Conversion. Example
• Derived Column:  Create a new (computed) column from given expressions. Example
• Lookup: Performs the lookup (searching) of a given reference object set against a data source. It is used for exact matches only.
• Merge: Merges two sorted data sets into a single data set into a single data flow.
• Merge Join: Merges two data sets into a single data-set using a join junction.
• Multicast: Sends a copy of supplied Data Source onto multiple Destinations.
• Row Count: Stores the resulting row count from the data flow / transformation into a variable.
• Union All: Merge multiple data sets into a single data-set.
• Audit: Adds Package and Task level Metadata - such as Machine Name, Execution Instance, Package Name, Package ID, etc..
• Character Map: Performs SQL Server column level string operations such as changing data from lower case to upper case.
• Copy Column: Add a copy of column to the output we can later transform the copy keeping the original for auditing.
• Data Mining Query: Used to perform data mining query against analysis services and manage Predictions Graphs and Controls.
• Export Column: Used to export a Image specific column from the database to a flat file.
• Fuzzy Grouping: Used for data cleansing by finding rows that are likely duplicates.
• Fuzzy Lookup: Used for Pattern Matching and Ranking based on fuzzy logic.
• Import Column: Reads image specific column from database onto a flat file.
• Row Sampling: Captures sample data by using a row count of the total rows in data-flow specified by rows or percentage.
• Pivot: Used for Normalization of data sources to reduce anomalies by converting rows into columns
• Unpivot: Used for de-normalize the data structure by converts columns into rows in case of building Data Warehouses.

<< Prev Next >>

SSIS Interview Questions - Part 1

Q: What is SSIS?
SSIS was first introduced in SQL Server 2005, which was the next generation of SQL Server software after SQL Server 2000. SSIS is a form of ETL (Extraction, Transformation and Load) used to perform operations like loading the data performing transformations/caluculations on data to define workflow of the process flow. The SSIS engine automation perform many data maintenance tasks, so you can update data without manually firing procedures and imports.

Q: What are the tools associated with SSIS?

• Business Intelligence Development Studio (BIDS):  to work with Development of SSIS Projects.
• SQL Server Management Studio (SSMS): to manage the SSIS Packages and Projects.

Q: How is SSIS different from DTS?
Older versions of SQL Server used DTS, which was similar to SSIS. DTS let you create steps that you would then assign a priority order. With SSIS, you can separate data from work flow, and SSIS has significantly better performance than older DTS packages. While performance isn’t always an issue when running jobs during off-peak hours, it’s a problem when you must run jobs during normal business hours. You can run SSIS during business hours without too much performance degradation.

Q: What is a package?
A discrete executable unit of work composed of a collection of control flow and other objects, including data sources, transformations, process sequence, and rules, errors and event handling, and data destinations.

Q: Different components in SSIS package?

• Control flow
• Data flow
• Event handler
• Package explorer

Q: What is Control Flow?
A Control Flow consists of one or more tasks and containers that execute when the package runs. To control order or define the conditions for running the next task or container in the package control flow, we use Precedence Constraints to connect the tasks and containers in a package. A subset of tasks and containers can also be grouped and run repeatedly as a unit within the package control flow. SQL Server Integration Services (SSIS) provides three different types of control flow elements:

• Containers that provide structures in packages
• Tasks that provide functionality
• Precedence Constraints that connect the executable, containers, and tasks into an ordered control flow.

Q: What is a Precedence Constraint and what types of Precedence Constraint are there?
SSIS allows you to place as many as tasks you want to be placed in control flow. You can connect all these tasks using connectors called Precedence Constraints. Precedence Constraints allow you to define the logical sequence of tasks in the order they should be executed. You can also specify a condition to be evaluated before the next task in the flow is executed.
Types of precedence constraints and the condition could be either a constraint, an expression or both

• Success: Next task will be executed only when the last task completed successfully
• Failure:  Next task will be executed only when the last task failed
• Complete: Next task will be executed no matter the last task was completed or failed.

Q:  Which are the different types of Control Flow components in SSIS?

The different types of Control Flow components are:

• Data Flow Tasks
• SQL Server Tasks
• Data Preparation Tasks
• Work flow Tasks
• Scripting Tasks
• Analysis Services Tasks
• Maintenance Tasks
• Containers.

Q: What is the data flow?
Data flow is nothing but the flow of data from the corresponding sources to the referred destinations. In this process, the data transformations make changes to the data to make it ready for the data warehouse.

Q:  What are the different types of Data flow components in SSIS?

• Sources
• Transformations
• Destinations

Next >>

jQuery Interview Questions - Part 3

Q: What is the use of jquery .each() function?
The $.each() function is used to iterate over a jQuery object. The $.each() function can be used to iterate over any collection, whether it is an object or an array.

Q: What is the difference between jquery.size() and jquery.length?
jQuery .size() method returns number of element in the object. But it is not preferred to use the size() method as jQuery provide .length property and which does the same thing. But the .length property is preferred because it does not have the overhead of a function call.

Q: What is the difference between $('div') and $('') in jQuery?

• $(''): This creates a new div element. However this is not added to DOM tree unless you don't append it to any DOM element.
• $('div'): This selects all the div element present on the page.

Q: What is the difference between parent() and parents() methods in jQuery?
The basic difference is the parent() function travels only one level in the DOM tree, where parents() function search through the whole DOM tree.

Q: What is the difference between .empty(), .remove() and .detach() methods in jQuery?
All these methods .empty(), .remove() and .detach() are used for removing elements from DOM but they all are different.

• .empty(): This method removes all the child element of the matched element where remove() method removes set of matched elements from DOM.
• .remove(): Use .remove() when you want to remove the element itself, as well as everything inside it. In addition to the elements themselves, all bound events and jQuery data associated with the elements are removed.
• .detach(): This method is the same as .remove(), except that .detach() keeps all jQuery data associated with the removed elements. This method is useful when removed elements are to be reinserted into the DOM at a later time.

Q: Explain .bind() vs .live() vs .delegate() vs .on()
All these 4 jQuery methods are used for attaching events to selectors or elements. But they all are different from each other.

• .bind(): This is the easiest and quick method to bind events. But the issue with bind() is that it doesn't work for elements added dynamically that matches the same selector. bind() only attach events to the current elements not future element. Above that it also has performance issues when dealing with a large selection.
• .live(): This method overcomes the disadvantage of bind(). It works for dynamically added elements or future elements. Because of its poor performance on large pages, this method is deprecated as of jQuery 1.7 and you should stop using it. Chaining is not properly supported using this method.
• .delegate(): The .delegate() method behaves in a similar fashion to the .live() method, but instead of attaching the selector/event information to the document, you can choose where it is anchored and it also supports chaining.
• .on(): Since live was deprecated with 1.7, so new method was introduced named ".on()". This method provides all the goodness of previous 3 methods and it brings uniformity for attaching event handlers.

Q: How can we give face effect in jQuery?
In jQuery we have three methods to give the fade effect to elements:
fadeIn, fadeOut and fadeTo
This methods change the opacity of element with animation.
Syntax

$(selector).fadeIn(speed,callback)
$(selector).fadeOut(speed,callback)
$(selector).fadeTo(speed,opacity,callback)

• speed can be one of following values : “slow”, “fast”, “normal” or milliseconds
• opacity specify the value that allows the fading to given opacity.
• callback is the function which we want to run once the fading effect is complete.

Example

$("clickme").click(function(){
   $("mydiv").fadeTo("slow",0.50);
});

$("clickme").click(function(){
   $("mydiv").fadeOut(3000);
});

Q: Explain the animate function.
The animate function is used to apply the custom animation effect to elements.

Syntax

$(selector).animate({params}, [duration], [easing], [callback])

• param defines the CSS properties on which you want to apply the animation.
• duration: specify how long the animation will run. It can be one of following values : “slow”, “fast”, “normal” or milliseconds
• easing is the string which specify the function for the transition.
• callback: is the function which we want to run once the animation effect is complete.

Following is the jQuery to animate opacity, left offset, and height of the mydiv element

$('# clickToAnimate’).click(function(){
   $('#book').animate({
      opacity: 0.30,
      left: '+=20',
      height: 'toggle'
   }, 3000, function() {
      // run after the animation complete.
   });
});

Q: Explain width() vs css(‘width’).
In jQuery, there are two way to change the width of an element.
One way is using .css(‘width’) and other way is using .width().
Syntax

$(‘#mydiv’).css(‘width’,’300px’);
$(‘#mydiv’).width(100);

The difference in .css(‘width’) and .width() is the data type of value we specify or return from the both functions.
In .css(‘width’) we have to add “px” in the width value while in .width() we don’t have to add.
When you want to get the width of “mydiv” element then .css(‘width’) will return ‘300px’ while .width() will return only integer value 300.

Q: What is the use of jQuery.data()?
jQuery.data() is used to set/return arbitrary data to/from an element.
Syntax

jQuery.data(element, key, value)

• element is the DOM element to which the data is associated.
• key is an arbitrary name of the piece of data.
• value is value of the specified key.

Suppose we want to set the data for a span element:

jQuery.data(span, “item”, { val1: 10, val2: "myitem" });

If we want to retrieve the data related to div element and set it to label’s data:

$("label:val1").text(jQuery.data(div, "item").val1);
$("label:val2").text(jQuery.data(div, "item").val2);

<< Prev

Home

jQuery Interview Questions - Part 2

Q: Name some of the methods of JQuery used to provide effects?
Some of the common methods are :

• Show() 
• Hide() 
• Toggle() 
• FadeIn() 
• FadeOut() 

Q: What is CDN?
CDN Stands for Content Distribution Network or also called Content Delivery Network is a group of computers placed at various points connected with network containing copies of data files to maximize bandwidth in accessing the data. In CDN a client access a copy of data nearer to the client location rather than all clients accessing from the one particular server. This helps to achieve better performance of data retrieval by client.

There are two leading CDNs available that hosts jQuery files.
Microsoft - To load jQuery from Microsoft AJAX CDN

<script type="text/javascript" language="Javascript" src="http://ajax.aspnetcdn.com/ajax/jquery/jquery-1.4.1.min.js">

Google - To load jQuery from Google Libraries API

<script type="text/javascript" language="Javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js">

Q: How JavaScript and jQuery are different?
JavaScript is a language While jQuery is a library built in the JavaScript language that helps to use the JavaScript language.

Q: Can we have multiple document.ready() function on the same page?
Yes. We can have any number of document.ready() function on the same page.

Q: What is jQuery.noConflict?
As other client side libraries like MooTools, Prototype can be used with jQuery and they also use $() as their global function and to define variables. This situation creates conflict as $() is used by jQuery and other library as their global function. To overcome from such situations, jQuery has introduced jQuery.noConflict().

jQuery.noConflict();
// Use jQuery via jQuery(...)
jQuery(document).ready(function(){
   jQuery("div").hide();
});

You can also use your own specific character in the place of $ sign in jQuery.

var $j = jQuery.noConflict();
// Use jQuery via jQuery(...)
$j(document).ready(function(){
   $j("div").hide();
});

Q: Is there any difference between body onload() and document.ready() function?
document.ready() function is different from body onload() function for the following reasons:

• We can have more than one document.ready() function in a page where we can have only one body onload function.
• document.ready() function is called as soon as DOM is loaded where body.onload() function is called when everything gets loaded on the page that includes DOM, images and all associated resources of the page.

Q: What is the difference between .js and .min.js?
jQuery library comes in 2 different versions Development and Production/Deployment. The deployment version is also known as minified version. So .min.js is basically the minified version of jQuery library file. Both the files are same as far as functionality is concerned. but .min.js is quite small in size so it loads quickly and saves bandwidth

Q: What are the fastest selectors in jQuery?
ID and element selectors are the fastest selectors in jQuery.

Q: What are the slow selectors in jQuery?
class selectors are the slow compare to ID and element.

Q: How do you check if an element exists or not in jQuery?
Using jQuery length property, we can ensure whether element exists or not.

$(document).ready(function(){
    if ($('#element').length > 0){
       //Element exists
  }
});

<< Prev

Home

Next >>

SSRS Interview Questions & Answers (Part-8)

Q: What is report subscription?
Subscriptions are standing requests to deliver report data to requested recipients. Once the report is being subscribed and subscriber will get updates from report server on scheduled interval.

Q: Can you use a stored procedure to provide data to an SSRS report?
Yes, you can use a stored procedure. However, your stored procedure should return only a single result set. If it returns multiple result sets, only the first one is used for the report dataset.

Q: How to send a SSRS report from SSIS?
Often there is a requirement to be able to send a SSRS report in Excel, PDF or another format to different users from a SSIS package one it has finished performing a data load. In order to do this, first you need to create a subscription to the report. You can create a SSRS report subscription from Report Manager. At the report subscription you can mention the report format and the email address of the recipient. When you create a schedule for the SSRS report, a SQL Server Agent Job will be created. From the SSIS, by using sp_start_job and passing the relevant job name you can execute the SSRS report subscription.

Q: You want to use BIDS to deploy a report to a different server than the one you chose in the Report Wizard. How can you change the server URL?
You can right-click the project in Solution Explorer and then change the Target-Server URL property.

Q: Can we deploy SSRS reports on our personal website?
Your reports can only be deployed on a reporting services site. Your only option for viewing them from other sites is an HTTP link. Some tools, like SharePoint offer controls allowing you to view reports in the context of the other websites, but the report is still deployed to and hosted from reporting services.

<< Prev

Explore a world of possibilities with online tools , enhancing productivity and creativity for developers and designers alike.

SSRS Interview Questions & Answers (Part-7)

Q: What are the different types of reports in SSRS?
The types of reports in SSRS are given below:

• Parameterized reports
• Linked reports
• Snapshot reports
• Cached reports
• Ad hoc reports
• Clickthrough reports
• Drilldown reports
• Drillthrough reports
• Subreports

Q: What is Parameterized Reports in SSRS ?
A parameterized report uses input values to complete report or data processing. With a parameterized report, you can vary the output of a report based on values that are set when the report runs. Parameterized reports are frequently used for drill-through reports, linked reports, and sub-reports, connecting and filtering reports with related data.

Q: What is Linked Report?
A linked report is a report server item that provides an access point to an existing report. Conceptually, it is similar to a program shortcut that you use to run a program or open a file.
A linked report is derived from an existing report and retains the original's report definition. A linked report always inherits report layout and data source properties of the original report. All other properties and settings can be different from those of the original report, including security, parameters, location, subscriptions, and schedules.
You can create a linked report on the report server when you want to create additional versions of an existing report. For example, you could use a single regional sales report to create region-specific reports for all of your sales territories.
Although linked reports are typically based on parameterized reports, a parameterized report is not required. You can create linked reports whenever you want to deploy an existing report with different settings

Q: What is Snapshot Report?
A report snapshot is a report that contains layout information and query results that were retrieved at a specific point in time. Unlike on-demand reports, which get up-to-date query results when you select the report, report snapshots are processed on a schedule and then saved to a report server. When you select a report snapshot for viewing, the report server retrieves the stored report from the report server database and shows the data and layout that were current for the report at the time the snapshot was created.
Report snapshots are not saved in a particular rendering format. Instead, report snapshots are rendered in a final viewing format (such as HTML) only when a user or an application requests it. Deferred rendering makes a snapshot portable. The report can be rendered in the correct format for the requesting device or Web browser.
Report snapshots serve three purposes:

• Report history: By creating a series of report snapshots, you can build a history of a report that shows how data changes over time.
• Consistency: Use report snapshots when you want to provide consistent results for multiple users who must work with identical sets of data. With volatile data, an on-demand report can produce different results from one minute to the next. A report snapshot, by contrast, allows you to make valid comparisons against other reports or analytical tools that contain data from the same point in time.
• Performance: By scheduling large reports to run during off-peak hours, you can reduce processing impact on the report server during core business hours.

Q: What is Cached Report?
A cached report is a saved copy of a processed report. Cached reports are used to improve performance by reducing the number of processing requests to the report processor and by reducing the time required to retrieve large reports. They have a mandatory expiration period, usually in minutes.

Q: Whar are Click-through Reports?
A click-through report is a report that displays related data from a report model when you click the interactive data contained within your model-based report. These reports are generated by the report server based on the information contained within the report model. The person who created the model determines which fields are interactive and which fields are returned when a click-through report is opened. These field settings cannot be changed in the report authoring tools. Click-through reports are auto-generated. However, you can create an alternative customized report to the model for interactive data items that is displayed instead. The custom report is a standard Reporting Services report.

Q: What are Drill-down Reports?
Drill-down reports initially hide complexity and enable the user to toggle conditionally hidden report items to control how much detail data they want to see. Drill-down reports must retrieve all possible data that can be shown in the report.
For reports with large amounts of data, consider drill-through reports instead.

Q: What are Drill-through Reports?
Drill-through reports are standard reports that are accessed through a hyperlink on a text box in the original report. Drill-through reports work with a main report and are the target of a drillthrough action for a report item such as placeholder text or a chart. The main report displays summary information, for example in a matrix or chart. Actions defined in the matrix or chart provide drill-through links to reports that display greater details based on the aggregate in the main report. Drill-through reports can be filtered by parameters, but they do not have to be. Drillthrough reports differ from sub-reports in that the report does not display within the original report, but opens separately. They differ from click-through reports in that they are not auto-generated from the data source, but are instead custom reports that are saved on the report server. They differ from drill-down reports in that they retrieve the report data only for the specified parameters or for the dataset query.

Q: What is Subreport?
A subreport is a report that displays another report inside the body of a main report. Conceptually, a subreport is similar to a frame in a Web page. It is used to embed a report within a report. Any report can be used as a subreport. The subreport can use different data sources than the main report. The report that the subreport displays is stored on a report server, usually in the same folder as the parent report. You can set up the parent report to pass parameters to the subreport.
Although a subreport can be repeated within data regions using a parameter to filter data in each instance of the subreport, subreports are typically used with a main report as a briefing book or as a container for a collection of related reports.
Step by Step Example of Sub Report...
For reports with many instances of subreports, consider using drillthrough reports instead.

Q: What is Data Set in report?
Data set is a set of data which we want to show in report. Data source is the source of data from where we are getting this data (database server name, database name, connection string).

<< Prev Next >>

SSRS Interview Questions & Answers (Part-6)

Q: Can we run Reporting Services with SQL Server express edition, which is a free version of SQL Server?
Yes we can. SQL Server Express Edition with Advanced Services support Reporting Services. These is the free version.

Q: What are the limitations in SSRS on SQL Server express edition?
Microsoft offers reporting services free as part of SQL Server Express with Advance Services edition. But it has the following limitations:

• Management Studio cannot be used to administer report server
• Report Models will not be available
• Report Builder is not available
• Caching, History and Delivery of Report is not available.
• SQL Server agent is not available
• No scheduling is possible
• Remote server database is not available for Report Data Source (Local SQL Server is a only option,)
• We cannot store the report server database on a remote server (it has to be local only)
• Reports can be rendered only in Excel, PDF, Image formats only
• Reporting Services will not be able to use more than 1 GB of RAM
• No Subscriptions (Standard and Data Driven) can be made
• Can not be integrated with Share Point
• Can not implement Role based security
• Only named instances is supported
• Scale-out Report Servers will not be available

Q: What are the tools available in market as an Alternative to SQL Server Reporting Services?

• Non-Open Source:
• Actuate
• Hyperion (BRIO)
• SIEBEL-CRM
• BusinessObjects
• Oracle Express OLAP
• Qlikview
• Cognos
• Informatica Power Analyzer
• Proclarity
• IntelliView
• Dundas Chart for .NET
• MS-Excel
• SAS
• MicroStrategies
• Pentaho
• Open Source:
• Jasper Reports
• JFreeReport
• BIRT (Business Intelligence Reporting Tools)
• OpenReport
• DataVision
• Pentaho

Q: How to deploy the Report?
We can deploy SSRS report in three ways.

• Using Visual Studio: In visual studio we can directly deploy the report through Solution explorer by providing the report server URL in project properties at Target Server URL. As our choice this will deploy entire project or single report as.
• Using Report Server: We can directly go to the report server and deploy the report by browsing the report from the disk location of server.
• Creating the Utility: SQL server provides the facilities to Create a customize utility to deploy the report.

Q: What are the new features of SQL Server 2008 R2 reporting service?
The SQL Server 2008 R2 has introduced a lot of new features. Some of them are given below:

• New Report Types – Table, Matrix, List, Chart, and Sub report
• Some New Tools is added to report designer Toolbox
• Report Data Panel – built in page numbers
• Report Builder 3.0

Q: What are the new features of SQL Server 2012 reporting service?
The SQL Server 2012 has introduced a lot of new features. Some of them are given below:

• Power View – interactive data exploration
• SharePoint integration
• Introduction to Data Alerts
• SQL Server Data tool
• New rendering extensions (supports MS Office 2010)
• Project Crescent is being introduced

Q: How to backup SQL Server Reporting Services ?
There are mainly three things, which should be backed up as part of reporting services backup

• Report Server Databases, which can be backed up by SQL server backup and restore method.
• SQL Server Reporting Services Configuration, SQL Server Reporting Services Configuration is saved in config files, which can be copied as part of backup. look for other to know config files and there location.
• Encryption Keys backup, use SQL Server Reporting Services Configuration tool to backup symmetric keys.

Q: What is the web service used for reporting services?
Reporting Service Web Service used in SSRS. By accessing this web service you can access all report server component and also get the report deployed on report server.

Q: What is a cache in SSRS?
Report server can lay up a copy of processed report in a memory and return the copy when a user opens the report. This server memory is known as cache and the process is called caching.

Q: Can you always create a cache of a report?
No, you can create a cache of a report only when certain requirements, such as having credentials stored in the Report Server, are met.

<< Prev Next >>

SSRS Interview Questions & Answers (Part-5)

Q: What are the different Processing Modes offered by SSRS?

• Local Processing Mode: Processes reports on the client application.
• Remote Processing Mode: Processes on SQL Server Reporting Services Report Server.

Q: What is ReportServer and ReportServerTempDB ?
Reporting Services uses two SQL Server databases for storage by default, the databases are named ReportServer and ReportServerTempdb.
ReportServer is a main database, which store all internal configuration and report meta data whereas ReportServerTempdb is used to store temporary data, session information, and cached reports.

Q: What is encryption key?
Encryption keys are used by the report server so that items such as connection strings are maintained securely. These keys are required in case you want to perform restoration of report server databases

Q: How to backup encryption key?
Encryption Keys backup, use SQL Server Reporting Services Configuration tool to backup symmetric keys.

Q: What are the key configuration files for SQL Server Reporting Services?
Mostly all Configuration files located at Install Directory:
\Microsoft SQL Server\\Reporting Services\ReportServer and  ReportManager

• RSReportServer.config stores configuration settings for feature areas of the Report Server service: ReportManager, the Report Server Web service, and background processing.
• RSSrvPolicy.config stores code access security policies for the server extensions.
• RSMgrPolicy.config stores code access security policies for Report Manager
• ReportingServicesService.exe.config stores configuration settings that specify the trace levels and logging options for the Report Server service.
• RSReportDesigner.config contains settings for Report Designer and this file is located in the..\Program Files\Visual Studio 9.0\Common7\IDE\PrivateAssemblies
• RSPreviewPolicy.config stores server extensions used during report preview and this file is located in ..\Program Files \Microsoft SQL Server\100\Tools \ReportDesigner

Q: What is Report Builder?
Report Builder is a business-user, ad-hoc report design client that allows users to design reports based on the business terms (Report Builder model) they are familiar with, but without needing to understand database schemas or how to write SQL or MDX queries. Report Builder works with both SQL Server and Analysis Services data sources.

Q: In which SQL Server version report builder introduced?
Report builder introduced in SQL Server 2005. While creating or deploying report model project on report server you can get error or it might not get created. For this you need to check whether the service pack 22 is installed or not.

Q: How does Report Builder support Analysis Services cubes?
Report Builder supports relational SQL and Analysis Services data sources in SQL Server. To create a model for Analysis Services cube, go to Report Manager or Management Studio, create a data source for your Analysis Services database, and then select the Generate Model option to create the model.

Q: How do users use Report Builder with SQL Server data sources?
While models that provide access to SQL Server Analysis Services are automatically generated on the report server, the Report Builder Model Designer can be used to generate or modify the models that are built on top of SQL Server relational databases. These model-building projects are a new type of project within a Visual Studio–based development shell.

Q: How do I get Report Builder to generate a parameter that can be set by users viewing the report?
In the filter dialog box, click the name of the criteria that you would like to prompt the user for when viewing the report. For example, for the criteria Order Year=2000, click Order Year. Select the Prompt option in the drop-down list.

<< Prev Next >>