Hot News »
Bagikan kepada teman!

SSIS Intreview Questions - Part 4

Penulis : Sandeep Mittal on Tuesday, July 28, 2015 | 7/28/2015 12:57:00 PM

Tuesday, July 28, 2015

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 runtime. If data viewer is placed before and after the Aggregate transform, we can see data flowing to the transformation at the runtime and how it looks like after the transformation occurred. The different types of data viewers are:
  1. Grid
  2. Histogram
  3. Scatter Plot
  4. 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 Unionall 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 Home
comments | |

SSIS Interview Questions - Part 3

Q: Explain Aggregate Transformation in SSIS?
It aggregates data, similar you do in applying TSQL 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 datagrid, and also operations for each Output Alias in Operation columns of the same datagrid. 
Some of operation functions listed below :
  • Group By
  • Average
  • Count
  • Count Distinct : count distinct and non null column value
  • Min
  • Max
  • Sum
Q: Explain Audit Transformation ?
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?
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 Conditional Split transformation?
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.

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

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

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.

Q: Explain Merge Transformation?
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?
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 OLEDB 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?
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?
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?
This component sorts the data similar to TSQL command ORDER BY.

Q: Explain Union all Transformation?
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 Home Next >>
comments | |

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?
  • OLEDB connection – Used to connect to any data source requiring an OLEDB connection (i.e.,SQL Server 2000)
  • 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
  • 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?
There are 7 types of data sources provided by SSIS:
  • Data Reader source
  • Excel source
  • Flat file source
  • OLEDB source
  • Raw file source
  • XML source
  • Script component
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.

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.
  • Audit: Adds Package and Task level Metadata - such as Machine Name, Execution Instance, Package Name, Package ID, etc..
  • CharacterMap: Performs SQL Server column level string operations such as changing data from lower case to upper case.
  • Conditional Split:  Separates available input into separate output pipelines based on Boolean Expressions configured for each output.
  • Copy Column: Add a copy of column to the output we can later transform the copy keeping the original for auditing.
  • Data Conversion: Converts columns data types from one to another type. It stands for Explicit Column Conversion.
  • Data Mining Query: Used to perform data mining query against analysis services and manage Predictions Graphs and Controls.
  • Derived Column:  Create a new (computed) column from given expressions.
  • 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.
  • 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 dataset 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.
  • Row Sampling: Captures sample data by using a row count of the total rows in dataflow specified by rows or percentage.
  • Union All: Merge multiple data sets into a single dataset.
  • Pivot: Used for Normalization of data sources to reduce analomolies by converting rows into columns
  • Unpivot: Used for denormalizing the data structure by converts columns into rows in case of building Data Warehouses.
<< Prev Home Next >>
comments | |

SSIS Interview Questions - Part 1

Q: What is SSIS?
SSIS was first introduced with 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), which is a database term that integrates data scheduling and tasks. The SSIS engine automation 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, and Precedence Constraints that connect the executables, 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

Home Next >>
comments | |

jQuery Interview Questions - Part 3

Penulis : Sandeep Mittal on Sunday, July 26, 2015 | 7/26/2015 01:28:00 PM

Sunday, July 26, 2015

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.
“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.


Q: Explain the animate function.
The animate function is used to apply the custom animation effect to elements.
$(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() {
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().

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 is used to set/return arbitrary data to/from an element.
Syntax, 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:, “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(, "item").val1);
$("label:val2").text(, "item").val2);

<< Prev Home
comments | |

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=""></script>
Google - To load jQuery from Google Libraries API
<script type="text/javascript" language="Javascript" src=""></script>

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().

// Use jQuery via jQuery(...)
You can also use your own specific character in the place of $ sign in jQuery.
var $j = jQuery.noConflict();
// Use jQuery via jQuery(...)

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.
    if ($('#element').length > 0){
       //Element exists

<< Prev Home Next >>
comments | |

SSRS Interview Questions & Answers (Part-8)

Penulis : Sandeep Mittal on Thursday, July 23, 2015 | 7/23/2015 02:20:00 PM

Thursday, July 23, 2015

Q: Are there issues when exporting SSRS reports into Microsoft Excel? When my users are trying to export a SSRS report into Microsoft Excel, one or two columns in the report appear to merge together. Why might this be?
Exporting from SSRS is not always perfect, even if you stay within the Microsoft range of products. If you have extra resources, you could splurge for an add-on that offers much better control over exporting to Excel, such as OfficeWriter. From my experience, though, it is usually headers or footers that cause exporting issues. If any of these headers or footers overlap with data columns in your report, you will find that the exported version of the report has merged cells. Also, check columns next to each other to make sure that there is no overlap, as well.

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 Home
comments | |

SSRS Interview Questions & Answers (Part-7)

Q: What are the Types of SSRS?
The types of 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 drillthrough reports, linked reports, and subreports, 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 Clickthrough Reports?
A clickthrough 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 clickthrough report is opened. These field settings cannot be changed in the report authoring tools. Clickthrough reports are autogenerated. 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 Drilldown Reports?
Drilldown reports initially hide complexity and enable the user to toggle conditionally hidden report items to control how much detail data they want to see. Drilldown reports must retrieve all possible data that can be shown in the report.
For reports with large amounts of data, consider drillthrough reports instead.

Q: What are Drillthrough Reports?
Drillthrough reports are standard reports that are accessed through a hyperlink on a text box in the original report. Drillthrough 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 drillthrough links to reports that display greater details based on the aggregate in the main report. Drillthrough reports can be filtered by parameters, but they do not have to be. Drillthrough reports differ from subreports in that the report does not display within the original report, but opens separately. They differ from clickthrough reports in that they are not autogenerated from the data source, but are instead custom reports that are saved on the report server. They differ from drilldown 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.
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 Home Next >>
comments | |