SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error Handling in SSIS


Error Handling in SSIS

Author
Message
nico.verbaenen
nico.verbaenen
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 28
thx! I didn't read all 8 pages of the discussion... :-)

grtz,
NIco
John Rowan
John Rowan
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13672 Visits: 4588
Jack,

Great article. I implemented a similar solution (using Jamie's script to get the error description) but your's is a bit more elegant. I love the idea of a one-table-catches-all-errors concept with an xml column......great job!

John

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44719 Visits: 14925
John,

Thanks for the nice comment. Just so you know earlier in the thread I attached an SSRS report that displays the data from the catch-all data, parsing the xml column.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Lempster
Lempster
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5099 Visits: 1657
Jack,

The solution clearly provides what you want in terms of error handling/logging, but wouldn't it be easier to use the Log Providers that are readily available with SSIS? For example, you could use a 'SSIS log provider for SQL Server' provider to write to a table of your choice. The details that you want to capture (MessageText, Source etc.) can be chosen for each Event, e.g. OnError, OnWarning, OnInformation etc.
Seems a lot easier than having to write script components...

Regards
Lempster
WI-DBA
WI-DBA
SSChasing Mays
SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)

Group: General Forum Members
Points: 610 Visits: 605
If I may interject for Jack, a big part of the components value is the ability to roll up the pertinent fields in the pipeline and stored them as XML to be queried, along with package name, etc. That and the ability to make error handling so easy is quite valuable in my mind. No custom work to do at all.

That being said, there are many ways to skin the proverbial cat in SSIS, this component is great for integration work where you don't want to stall the whole process for some bad data.

Cheers
http://twitter.com/widba
http://widba.blogspot.com/
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44719 Visits: 14925
Lempster,

I'd have to look into using the Log providers. I actually learned some about them AFTER I wrote the script component and article. As you can see from the references I used to create the component, I did look for other answers and people smarter than I had done some similar things. I did not find anything that mentioned using the log providers.

One question though, do the log providers allow the package/data flow to continue or does the package stop? One of my goals was to have the dataflow and package continue, while still logging the error and the data that caused the error.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Lempster
Lempster
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5099 Visits: 1657
Jack,

There is no direct correlation between the Log Providers and failing packages/data flows, By that I mean that the use of a Log Provider in and of itself will not cause a package or task to fail. Those properties are set within the package/component using the 'FailParentOnFailure' and FailPackageOnFailure properties. The Log Providers will simply log what you ask them to log for the conditions that you want them to log on, e.g. OnWarning, OnInformation, OnFailure.

The attached screenshot shows the configuration for a 'SSIS log provider for SQL Server' log provider. As you can see, the log provider can be enabled at the package level (as in the screenshot) or the individual Control Flow task level.



Regards
Lempster
Attachments
SSIS for SQL Server log provider.JPG (31 views, 141.00 KB)
Laura Lin
Laura Lin
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 19
Would you give out step by step instruction on how to install and set up your error handling component? Thanks
JMI
JMI
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2793 Visits: 775
First when I saw Jack's article I used it in my packages.
It was about 5 months ago.
But later I switched to a different solution.
Let's say I have a Data Conversion task "CNV" in my Data Flow.
In "CNV" Error Output I redirected rows to OLE DB Command
with a simple SQL:

UPDATE t1 SET DQ_CD = 'CNV' WHERE RECORD_ID = ?

That's it. Now if a record fails conversion
DQ_CD (Data Quality Code) gets updated to 'CNV' and this will tell me exactly what's wrong with the record.

Both Jack's and my solution have a drawback.
If your record has 45 columns you have to spend a lot of time investigating which column caused you the conversion problem.
I thought about having 3 "CNV" tasks.
One for numeric, one for characters and one for dates.
Then you can write:

CNV-Num: UPDATE t1 SET DQ_CD = 'CNV-Num' WHERE RECORD_ID = ?
CNV-Char: UPDATE t1 SET DQ_CD = 'CNV-Char' WHERE RECORD_ID = ?
CNV-Date: UPDATE t1 SET DQ_CD = 'CNV-Date' WHERE RECORD_ID = ?

It will probably save you some time while troubleshooting
but I'm still not using this approach.



Lempster
Lempster
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5099 Visits: 1657
Laura,

To set up a SQL Server Log Provider, do the following steps:

1. Open the package for which you want to configure logging in BIDS.
2. Select 'Logging...' from the SSIS menu.
3. Select 'SSIS log provider for SQL Server' from the Provider type drop-down box and click 'Add'.
4. Tick the checkbox on the newly-added line (to indicate that you want to use this new log provider).
5. Select a configuration - explanation below.
6. Choose the containers for which you want logging to be active.


The default location for SSIS logging using a SQL Server Log Provider is a table called dbo.sysdtslog90 in the msdb database, but you can choose any database as the logging repository. To do that you need to set up a Connection Manager for your chosen database and the dbo.sysdtslog90 table will be automatically created in that database. You then simply choose that database in the Configuration drop-down box.
(A more flexible option is to use Package Configurations to set your Connection Manager properties at runtime for example if you want to run the same package on multiple environments without having to edit the Connection Manager properties each time. You would then choose the name of the relevant package configuration in the Configuration drop-down box).

Have a look in Books Online under 'Implementing Logging in Packages' for more info.

HTH
Lempster
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search