December 30, 2008 at 12:56 pm
Yeah, that would be great!
Glad you could find it useful.
Jack Corbett
Consultant - Straight Path Solutions
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
March 19, 2009 at 10:19 am
Just for reference if anyone comes to this point - the 2008 Version of Jack's code with some enhancements is documented here:
http://qa.sqlservercentral.com/articles/SSIS/65758/
Cheers
http://twitter.com/widba
http://widba.blogspot.com/
April 3, 2009 at 1:27 am
Nice article Jack, and discussion also!
April 3, 2009 at 3:13 am
What version is being used?
I'm doing something similar in 2008 version, but it crashes.
Posted a bug report @ microsoft, but still no reply...
grtz,
Nico
April 3, 2009 at 6:00 am
Thanks for the positive feedback dg.
Nico,
This article deals with 2005. In the post just before yours there is a link to an article for 2008. Here it is again, http://qa.sqlservercentral.com/articles/SSIS/65758/
Also, if people missed it in the discussion, I did write a custom component for 2005 that is available, including source, here.
Jack Corbett
Consultant - Straight Path Solutions
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
April 3, 2009 at 6:07 am
thx! I didn't read all 8 pages of the discussion... 🙂
grtz,
NIco
April 3, 2009 at 8:53 am
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
April 3, 2009 at 9:48 am
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
Consultant - Straight Path Solutions
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
April 4, 2009 at 10:28 am
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
April 4, 2009 at 10:58 am
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/
April 6, 2009 at 3:47 am
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
Consultant - Straight Path Solutions
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
April 6, 2009 at 4:04 am
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
April 7, 2009 at 3:30 pm
Would you give out step by step instruction on how to install and set up your error handling component? Thanks
April 7, 2009 at 10:12 pm
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.
April 8, 2009 at 7:16 am
Laura,
To set up a SQL Server Log Provider, do the following steps:
[font="System"]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.[/font]
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
Viewing 15 posts - 76 through 90 (of 107 total)
You must be logged in to reply to this topic. Login to reply