January 6, 2012 at 5:34 pm
I have a ssis package that I use a script component in it.
The reason I use is I use it to calculate a column value by another column.
The script works fine if my input column is not null, but if it is null, the script runs with a runtime error,
it says : Script component has encountered an exception in user code , the column has a null value.
But I do want null values in the table.
The script I have is like below:
public class ScriptMain : UserComponent
{
Int32 counter = 0;
Int32 baseNumber = 99000000;
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
if ((Row.DefaultOrgUnitID == null))
{
Row.NewPositionNumber = baseNumber + counter;
counter = counter + 10;
}
else
{
string orgID = Row.DefaultOrgUnitID.ToString();
int start = orgID.Length - 3;
string lastThreeDigits = orgID.Substring(start);
Row.NewPositionNumber = Convert.ToInt32("99" + lastThreeDigits + "000");
}
}
I also so the automated generated Buffer Wrapper.cs that inclues null :
public class Input0Buffer: ScriptBuffer
{
public Input0Buffer(PipelineBuffer Buffer, int[] BufferColumnIndexes)
: base(Buffer, BufferColumnIndexes)
{
}
public Int32 DefaultOrgUnitID
{
get
{
return Buffer.GetInt32(BufferColumnIndexes[0]);
}
}
public bool DefaultOrgUnitID_IsNull
{
get
{
return IsNull(0);
}
}
public Int32 NewPositionNumber
{
set
{
this[1] = value;
}
}
public bool NewPositionNumber_IsNull
{
set
{
if (value)
{
SetNull(1);
}
else
{
throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.");
}
}
}
new public bool NextRow()
{
return base.NextRow();
}
new public bool EndOfRowset()
{
return base.EndOfRowset();
}
}
What could be wrong, how to fix this, thanks!
January 6, 2012 at 8:43 pm
What does the script actually do? Chances are, you don't need a script for such a thing...
--Jeff Moden
Change is inevitable... Change for the better is not.
January 6, 2012 at 10:19 pm
well, the script is to update a column A based on a column B in the same table.
If column B value is null, then use A will be concatenate with formula based on basenumber + counter (increased by 10.)
if not null the A will be concatenate another formula.
Could you help?
January 8, 2012 at 4:16 pm
sqlfriends (1/6/2012)
well, the script is to update a column A based on a column B in the same table.If column B value is null, then use A will be concatenate with formula based on basenumber + counter (increased by 10.)
if not null the A will be concatenate another formula.
Could you help?
Apologies for the delay...
I don't know enough about your scripting language to write anything in it but I can read it to some extent once I'm told what the jist of the code is. Thanks for that.
Since your code is giving an error about NULLs being present, I suspect the following snippet in your code is allowing NULLs through...
if ((Row.DefaultOrgUnitID == null))
Like I said, I'm not sure about the scripting language you're using but, in SQL Server, even though you can write the code to compare a NULL like that, it won't work because you can't compare anything to a NULL. It will always return FALSE. If there's a method in your scripting language similar to the IS NULL condition is T-SQL, then you might want to try using that, instead.
Shifting gears and with the understanding that I'm "guessing" at what the code should be because I know nothing about the table you're working with, here's how it could be done using, perhaps, an SQL Server stored procedure or even just an SQL Task. You'll obviously need to double check it and replace dbo.YourTable with the real table name.
--===== This will handle where the DefaultOrgUnitID is NOT NULL
-- and must be executed BEFORE the code that handles the NULL's.
UPDATE dbo.YourTable
SET NewPositionNumber = CAST('99'+RIGHT(DefaultOrgUnitID,3)+'000' AS INT)
WHERE DefaultOrgUnitID IS NOT NULL
;
--===== This will handle where the DefaultOrgUnitID is NULL
WITH
cteDoIfNull AS
(
SELECT Counter = (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)-1)*10+99000000,
NewPositionNumber
FROM dbo.YourTable
WHERE DefaultOrgUnitID IS NULL
)
UPDATE cteDoIfNull
SET NewPositionNumber = Counter
;
--Jeff Moden
Change is inevitable... Change for the better is not.
January 8, 2012 at 4:26 pm
It seems that the issue is around this
if ((Row.DefaultOrgUnitID == null))
A null comparison will always return false (check here: http://msdn.microsoft.com/en-us/library/edakx9da.aspx )
Try this for a workaround
http://msdn.microsoft.com/en-us/library/ms172138(v=vs.80).aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 8, 2012 at 4:27 pm
I'm not sure of the entire reason behind doing all of this with a script component rather than with TSQL. I would lean more towards a sql script to do this.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 8, 2012 at 5:53 pm
Thanks, Jeff,
the query works perfect, I was not sure how to calculate the value by using a counter, now I learn from you that I can use row number over.
Thanks for the tip,
Thanks for other posters too.
January 8, 2012 at 7:25 pm
Thank you for the feedback. I usually don't like to write queries for tables I know nothing about and think I got pretty lucky on this one. Glad to have been able to help.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply