October 21, 2008 at 11:35 am
Hello all,
I have a script component inside a data flow. The input into the script component has alot of columns. I need to check every column for null and set it to a value based on it. Since script component handles a row at a time the ideal way I'd like to do it would be looping through all the columns and checking for row.fieldname_isnull is true and setting the value of that row to what I need it to be.
Unfortunately, it looks like there's no column property similiar to .NET's datatable object unless I'm missing it. Can this be done?
Thanks,
Strick
October 21, 2008 at 12:03 pm
Unfortunately there is not a straightforward way of doing this. Here is a link to a blog post (not my blog) about how to do it:
You could also look at this article (which is mine), http://www.sqlservercentral.com/articles/Integration+Services/62662/ which has some code that loops through the input columns. It is based on information I got in the blog post I noted earlier.
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:56 pm
Great thanks!
I think this is what I need. Question though; have you ever found a way to step through the code (F10 key) in script component like you can do in script task. I'd really love to see what values are being returned as its happening. This gives me a really good visual of what's being returned in the code.
Thanks,
Strick
October 21, 2008 at 2:06 pm
Glad I could help.
Unfortunately there is no debugging in Script Components like there is in the script task. A real oversight in my opinion.
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 4:34 pm
Hi,
As a test I copied the info from the site word for word and pasted it into my script ocmponent:
Dim column As IDTSInputColumn90
Dim rowType As Type = Row.GetType()
Dim columnValue As PropertyInfo
For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection
columnValue = rowType.GetProperty(column.Name)
If columnValue.GetValue(Row, Nothing).ToString() = "January" Then
columnValue.SetValue(Row, String.Empty, Nothing)
End If
Next
It is failing at the line in bold and i'm not sure why. I threw it in a Try Catch block to get some error info and here is what I got:
Exception: System.NullReferenceException
Exception Message: Object reference not set to an instance of an object.
Once I can get the value I know all will be well but I been trying to figure this out for a few hours.
Thanks,
Strick
October 21, 2008 at 6:55 pm
You need to do a check for a null value BEFORE the ToString() function is used. You can't convert a NULL to a string. Somthing like:
If Not IsNothing(columnValue.GetValue(Row, Nothing)) Then
If columnValue.GetValue(Row, Nothing).ToString() = "January" Then
columnValue.SetValue(Row, String.Empty, Nothing)
End If
End If
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply