July 16, 2004 at 11:09 am
I want to data pump info in from an Excel file but use a "default" value when the column is empty. All I'm getting is an integrity violation because the data pump is trying to insert a null into a non-null column. I tryied using VB in the transformation like this:
Function Main()
If IsNull(DTSSource("col").Value) = False Then
DTSDestination("col") = DTSSource("col")
End If
Main = DTSTransformStat_OK
End Function
July 19, 2004 at 8:00 am
This was removed by the editor as SPAM
July 20, 2004 at 10:42 am
Don't you need an ELSE statement in there that puts something in there if IsNull(DTSSource("col").Value) = True
???
At the moment I think this will just pull through a NULL if DTSSource("col") is null because you haven't told it to do anything else.
Regards
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
July 20, 2004 at 1:17 pm
The above comment is correct you need to include a logical else and tell it what to do if it finds a null.
============================
Richard S. Hale
Senior Database Marketing Developer
Business Intelligence
The Scooter Store
RHale@TheScooterStore.com
DSK: 830.627.4493
July 21, 2004 at 9:22 am
Boy I hate M$ idea of a programming language. From a logical stand-point, I can have columns on both sides that are unused, but not include them in a transformation. However, if I connect them with an ActiveX transform, I can't say "transform/run copy only when the column is not null". I assumed that would implicitly state that "don't do anything/don't include these two if the column is null".
Maybe there's a way to say "if is null, set destination = destination's default"?
July 22, 2004 at 9:37 am
Boy, I tried and tried to get this to work just using a transform data task. I couldn't and it still bugs me. If you didn't find a work around yet I did this:
For the ActiveX transform:
Function Main()
If not IsNull(DTSSource("col")) Then
DTSDestination("col") = DTSSource("col")
else
DTSDestination("col") = "default"
End If
Main = DTSTransformStat_OK
End Function
Then on success of the data transfer do a SQL task:
update tableName
set col = (select replace(replace(replace(column_default,'''',''),')',''),'(','')
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'tableName'
and column_name = 'col')
where col = 'default'
Yuck, I know
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply