Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««7891011»»»

Error Handling in SSIS Expand / Collapse
Author
Message
Posted Friday, April 3, 2009 6:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 16, 2014 5:34 AM
Points: 5, Visits: 28
thx! I didn't read all 8 pages of the discussion...

grtz,
NIco
Post #689767
Posted Friday, April 3, 2009 8:53 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:01 PM
Points: 3,843, Visits: 3,836
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
Post #689985
Posted Friday, April 3, 2009 9:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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

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
Post #690055
Posted Saturday, April 4, 2009 10:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:13 AM
Points: 2,036, Visits: 1,380
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

Post #690486
Posted Saturday, April 4, 2009 10:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, July 13, 2014 5:53 PM
Points: 94, Visits: 575
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/
Post #690496
Posted Monday, April 6, 2009 3:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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

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
Post #690851
Posted Monday, April 6, 2009 4:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:13 AM
Points: 2,036, Visits: 1,380
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


  Post Attachments 
SSIS for SQL Server log provider.JPG (21 views, 141.40 KB)
Post #690860
Posted Tuesday, April 7, 2009 3:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 20, 2010 5:11 PM
Points: 1, Visits: 19
Would you give out step by step instruction on how to install and set up your error handling component? Thanks
Post #692569
Posted Tuesday, April 7, 2009 10:12 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 28, 2011 12:06 PM
Points: 557, 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.



Post #692732
Posted Wednesday, April 8, 2009 7:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:13 AM
Points: 2,036, Visits: 1,380
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
Post #693052
« Prev Topic | Next Topic »

Add to briefcase «««7891011»»»

Permissions Expand / Collapse