Examples of Errors SSIS can handle

Question

Hi,

I set up error handling in my packages (using SSIS 2005) as described in the below link

http://consultingblogs.emc.com/jamiethomson/archive/2005/08/08/1969.aspx

So basically I am redirecting my error causing rows to another destination along with Error Code, Error Column and Error Description. Now I have read in various threads/posts online that the redirection doesn't take place when they are command related errors and only for column/data related errors the rows would get redirected. In some of my packages I used the Data Conversion task and when I give a wrong length here I am able to redirect the rows to Error table. Now I would like to test error handling in other packages where I didn't use Data Conversion task. I can't try integrity check violations too as my data is being sent to a Delimited Flat File.

Please suggest me some scenarios where I can test the error handling for my other packages. Please suggest something other than Data Conversion errors and Integrity violation errors.

Answer 1

Based on what I read I could not extract what your test case scenarios are.

There is a unit testing tool for SSIS http://ssisunit.codeplex.com/; I think this tool opens you a door to a more through and organized approach to testing SSIS packages.

---

Arthur My Blog

Answer 2

Hi ArthurZ,

Thanks for your reply. But I don't have permissions to install new softwares. I will try and explain my scenario a bit more in detail. For all my packages the below holds true - 

1. Source = OLEDB Source

2. Destination = Flat File Destination (Delimited by |)

In a few packages I have a Data Conversion task / Derived Column Tasks in between the Source and Destination. Now in these packages I redirected the Error output from OLEDB Source , Data Conversion task and Derived Column Task  to a Union All, then through a Script Component(to get a Error Description column) and finally to an Error table using OLEDB Destination. In my Data Conversion task I am changing the datatype of a field say "Amount" to numeric(4,2). So if my Amount field has a value like 100.32 that would cause an error and this row was getting redirected to my error table.

In a few packages I have just Source and Destination and I am redirecting my Error output form OLEDB Source to Script Component(to get a Error Description column) and then to an Error table. Now I want to create a scenario where something would go wrong here and the error causing rows will be redirected to the Error table.

Please help me with this.

Answer 3

In a few packages I have just Source and Destination and I am redirecting my Error output form OLEDB Source to Script Component(to get a Error Description column) and then to an Error table. Now I want to create a scenario where something would go wrong here and the error causing rows will be redirected to the Error table.

You did mention that your source is a table and your destination is a file , so i am assuming that you are running a SELECT stsatment on your table in SSIS

so you can have a Bad formated select statmet like  SELECT GETDATE() AS 'AMOUNT'  , ..... FROM tbl.... or insted of GETDATE have 'ABC' so that when the package is running and converting the date or the ABC to a numeric(4.2) you will get an error.

all you have to do is to load wrong Metadata (field data format) into the SSIS.

THE LINK IS NOT YOUR ANSWER BUT GOOD TO KNOW

http://plexussql.blogspot.com/2010/01/emailing-error-log-file-as-attachments.html

---

Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Answer 4

Hi,

Thanks for your reply.

You are right I am using a SELECT statement in the OLEDB Source. I have an int field project_id in my SELECT statement , so I did something like this -

SELECT GETDATE() AS project_id, project_name
FROM dbo.Project

When I put this query in my source an error is being thrown at the Package level itself(error converting DBtimestamp to Int) and thus its not executing at all.

 

 

Answer 5

Why would you write such an SQL statement (sorry I am still in the issue discovery state)?

---

Arthur My Blog

Answer 6

OK try another data type, like fixed text or maybe you can UNION a record to the real SELECT Statment

ie assuming that the right and good Query is SELECT project_id, project_name FROM dbo.Project , and project ID is numeric, you can have isted

SELECT project_id, project_name FROM dbo.Project

UNION

SELECT 25.5555555 AS project_id, 'ProjectABC' AS project_name

 

---

Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Answer 7

Why would you write such an SQL statement (sorry I am still in the issue discovery state)?

it is interesting for me as well, but anyways his request was that he want to make an error, maybe he is testing something

Hi Timmana , i think ArthurZ is right , why do you want to do this????????

---

Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Answer 8

To answer both Your questions - Yes, I am trying to generate an error and check if they get redirected to the Error tables. 

Nik - I'll try your above query and see how it works...

Answer 9

Hi Timmana
you mentioned that your source is a table and your destination is a Text file, so technically if you have the right SELECT statment for the source (which basically it must convert all the metadata to a nVarchar or any text or any CHAR data type) you dont need to redirect records , unless your sources are in two different DB and you have to join them to pull out the redirected records.

good luck

---

Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Answer 10

I still do not understand if the SQL statement is not generated dynamically why and how it would change from outside of the package to become an erroneous SQL query?

---

Arthur My Blog

Answer 11

Thanks for your suggestion Nik.

The correct and working statement(like you said) is 

SELECT project_id, project_name FROM dbo.Project 

project_id is int, project_name is varchar.

Nik- I tried your above query with UNION. 

This part -

SELECT 25.5555555 AS project_id, 'ProjectABC' AS project_name

No error was thrown as the number 25.555555 got rounded off and loaded as 26 into the file. Tried putting a Text there like 'abc' instead of 25.5555555, package execution started but failed at OLEDB Source.

I really don't understand what kind of errors can trigger this redirection of rows from Error Output in OLEDB Source. Any insight into this issue is appreciated.

 

Answer 12

Nik-just read your above reply.

 

Just to rephrase what you said -

When I just have on source DB and a File Destination, there wouldn't be a scenario where the redirecting of rows could occur and its unnecessary to have Error handling in the package. Is my understanding correct?? Please let me know.

 

ArthurZ -

I am newly trying this Error handling mechanism in SSIS. When I connected a OLEDB Source to a File Destination I saw an option of redirecting error Output from the OLEDB Source. So I have been trying different things to try and see if any rows would get redirected in this scenario(try to create an error).

Answer 13

Error redirection IMHO is to satisfy a certain business scenario say when you want the bad rows logged (e.g. to after-process them somehow later like sending back them all as an email/attachment, whatever).

Why do you need this?

In some cases it is OKay to just let the package bomb. Depends how much time you have to burn, anyways,

Typically, the error rows should redirect when there are subtle data issues (for example a positive value IN THE SOURCE where a negative was expected, etc.), not a wierd SQL like above.

---

Arthur My Blog

Answer 14

Just to rephrase what you said -

When I just have on source DB and a File Destination, there wouldn't be a scenario where the redirecting of rows could occur and its unnecessary to have Error handling in the package. Is my understanding correct?? Please let me know.

Every package needs Error Handling , BUT not every package needs to redirect records.

in you case

1- you need error handling, like the link i had provided

2- your source is a table or a SELECT and your destination is a text file, so if your SELECT provides TEXT only (CONVERTS all fields to nVarChar)  the question will be what do you want to redirect ? and why ? i think the answer is nother

in your DFT you have

1 -SQL Server CONNECTION (Your select)    SELECT CAST ( project_id AS nVarChar(50)) , CAST ( project_name AS nVarChar(50))  FROM tblXYZ

2- FF Destination file

----- NOTE : what you have above is a string from the SELECT that will be inserted into a text file as Text, my question is what do you want to redirect? my answer in NOTHING

unless you have other objects in between like lookup and etc... , in that case that is a different scenario.

 

---

Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Answer 15

Thank you Nik and Arthur for your replies.

Guess my understanding of Error handling wasn't totally right.

 

Answer 16

If you want to practice redircting errors try this

let say you have 2 tables 1-tblUsers that has 100 users  2-tblMappingTable that has 10 qualified users that exist in the tblUsers , assuming that in each table you have unique users id

1- SQL OLE SOurce (Assuming that it has the UserID from tblUsers)

2- LOOKUP    (SELECT UserID FROM tblMappingUsers)      RED ARROW (Error ---> Not in the dictionary table- redirect to )------------> SQL Ole Error Table  (90 records)tblMissing Users

3-  Green arrow

4- SQL destination (Good users) (10 records)

---

Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).