Thursday, 7 February 2013

SSIS INTERVIEW QUESTIONS



1)Difference between Sql server destination and oledb destination?
The SQL server destination uses shared memory to connect to the SQL server database.It also has some 
SQL Server specific optimizations that allow pushing data faster to the database (using insert Bulk).As a
 result, it will be faster than the OLEDB provider, but has more restrictions.
Most important restriction is that it will only work on a local server.So if your production environment
 has SSIS package running on a different server than the destination server, use Oledb.

2)How to handle failed rows in ssis? or 

how to handle redirected bad rows in ssis 


How to handle Failed Rows in a Data Flow

Reza Rad's Technical blog

How to handle Failed Rows in a Data Flow

SSIS
suppose this scenario:

you have a source table and a destination table, you want to transfer rows from source table to destination table, so you can use simple data flow with OLE DB source and OLE DB Destination. but problem hits when destination table has few rows inside, and there is Unique Constraint in ID field of Destination table.
in this situation your data flow will fail, because when OLE DB Destination want to insert rows in destination table, it will got Unique Constraint error and it will raise Data Flow error, and this will prevent OLE DB Destination to insert any rows in table, even rows which hasn't equivalent key in destination !

So, a work around needed here to avoid failing package and only move rows which hasn't problem with unique constraint in destination table.

let me explain it with a sample here...

create source table with this script:

CREATE TABLE [dbo].[SourceFailureTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NULL,
 CONSTRAINT [PK_SourceFailureTable] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 

ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

and fill it with this data:


now create destination table with this structure:

CREATE TABLE [dbo].[DestinationFailureTable](
    [ID] [int] NOT NULL,
    [name] [varchar](50) NULL,
 CONSTRAINT [PK_DestinationFailureTable] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,

ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Note that DestinationFialureTable, has a Primary Key on ID filed, primary key is basically a Unique Constraint. so this constriant will check IDs to be unique , and if an insertion tends to insert not unique value in ID field this will cause Unique Constraint Error.

fill Destination with these three fields:



we want to import rows from SourceFailureTable to DestinationFailureTable.
Create  simple Data flow task, with an OLE DB Source, connected to SourceFailureTable. and an OLE DB Destination connected to DestinationFailureTable. map the columns too.
now every time you run this package you will got error:


error description:
"Violation of PRIMARY KEY constraint 'PK_DestinationFailureTable'. Cannot insert duplicate key in object 'dbo.DestinationFailureTable'."

and none of ten rows in Source table moved to destination table. even rows with IDs like 1 , 2, 4 ,.. which

not exists in the destination table.

So, What we can do?
right click on OLE DB Destination, select properties window. find AccessMode in properties, you can see that the AccessMode value is OpenRowSet using FastLoad. this will cause that all records from input insert with a fast load transaction at one time. so this will cause error.
Change AccessMode to OpenRowSet . this will try to insert row by row.
Note that this is not good option when you insert large amount of records because insertion with fast load has better performance, but in this sample I just want to show you how to handle failed rows in insert.
after chaning AccessMode to OpenRowSet , run package again, you will see the error happens again with no luck. but difference is here in the destination table:



in the destination table two new rows inserted , rows with ID 1 and 2. but no more rows. the meaning of this behaviour is that OLE DB Destinaion tried to insert row by row into the DestinationFailureTable, first two rows inserted successfully. but for third row it got unique constraint error again and cause package to fails. and failing the package stops other rows transformation.

So you should handle rows which cause Error. this is the time when Failure Error Output comes to help.
first delete rows with ID 1 and 2 from DestinationFailureTable manually.
and then add another table named FailedRows with this structure:

CREATE TABLE [dbo].[FailedRows](
    [ID] [int] NULL,
    [name] [varchar](50) NULL
)
we want to move rows which cause error to this table.

now add another OLE DB Destination in data flow , right after DestinationFailureTable . and connect RED ARROW to it. when you connect this arrow, a Configure Error Output window will appear. look at this screenshot:



there are three options for Error property in this window:

Ignore Failure    will ignore any errors during insertion of records to destination
Redirect Row    will redirect any rows which cause error during insertion to destination
Fail Component    will cause to fail component when insertion hits any error

by default this option set to Fail Component. so when you got an error during insertion the whole OLE DB Destination will fail.
Set the Error as Redirect row. this will cause every rows which cause error ( means BAD rows ) to redirect to new OLE DB Destination.
now hit ok. and double click on new OLE DB Destination let's name it FailedRows. connect this to failed rows. and map the columns like below:



Note that there are two new columns in mapping page, ErrorCode and ErrorColumn. these are auto generated rows by Failure Error Output and will show code and column number of error. we don't need them in this sample. so just map ID and name.

now run the package. you will see that there are 10 rows transfered from SourceFailureTable to DestinationFailureTable, but there are only 3 rows transfered to FailedRows. this means that there are 3 rows exists in DestiantionFailureTable which prevent equivalent values in SourceFailureTable to be inserted . so these rows transfered to FailedRows Table.
this is full schema of package:



and look at results in DestinationFailureTable:



there are 7 new rows in this table.

and this is results in FailedRows Table:



there are 3 rows which cause unique constraint failure transfered to this table.

This was a sample of how to use failure error output in SSIS. hope to solve your issues in this area.
Posted by Reza Rad on Friday, July 16. 2010 at 02:22 in SSIS

Vote for this article:
  2. -2
  3. -1
  4. 0
  5. +1
  6. +2
Current Vote: 2.47 of 5, 17 vote(s) 12713 hits


Trackbacks
Trackback specific URI for this entry

No Trackbacks


Comments
Display comments as (Linear | Threaded)


Jitendra says,
Monday, July 16. 2012 at 03:45 (Reply)
Hi, The above topic helped me lot in understanding error handling. I was trying the same thing, but, I didn't the red arrow after oledb destination adapter. I put oledb source adapter, configure it and then I placed oledb destination adapter. I dragged green arrow from source and placed it in destination adapter. Next, for failed rows, I placed second destination adapter after first oledb destination adapter. I tried to connect second destination adapter with first one. But, I didn't get red or green arrow after I clicked on first destination adapter. Please throw some light on it.

Reza Rad says,
Monday, July 16. 2012 at 05:42 (Link) (Reply)
Hi Jitendra, When you click (Single Click) on OLE DB Destination you will see a red arrow at the blow of component. you can use that one. Regards, Reza

Add Comment


Standard emoticons like :-) and ;-) are converted to images.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA

 
 
 
Submitted comments will be subject to moderation before being displayed.
 
Posted by at

5 comments:

  1. Anonymous27 November 2015 at 22:00

    thank u for providing this blog...it's contains very interesting information..we are giving msbi online training...

    ReplyDelete
  2. alaska30 November 2015 at 21:38

    recently i came your blog and have been read along it's very interesting..we are offering msbi online training

    ReplyDelete
  3. jennifersusan3 December 2015 at 04:55

    thank u for sharing..it's really very nice...msbi online training

    ReplyDelete
  4. christianasteves8 December 2016 at 22:10

    I am impressed with your writing skills and with the layout on your blog. For more info on MSBI click on MSBI Tutorials.

    ReplyDelete
  5. Johnny28 September 2017 at 06:04

    Hi,
    This is useful for basic knowledge for ssis and can you update indepth questions

    Regards
    johny.

    ReplyDelete

Subscribe to: Post Comments (Atom)