Wednesday, April 29

How To Create SSIS Package (Step By Step Example: With Diagrams)


In this post we will try to crate a package that extract the data from 'Student Table' of Source-Database and load it successfully in 'Student Table' of 'Destination-Database'. You can Download or look into the sample database I am using.

System requirements for SSIS or Business intelligence Development:

  1. You should have installed SQL Server Standard or Enterprise version.
  2. I am using Visual Studio Add-in (SQL Server Data Tools) in this SSIS tutorial to design SSIS package. This needs SSDT also to be installed.


Once confirmed that SSDT is installed on machine. We are ready to create an ETL(Extract - Transform - Load) package.

So here we go!

Here are the scripts that you can run in order to generate two databases(Source and destination) with Tables in it.

Go to Start Screen or Click Start Button. Go to Microsoft SQL Server and you will find 'SQL Server Business Intelligence Development Studio' under this.
How to open SQL Server business intelligence development studio
Open SQL Server business Intelligence Development Studio




This opens a instance of Visual Studio. Click on File tab and select 'New Project'. Chose 'Business Intelligence Projects' as project type and select 'Integration Services Project', type a sensible name for your project(I have given it SimpleDataTransfer, that's what it does.). you can chose a location and whether you want to create a separate directory for your project under selected hard-drive location.

How to create ssis project
Selecting  project template for SSIS using visual studio.




This opens a Package.dtsx file on visual editor. There are 2 sections on left, and 2 on right side of Editor which are:
  1. Variables: Here variables can be defined that can later used during execution of ETL or package. user should mention a appropriate scope for the variable defined.
  2. ToolBox: As normal toolbox control of visual studio, contains all the necessary tools to crate a package under SSIS
  3. Solution Explorer: Hierarchy of the folders of the current project.
  4. Properties: Shows available properties for selected module/component from editor.

There are also 4 tabs on editor window namely
  1. Control Flow: The main container where everything else resides.
  2. Data Flow: Data Flow is the main component when we have to transfer data.
Other than these there are 2 more: Event Handlers and Package Explorer(skip for now).

How to create package in ssis




Open Toolbox and double click 'Data Flow Task' or drag-drop it on editor window. This is the only task needed for this 'Simple Data Transfer' ETL Project. Double click on the 'Data Flow Task' in editor and it opens a editor for selected Data Flow Task.

How to create data flow task in ssis





























Double Click the 'Data Flow Task'. Select 'OLE DB Source' from toolbox and drag it to editor. as we have to transfer the data from a SQL Server Database.
Note:
1. Flat File Source should be chosen when source is flat file.
2. Excel Source should be chosen when source is excel file. 

OLE DB Source in ssis





























Double Click the 'OLE DB Source' component to configure it. Here click on 'Connection Manager' menu and select an available connection. If it seems empty in here and no connection is available then click on 'Cancel' button.
Ole DB Configuration SSIS



































On 'Data Flow' editor, there is a 'Connection Manager' section at bottom. Right Click on this section and select 'New OLE DB Connection...'
SSIS | database connection using connection manager





























This opens a 'Connection Manager' window. Select the server here and connect to a database selecting it from drop-down box. To log on to server either use windows Authentication or use SQL Server Authentication credentials of the user you are logging in with. Click 'OK'. This will create a new connection manager to connect with.

SSIS | Add new connection using connection manager







































Now 'OLE DB Source' can be configured using this new connection. select the appropriate connection from list and say 'OK' pressing the button obviously :)

SSIS | ole db connection manager

































Once done with setting-up the database connection for source, chose the table to extract data from.(Student in this case). Click 'OK' and we are done with 'OLE DB Source' configuration.

SSIS | ole db source editor































Now drag-drop a 'OLE DB Destination' component from toolbox on editor window, click on 'OLE DB Source' and connect the Green Line to 'OLE DB Destination.' And Configure the destination database same as we configured source database.(just select destination databse while you select database and not source)
SSIS | ole db destination editor


































Now its the time to disco (mapping actually). Let us now map the columns of source and destination tables. Click on 'Mapping' menu in 'OLE DB Destination' configuration, and there will be 2 tables connecting by some links to appropriate columns.

SSIS  | Mapping
































Click on the first link connecting IDs of both the tables and delete it. As we have defined ID column for tables as Identity it will get auto-generated value by itself.

SSIS | mapping tables
































That's fine for now. We don't need so many configuration for this easy task. Save your package file and go to 'Control Flow' tab. Right click on 'Data Flow task' and click on 'Execute task'. This will execute the task and transfer the data from source table to destination table.

SSIS | Execute task from control flow





























If it runs successfully(chances are 100% if your are following step by step ETL creation till now.), it will turn 'GREEN'.

SSIS | Executing package successfully



























But if it turns 'RED' don't worry, there is a tab 'Execution Result'. This tab shows the progress of the package execution. If you get an error it will be shown here. Copy the error in a notepad and check the issue.
Execution tab SSIS





























Or if you want you can also debug SSIS package using debugging events. Right click on 'Data Flow Task' and set debugging brake points.

SSIS | Debug breakpointa in ssis
























Cheers!!
Let me know your feedback/suggestions in comments.

Monday, April 20

How To Play Multiple HTML5 Audio-Video in Sync on Safari Browser

As Safari HTML Audio Video Guide Suggests:

Syncing Multiple Media Elements Together Until the advent of media controllers, ensuring that two or more videos played at precisely the same time was a challenging endeavor.

Media controllers let you group any number of audio and/or video elements so that they can be managed by a universal set of controls, and also so that they can be kept in perfect sync, even if a network hiccup occurs.

To create a media controller, simply add the mediagroup attribute to all of the elements you wish to sync together. The value you choose to assign to mediagroup is up to you—as long as the value is the same for each slaved element, a media controller will be created implicitly.


Most of the same functions, attributes, and events available to audio and video elements are also available to media controllers. Instead of calling play() or pause() directly on the video itself, you call them on the media controller.
Controlling Media with JavaScript Syncing Multiple Media Elements Together 2012-12-13 | Copyright © 2012 Apple Inc. All Rights Reserved.


Note: Two attributes that aren’t supported by media controllers are loop and auto-play.
Accessing the controller object on any of the slaved media elements will return a controller of the grouped elements. You can also create a media controller entirely in JavaScript without needing to modify the attributes of your HTML:


If one video stalls or stutters, the other videos will automatically pause to wait for the lagging video to catch up. When the video buffers and is ready to play, the remaining videos will resume in sync.

And you can find this doc here: Safari HTML Audio Video Guide

Monday, April 13

Difference Between Implicit and Explicit Join

If you are looking for the performance difference between INNER JOIN(Explicit Join) and WHERE clause(Implicit Join), Let me clear the SQL Engine(either its mySql/SQL Server or Oracle) got more and more intelligent during the time. So there is no such difference in performance whatever you write from INNER JOIN or WHERE clause.


Let me write a simple query using WHERE clause or Implicit Join:



This SQL Query is written with only 4 tables, 5-10 tables in queries is common scenario while you are working with a good size of project. And believe me or not, writing these queries with WHERE clause is definitely going to get your head off the shoulders.

Now let me write it using INNER JOIN or Explicit Join:



The later one is hell more readable than the conventional(not now though) WHERE clause written above.

Here is an Execution Plan for INNER JOIN and WHERE clause, which is same for both the cases:

Execution Plan Comparison: Implicit Join vs Explicit Join
Execution plan credit: Blog do Ezequiel

So Is the only difference READABLITY??

Answer is Yes and No.


So here are some other points which make INNER JOIN better than WHERE clause:

  1. INNER JOIN is ANSI syntax which you should use.
  2. It is generally considered more readable, especially when you join lots of tables.
  3. It can also be easily replaced with an OUTER JOIN whenever a need arises.
  4. The WHERE syntax is more relational model oriented.
  5. A result of two tables JOIN'ed is a Cartesian product of the tables to which a filter is applied which selects only those rows with joining columns matching.
And some of interviewers who believe in playing word tricks(I hate them really) this post is actually Difference: Implicit Joins vs Explicit Joins

Cheers !!!

Wednesday, February 25

Cannot Modify Header Information - Headers Already Sent : How To Fix

This error message gets triggered when anything is sent before you send HTTP headers (with setcookie or header). Common reasons for outputting something before the HTTP headers are:
1. Accidental whitespace, often at the beginning or end of files, like this:
the space before "php opening tag"
To avoid this, simply leave out the closing ?> - it's not required anyways.

2. "Byte order marks" at the beginning of a php file. Examine your php files with a hex editor to find out whether that's the case. They should start with the bytes 3F 3C. You can safely remove the BOM EF BB BF from the start of files.

3. Explicit output, such as calls to echo, printf, readfile, passthru, code before php opening tag etc.

4. A warning outputted by php, if the display_errors php.ini property is set. Instead of crashing on a programmer mistake, php silently fixes the error and emits a warning. While you can modify the display_errors or error_reporting configurations, you should rather fix the problem. Common reasons are accesses to undefined elements of an array (such as $_POST['input'] without using empty or isset to test whether the input is set), or using an undefined constant instead of a string literal (as in $_POST[input], note the missing quotes).



Turning on output buffering should make the problem go away; all output after the call to ob_start is buffered in memory until you release the buffer, e.g. with ob_end_flush.

However, while output buffering avoids the issues, you should really determine why your application outputs an HTTP body before the HTTP header. That'd be like taking a phone call and discussing your day and the weather before telling the caller that he's got the wrong number.
For more answers : StackOverflow

Thursday, February 19

MVC Interview Questions

What are the 3 main components of an ASP.NET MVC application?
1. M - Model
2. V - View
3. C - Controller

In which assembly is the MVC framework defined?
System.Web.Mvc

Is it possible to combine ASP.NET webforms and ASP.MVC and develop a single web application?
Yes, it is possible to combine ASP.NET webforms and ASP.MVC and develop a single web application.

What does Model, View and Controller represent in an MVC application?
Model: Model represents the application data domain. In short the applications business logic is contained with in the model.

View: Views represent the user interface, with which the end users interact. In short the all the user interface logic is contained with in the UI.

Controller: Controller is the component that responds to user actions. Based on the user actions, the respective controller, work with the model, and selects a view to render that displays the user interface. The user input logic is contained with in the controller.

What is the greatest advantage of using asp.net mvc over asp.net webforms?
It is difficult to unit test UI with webforms, where views in mvc can be very easily unit tested.

Which approach provides better support for test driven development - ASP.NET MVC or ASP.NET Webforms?
ASP.NET MVC

What are the advantages of ASP.NET MVC?
1. Extensive support for TDD. With asp.net MVC, views can also be very easily unit tested.
2. Complex applications can be easily managed
3. Seperation of concerns. Different aspects of the application can be divided into Model, View and Controller.
4. ASP.NET MVC views are light weight, as they donot use viewstate.

Is it possible to unit test an MVC application without running the controllers in an ASP.NET process?
Yes, all the features in an asp.net MVC application are interface based and hence mocking is much easier. So, we don't have to run the controllers in an ASP.NET process for unit testing.

Is it possible to share a view across multiple controllers?
Yes, put the view into the shared folder. This will automatically make the view available across multiple controllers.

What is the role of a controller in an MVC application?
The controller responds to user interactions, with the application, by selecting the action method to execute and alse selecting the view to render.

Where are the routing rules defined in an asp.net MVC application?
In Application_Start event in Global.asax

Name a few different return types of a controller action method?
The following are just a few return types of a controller action method. In general an action method can return an instance of a any class that derives from ActionResult class.
1. ViewResult
2. JavaScriptResult
3. RedirectResult
4. ContentResult
5. JsonResult

What is the significance of NonActionAttribute?
In general, all public methods of a controller class are treated as action methods. If you want prevent this default behaviour, just decorate the public method with NonActionAttribute.

What is the significance of ASP.NET routing?
ASP.NET MVC uses ASP.NET routing, to map incoming browser requests to controller action methods. ASP.NET Routing makes use of route table. Route table is created when your web application first starts. The route table is present in the Global.asax file.

What are the 3 segments of the default route, that is present in an ASP.NET MVC application?
1st Segment - Controller Name
2nd Segment - Action Method Name
3rd Segment - Parameter that is passed to the action method

Example: http://pragimtech.com/Customer/Details/5
Controller Name = Customer
Action Method Name = Details
Parameter Id = 5

ASP.NET MVC application, makes use of settings at 2 places for routing to work correctly. What are these 2 places?
1. Web.Config File : ASP.NET routing has to be enabled here.
2. Global.asax File : The Route table is created in the application Start event handler, of the Global.asax file.

What is the adavantage of using ASP.NET routing?
In an ASP.NET web application that does not make use of routing, an incoming browser request should map to a physical file. If the file does not exist, we get page not found error.

An ASP.NET web application that does make use of routing, makes use of URLs that do not have to map to specific files in a Web site. Because the URL does not have to map to a file, you can use URLs that are descriptive of the user's action and therefore are more easily understood by users.

What are the 3 things that are needed to specify a route?
1. URL Pattern - You can include placeholders in a URL pattern so that variable data can be passed to the request handler without requiring a query string.
2. Handler - The handler can be a physical file such as an .aspx file or a controller class.
3. Name for the Route - Name is optional.

Is the following route definition a valid route definition?
{controller}{action}/{id}
No, the above definition is not a valid route definition, because there is no literal value or delimiter between the placeholders. Therefore, routing cannot determine where to separate the value for the controller placeholder from the value for the action placeholder.

What is the use of the following default route?
{resource}.axd/{*pathInfo}
This route definition, prevent requests for the Web resource files such as WebResource.axd or ScriptResource.axd from being passed to a controller.

What is the difference between adding routes, to a webforms application and to an mvc application?
To add routes to a webforms application, we use MapPageRoute() method of the RouteCollection class, where as to add routes to an MVC application we use MapRoute() method.

How do you handle variable number of segments in a route definition?
Use a route with a catch-all parameter. An example is shown below. * is referred to as catch-all parameter.
controller/{action}/{*parametervalues}

What are the 2 ways of adding constraints to a route?
1. Use regular expressions
2. Use an object that implements IRouteConstraint interface

Give 2 examples for scenarios when routing is not applied?
1. A Physical File is Found that Matches the URL Pattern - This default behaviour can be overriden by setting the RouteExistingFiles property of the RouteCollection object to true.
2. Routing Is Explicitly Disabled for a URL Pattern - Use the RouteCollection.Ignore() method to prevent routing from handling certain requests.

What is the use of action filters in an MVC application?
Action Filters allow us to add pre-action and post-action behavior to controller action methods.

If I have multiple filters implemented, what is the order in which these filters get executed?
1. Authorization filters
2. Action filters
3. Response filters
4. Exception filters

What are the different types of filters, in an asp.net mvc application?
1. Authorization filters
2. Action filters
3. Result filters
4. Exception filters

Give an example for Authorization filters in an asp.net mvc application?
1. RequireHttpsAttribute
2. AuthorizeAttribute

Which filter executes first in an asp.net mvc application?
Authorization filter


What are the levels at which filters can be applied in an asp.net mvc application?

1. Action Method
2. Controller
3. Application
[b]Is it possible to create a custom filter?[/b]
Yes

What filters are executed in the end?
Exception Filters

Is it possible to cancel filter execution?
Yes

What type of filter does OutputCacheAttribute class represents?
Result Filter

What are the 2 popular asp.net MVC view engines?
1. Razor
2. .aspx

What symbol would you use to denote, the start of a code block in razor views?
@

What symbol would you use to denote, the start of a code block in aspx views?
<%= %>

In razor syntax, what is the escape sequence character for @ symbol?
The escape sequence character for @ symbol, is another @ symbol

When using razor views, do you have to take any special steps to protect your asp.net mvc application from cross site scripting (XSS) attacks?
No, by default content emitted using a @ block is automatically HTML encoded to protect from cross site scripting (XSS) attacks.

When using aspx view engine, to have a consistent look and feel, across all pages of the application, we can make use of asp.net master pages. What is asp.net master pages equivalent, when using razor views?
To have a consistent look and feel when using razor views, we can make use of layout pages. Layout pages, reside in the shared folder, and are named as _Layout.cshtml

What are sections?
Layout pages, can define sections, which can then be overridden by specific views making use of the layout. Defining and overriding sections is optional.

What are the file extensions for razor views?
1. .cshtml - If the programming language is C#
2. .vbhtml - If the programming language is VB

How do you specify comments using razor syntax?
Razor syntax makes use of @* to indicate the beginning of a comment and *@ to indicate the end. An example is shown below.
@* This is a Comment *@


What are various design patterns?
1. 
2. 

What is lazy loading and how can we achieve it ?


What is TDD or Test Driven Development ?

 
What is Web Socket ?


Adding more soon....

You are really welcome if you have any question. Please mail me at 17path@gmail.com or add a comment below.