May 13, 2008 at 3:11 pm
Jack,
Thanks for your approval. I will provide you not only with the Custom Control code but also the step-by-step instruction on how to create the control once it's completed.
May 14, 2008 at 9:57 am
Great, I love learning something new. I would suggest creating the control and then submit the instructions to SSC as a How To article, then we all benefit. Unless of course you plan on selling the custom component, which BTW would be fine with me as long as I get my copy free:D
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
May 14, 2008 at 10:49 am
Hi Jack,
That's a good suggestion. I will send our solution to SSC. I'm planning to do the following:
1) Re-use your table creation script to have the end-user create the underlying table structure.
2) Incorporate your script into a custom control. The control also has additional properties that the user can set:
a) OLE DB Destination connection - because at the end of the day we want to write to result to database table. Might as well combine the Script component and the OLE DB Destination component into one.
b) The option of launching a simple custom Windows application similar to a DataViewer which I will also write (more detail in the next step)
3) If the user sets option 2b to true then a custom DataViewer will pop up whenever (error) rows are being inserted into the database table above. The custom DataViewer will display the information that you would see if you were to use your SELECT query in the SQL Analyzer. The advantage of this custom DataViewer over the SQL Analyzer is that it will parse the XML and display the values in tabular format. I will write this custom DataViewer in such a way that we can leave it in the background without having to close and open it multiple times. Using the SQL Server 2005's SQL (Cache) Dependency feature, the data will refresh itself whenever there are changes to the table. Another feature that the custom DataViewer may offer in the case where input is coming from a flat file is to allow you to make change to the values parsed from the XML directly so you can quickly correct and save the data and retry your package right away.
SQL Server 2008 SSIS will be coming out with the SSIS Profiler which probably has these features built-in so I'm doing this only for the learning experience. Please feel free to brainstorm and provide any ideas or feedback. Your article has definitely been a great inspiration on my SSIS learning path.
July 25, 2008 at 10:05 am
I am trying to use your code to get the error rows. I am not too familiar with XML but when I pasted the code inside the script editor in script component, the following line becomes underlined in blue:
strErrorDetails = strErrorDetails & "name=" & _
ControlChars.Quote column.Name.Trim & ControlChars.Quote & _
" value=" & ControlChars.Quote & strValue & ControlChars.Quote & _
" /><field "
Is there anything missing?
July 25, 2008 at 10:16 am
aye_taur (7/25/2008)
I am trying to use your code to get the error rows. I am not too familiar with XML but when I pasted the code inside the script editor in script component, the following line becomes underlined in blue:strErrorDetails = strErrorDetails & "name=" & _
ControlChars.Quote column.Name.Trim & ControlChars.Quote & _
" value=" & ControlChars.Quote & strValue & ControlChars.Quote & _
" /><field "
Is there anything missing?
You a missing an ampersand (&). It should be:
strErrorDetails = strErrorDetails & "name=" & _
ControlChars.Quote & column.Name.Trim & ControlChars.Quote & _
" value=" & ControlChars.Quote & strValue & ControlChars.Quote & _
" /><field
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
July 25, 2008 at 2:08 pm
Thanks for the quick reply! However when I run the package, I am getting the following error:
[OLE DB Destination 1 [355]] Error: 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 48, semicolon expected".
Do you have any suggestions as to what I maybe doing wrong?
July 25, 2008 at 2:44 pm
It sounds like there is a replacement string in the xml like > (>) that is not terminated correctly. You can put a data viewer on the stream from the error code to the ole db destination and copy the data to notepad, save as xml, open in internet explorer and that will show you the problematic value.
I have actually upgraded the script a bit by adding a Reference to System.Web and adding Imports System.Web.HttpUtility and using the HTMLEncode function on strValue.
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
July 28, 2008 at 8:42 am
Can you upload the latest version of the script?
August 19, 2008 at 8:44 am
I am attaching the source for a custom component I have developed to do this task. I make no warranties about the code. Remember that the component needs to be put in DTS\Pipeline Components in you SQL Server install directory and also added to the GAC. The compiled DLL is in the zip file as well.
I know someone else had mentioned creating a component based on my earlier code, but I finally had enough copy and paste and dug in to write one. It really was easier than I thought once I had a good example to run from. The xml building is totally redone based on Josh Twist's XMLify Custom Component which I found through Jamie Thompson's excellent SSIS Junkie Blog[/url]
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
September 24, 2008 at 6:35 pm
Has anyone here discussed or thought about a system for creating your own readable error messages. I'm at a point now where my team and I have developed a fairly robust set of SSIS packages that are able to handle many different formats of data based on configurations and the file mask.
The challenge is when data quality issues are identified, how to communicate back to our business process owners something they can understand w/o hard coding every message inside the SSIS code (table driven). I'm looking all around but can't find anything, perhaps I could use the native SQL Server error tables?
Thoughts?
Ben Sullins
bensullins.com
Beer is my primary key...
September 24, 2008 at 7:23 pm
Ben Sullins (9/24/2008)
Has anyone here discussed or thought about a system for creating your own readable error messages. I'm at a point now where my team and I have developed a fairly robust set of SSIS packages that are able to handle many different formats of data based on configurations and the file mask.The challenge is when data quality issues are identified, how to communicate back to our business process owners something they can understand w/o hard coding every message inside the SSIS code (table driven). I'm looking all around but can't find anything, perhaps I could use the native SQL Server error tables?
Thoughts?
Ben,
I'm not sure what you mean. The code is in the custom component gives a readable error, granted "the data violates integrity constraints" won't mean much to a user, but you could translate it to "there are duplicate values".
What I am doing, and I think I posted the rdl on the thread, is create a report using the errors I have logged, which, while not perfect has helped me send reports to my users that tell them that a date or phone number is bad.
Can you give me an idea what you are looking for to send your users? Like an example of the information you want to give them.
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 16, 2008 at 2:12 pm
Jack,
In your article you instruct to create the following 5 output columns:
ErrorSource
ErrorDetails
ErrorDesc
ErrorTask
PackageTime
But in your Script you refer to ErrorCode column:
Row.ErrorDesc = ComponentMetaData.GetErrorDescription(Row.ErrorCode).Trim()
which is not available.
What it should be?
October 16, 2008 at 2:24 pm
The ErrorCode is part of the Input to the script component so you do not have to create it, it is already there as long as you have a synchronous input, which you do unless you changed it. The output columns you add are ADDED to row passed to the 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 16, 2008 at 2:48 pm
Thank you Jack.
Script was OK.
My fault. I didn't understand it well.
Jack, 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?
October 16, 2008 at 2:54 pm
I don't have too much XML experience.
How do I query [load_errors].error_details
so that it is more readible?
I mean reading a long XML string is not fun.
Viewing 15 posts - 31 through 45 (of 107 total)
You must be logged in to reply to this topic. Login to reply