Thursday, December 15, 2011

Transactions in SSIS

What is the transaction support feature in SSIS?

When you execute SSIS package, every task executes in its own Transaction. We can group tasks together to execute as a single Transaction.

This can be achieved by setting Transaction property. In this way we can ensure either all of the tasks complete successfully and can roll back if any of the task fails.

What properties do you need to configure in order to use the transaction feature in SSIS?

  • Suppose you want to execute 5 tasks in a single transaction, in this case you can place all 5 tasks in a Sequence Container and set the TransactionOption and IsolationLevel properties appropriately.
    • The TransactionOption property expects one of these three values:
      • Supported - The container/task does not create a separate transaction, but if the parent object has already initiated a transaction then participate in it
      • Required - The container/task creates a new transaction irrespective of any transaction initiated by the parent object
      • NotSupported - The container/task neither creates a transaction nor participates in any transaction initiated by the parent object
  • Isolation level dictates how two more transaction maintains consistency and concurrency when they are running in parallel. To learn more about Transaction and Isolation Level.

Transaction Isolation levels:

The transaction isolation level determines the duration that locks are held. We'll use SQL Server as an example. The following transaction isolation levels are available in SQL Server:

  • READ UNCOMMITTED - reads do not acquire share locks and they don't wait on locks. This is often referred to as a dirty read because you can read modified data that hasn't been committed yet and it could get rolled back after you read it.
  • READ COMMITTED - reads acquire share locks and wait on any data modified by a transaction in process. This is the SQL Server default.
  • REPEATABLE READ - same as READ COMMITTED but in addition share locks are retained on rows read for the duration of the transaction. In other words any row that is read cannot be modified by another connection until the transaction commits or rolls back.
  • SERIALIZABLE - same as REPEATABLE READ but in addition no other connection can insert rows if the new rows would appear in a SELECT statement already issued. In other words if you issue a select statement in a transaction using the SERIALIZABLE isolation level you will get the same exact result set if you issue the select statement again within the same transaction.

The built-in transaction support in SSIS makes use of the Distributed Transaction Coordinator (MSDTC) service which must be running. MSDTC also allows you to perform distributed transactions; e.g. updating a SQL Server database and an Oracle database in the same transaction. If you execute an SSIS package that utilizes the built-in transaction support and MSDTC is not running, you will get an error message like the following:

Error: 0xC001401A at Transaction: The SSIS Runtime has failed  to start the distributed transaction due to error 0x8004D01B  "The Transaction Manager is not available.". The DTC transaction  failed to start. This could occur because the MSDTC Service is not running.

Note also that the SSIS package elements also have an IsolationLevel property with a default of Serializable. As discussed above in the section on Transaction Isolation Levels, this setting impacts the duration of locks as well as whether shared locks are acquired.


When I enabled transactions in an SSIS package, it failed with this exception: "The Transaction Manager is not available. The DTC transaction failed to start." What caused this exception and how can it be fixed?

  • SSIS uses the MS DTC (Microsoft Distributed Transaction Coordinator) Windows Service for transaction support. As such, you need to ensure this service is running on the machine where you are actually executing the SSIS packages or the package execution will fail with the exception message as indicated in this question.

What is event handling in SSIS?

  • Like many other programming languages, SSIS and its components raise different events during the execution of the code. You can write an even handler to capture the event and handle it in a few different ways. For example consider you have a data flow task and before execution of this data flow task you want to make some environmental changes such as creating a table to write data into, deleting/truncating a table you want to write, etc. Along the same lines, after execution of the data flow task you want to cleanup some staging tables. In this circumstance you can write an event handler for the OnPreExcute event of the data flow task which gets executed before the actual execution of the data flow. Similar to that you can also write an event handler for OnPostExecute event of the data flow task which gets executed after the execution of the actual data flow task. Please note, not all the tasks raise the same events as others. There might be some specific events related to a specific task that you can use with one object and not with others.

Tuesday, June 7, 2011

BI- SSIS

I will post some Interview questions shortely to this blog...

Monday, February 11, 2008

Interview Questions

Q. What exactly happens when aspx page is requested from browser?

A. At its core, the ASP.NET execution engine compiles the page into a class, which derives from the code behind class (which in turn derives directly or indirectly from the Page class). Then it injects the newly created class into the execution environment, instantiates it, and executes it. ASP.NET, on the other hand, can accept code in any language that is compatible with the .NET framework, because it's compiled down natively just like other code

Q. What is the order of destructors called in a polymorphism hierarchy?

A. Destructors are called in reverse order of constructors. First destructor of most derived class is called followed by its parent’s destructor and so on till the topmost class in the hierarchy.
You don’t have control over when the first destructor will be called, since it is determined by the garbage collector. Sometime after the object goes out of scope GC calls the destructor, then its parent’s destructor and so on. When a program terminates definitely all object’s destructors are called.


Q. can we have two web.config files in a single project(web allplication)?

A. The answer is yes, you can have 2 web.config file under the same project. Look at the page below to know the answer

Q. How can you enable automatic paging in datagrid?

A. Using the Built-In Paging Controls To use default paging, you set properties to enable paging, set the page size, and specify the style of the paging controls. Paging controls are LinkButton controls. You can choose from these types: Next and previous buttons. The button captions can be any text you want. Page numbers, which allow users to jump to a specific page. You can specify how many numbers are displayed; if there are more pages, an ellipsis ( ? ) is displayed next to the numbers. You must also create an event-handling method that responds when users click a navigation control. To use the built-in paging controlsSet the control's AllowPaging property to true. Set the PageSize property to the number of items to display per page. To set the appearance of the paging buttons, include a element into the page as a child of the DataGrid control. For syntax, see DataGrid Control Syntax. Create a handler for the grid's PageIndexChanged event to respond to a paging request. The DataGridPageChangedEventsArgs enumeration contains the NewPageIndex property, which is the page the user would like to browse to. Set the grid's CurrentPageIndex property to e.NewPageIndex, then rebind the data.



Q. if a.equals(b) is true then a.gethashcode() and b.gethashcode() must always return same hash code


A. The answer is False because it is given that A.equals(B) returns true i.e. objects are equal and now its hashCode is asked which is always independent of the fact that whether objects are equal or not. So, GetHashCode for both of the objects returns different value.



Q. what is text stream object?

A. It facilitates sequential access to file. The property and method arguments can be any of the properties and methods associated with the TextStream object. Note that in actual usage TextStream is replaced by a variable placeholder representing the TextStream object returned from the FileSystemObject. Example In the following code, a is the TextStream object returned by the CreateTextFile method on the FileSystemObject: Set fs = CreateObject("Scripting.FileSystemObject") Set a = fs.CreateTextFile("c:testfile.txt", True) a.WriteLine("This is a test.") a.Close WriteLine and Close are two methods of the TextStream Object.


Q. how do you differentiate managed code and unmanaged code?

A. Managed Code
is what Visual Basic .NET and C# compilers create. It compiles to Intermediate Language (IL), not to machine code that could run directly on your computer.Managed code runs in the Common Language Runtime.
Unmanaged code
is what you use to make before Visual Studio .NET 2002 was released.It compiled directly to machine code that ran on the machine where you compiled it—and on other machines as long as they had the same chip, or nearly the same.

Q. What is the difference between EVENTS and FUNCTIONS?

A. Event represents an action that is done to an object whereas Function represents an action that the object itself is doing.


Q. what events will occur when a page is loaded?

A. Page_PreInit Page_Init Page_InitComplete Page_PreLoad


Q. How can we create custom controls in asp.net?

A. Custom Controls can be created in either of the following 3 methods. Creating as a composite control : This method uses and combines the existing controls to give a custom functionality which can be used across different projects by adding to the control library. This can provide for event bubbling from child controls to the Parent container, custom event handling and properties. The CreateChildControls function of the Control class should be overridden for creating this custom control. This can also support design time rendering of the control. Deriving from an existing control : This method of creating a custom control derives from an existing ASP .Net control and customizing the properties that we need. This also can support custom event handling, properties etc. Creating a control from Scratch : This method is the one which needs maximum programming. This method needs even the HTML code for the custom controls to be written by the programmer. This may also need one to implement the IPostBackDataHandler and IPostBackEventHandler interfaces. A detailed explanation with example for this is available at Rendering Custom Controls Sample in MSDN.


Q. What is the difference between login controls and forms authentication?

A. Login controls are an easy way to implement Forms authentication without having to write any code. For example, the Login control performs the same functions you would normally perform when using the FormsAuthentication class prompt for user credentials, validate them, and issue the authentication ticket but with all the functionality wrapped in a control that you can just drag from the Toolbox in Visual Studio. Under the covers, the login control uses the FormsAuthentication class (for example, to issue the authentication ticket) and ASP.NET membership (to validate the user credentials). Naturally, you can still use Forms authentication yourself, and applications you have that currently use it will continue to run.


Q. Difference between webconfig and machineconfig?

A. Web.config file, as it sounds like is a configuration file for the Asp .net web application. An Asp .net application has one web.config file which keeps the configurations required for the corresponding application. Web.config file is written in XML with specific tags having specific meanings. As web.config file is used to configure one asp .net web application, same way Machine.config file is used to configure the application according to a particular machine. That is, configuration done in machine.config file is affected on any application that runs on a particular machine. Usually, this file is not altered and only web.config is used which configuring applications.


Q. what is the use of fixed statement?

A. The fixed statement sets a pointer to a managed variable and “pins” that variable during the execution of statement. Without fixed, pointers to managed variables would be of little use since garbage collection could relocate the variables unpredictably. (In fact, the C# compiler will not allow you to set a pointer to a managed variable except in a fixed statement.) Eg: Class A { public int i; } A objA = new A; // A is a .net managed type fixed(int *pt = &objA.i) // use fixed while using pointers with managed variables { *pt=45; // in this block use the pointer the way u want }

Q.Is it possible to override private virtual methods?

A. No, First of all you cannot declare a method as ‘private virtual’.

Q. how to load data from one page to another page in asp.net?

A . There are many ways to do this? In Asp.net2.0 You can use cross page submission. Using Query String Using Post

Q. What does the volatile modifier do?

A. The system always reads the current value of a volatile object at the point it is requested, even if the previous instruction asked for a value from the same object. Also, the value of the object is written immediately on assignment. The volatile modifier is usually used for a field that is accessed by multiple threads without using the lock statement to serialize access. Using the volatile modifier ensures that one thread retrieves the most up-to-date value written by another thread.


Q. .What is the use of AutoWireup in asp.net? In which circumstances we have to use this?


A .AutoEventWireup attribute is used to set whether the events needs to be automatically generated or not. If AutoEventWireup is set to "false" see that the page related events are not fired. For example if you write Response.Write("hello world") in the Page_Load event when the AutoEventWireup="false" then the Response.Write.. is not executed. However, when we set the value of the AutoEventWireup attribute to true, the ASP.NET runtime does not require events to specify event handlers like Page_Load or Page_Init.

What is a Webcontrol?

Serves as the base class that defines the methods, properties and events common to all controls in the System.Web.UI.WebControls namespace.


Q. what is the difference between application state and caching

Application variable is the global variable specific to application but a caching variable is specifc to page and time out.

Q. What can be stored in webconfig file

There are number of important settings that can be stored in the configuration file. Here are some of the most frequently used configurations, stored conveniently inside Web.config file.. 1. Database connections 2. Session States 3. Error Handling 4. Security