October 21, 2008 at 12:12 pm
Hi Jack,
I didn't invent any new Error Script components.
I used the same your Script.
And the results are good. I get all the errors logged.
load_error_idpackage_run_dateerror_desc
--------------------------------------------------
12008-10-21 13:59:37.000The data value violates integrity constraints.
22008-10-21 13:59:37.000The data value cannot be converted ...
32008-10-21 13:59:37.000The data value cannot be converted ...
There is only one little inconvenience for me.
I just realized that without having "FAIL package on Error"
my SSIS will never stop executing which is undesirable.
Can I stop execution of a package if an error occurred at "OLE DB Destination" step? After my OLE DB Destination--->Error Output Script
I'd like to have another Email Notification step and then I'd like to stop the execution of the package.
Here is my desired flow:
OLE DB Destination--->Error Output Script--->EmailNotification--->Stop execution
October 21, 2008 at 12:41 pm
I realize you did not create a new component, what I am saying is that I created a Custom Component for SSIS that you can add to the toolbox and drag and drop into your packages. With the script component you have to copy and past the script multiple times, etc...
To do what you want done, I would think you could populate a variable, say ErrorCount, increment it in your script, then after your data completes you would have 2 paths using Expression and Constraint as your type and if the ErrorCount variable > 0 go to an Email step and end the package and the other path would be if ErrorCOuntv = 0 continue package.
You need to realize that implementing it this way will still populate your destination tables, just without the rows you redirected.
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
October 21, 2008 at 1:02 pm
Jack,
My boss doesn't want me to build any SSIS packages based on
custom components. I personally would definitely give it a try but
he's the boss...
So everything looks good now except that my SSIS Logging stopped reporting any "OnError" events. I think all "OnError" events are now being intercepted by your Script.
That means I don't have a choice. I will have to turn off SSIS Logging
and completely switch to Error Script and rely on it. I liked SSIS Logging.
It was automated and very detailed. It was very convenient to monitor the progress of the package. For example, I used OnPipelineRowsSent event
and it reported how many rows were on each input and output for each task. Was kind of convenient.
But now it becomes incomplete. OnError event does not get logged.
So my plan now is to change [load_errors] table:
Rename it to "Messages"
Add "message" column and log package progress together with any errors that occur.
Like "Package BRANCH2.dtsx started" for example.
Or "214 records were processed"
October 21, 2008 at 2:08 pm
Your boss is being a little shortsighted as custom components can save a ton of time. You should still download mine for the source as it does a better job and you can adapt that to us in your script component.
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
October 21, 2008 at 2:17 pm
Hi Jack,
I tried to increment vGlobalErrorCount in ErrorDetails Script step
Variables.vGlobalErrorCount = 1
but got an error:
The collection of variables locked for read and write access is not available outside of PostExecute
October 23, 2008 at 6:48 am
This worked...
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub PostExecute()
Variables.vGlobalErrorCount = 1
'MsgBox("vGlobalErrorCount=" + vGlobalErrorCount.ToString)
End Sub
End Class
Now in my Control Flow I can check for vGlobalErrorCount
and Send an email if there was an INSERT error.
October 24, 2008 at 11:01 am
Hi Jack,
So how about that little bug?
My output column has
"MAIN & RUPERT, WINNIPEG, MAN."
value and insert into [load_errors] table fails with this error:
Error: 0xC0202009 at DFT_branch, OLE DB Destination [5537]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "XML parsing: line 1, character 80, illegal name character".
Information: 0x402090E0 at DFT_branch, OLE DB Destination [5537]: The final commit for the data insertion has ended.
As soon as I remove " & " character from "MAIN & RUPERT, WINNIPEG, MAN."
insert works.
Is it a bug?
What should I do with "&" character?
October 24, 2008 at 11:20 am
Here is what I found on MSDN about "&" character...
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1500001&SiteID=1
With XML the ampersand needs to be escaped as & a m p ; otherwise your data is not well-formed and any XML parser will reject it.
I guess I need to replace "&" in Error Script
but not sure how to do it. Anybody?
October 24, 2008 at 12:51 pm
This is why I keep asking you to go get the Custom Component I posted. I included the source code which fixes the problem with illegal characters. I don't have the code in front of me now, but if you go download it you will have it.
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
October 28, 2008 at 8:53 am
Jack,
I tried to open your "JDC.SSIS.Logging.sln"
in my Visual Studio 2005
and got this error:
"The selected file is a solution file but was created by a newer version of this application and can not be opened."
Is your project compatible with VS2005?
I'm new to Custom Components.
How do I install your Component in my Business Intelligence Development Studio?
October 28, 2008 at 9:06 am
Nope I did it in VS 2008, but I thought I had the compatability mode in 2005.
Sorry
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
October 28, 2008 at 11:27 am
Thanks for reply Jack.
I guess then I need to wait until the company converts to VS 2008...
Is there like an Express version of VS 2008 free of charge?
October 28, 2008 at 11:32 am
Yes there is C# Express and VB Express 2008 available.
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
November 4, 2008 at 11:44 am
Jack,
Will I be able to build your component using that Express C# or VB.NET VS2008? If I compile it using VS2008 Express will I be able to use that component in my BI Development Studio 2005?
Also,
Can you give us the instructions on how to build a component?
I tried to follow the instructions from
http://msdn.microsoft.com/en-us/library/ms136078.aspx
and
http://www.sqlservercentral.com/articles/SQL+Server+2005+-+SSIS/2795/[/url]
and it is kind of hard.
I'm new to this. I've never done a custom component in SSIS.
December 30, 2008 at 11:02 am
I have installed/upgraded your Logging component to 08. Got it working - pretty sweet.
I am adding some enhancements and upgraded to 2008 - do you want the source?
Cheers
http://twitter.com/widba
http://widba.blogspot.com/
Viewing 15 posts - 61 through 75 (of 107 total)
You must be logged in to reply to this topic. Login to reply