| Author |
 Topic  |
|
|
rcorp
Starting Member
12 Posts |
 Posted - 07/30/2012 : 15:21:47
|
Hi All,
I loaded my lookup values into a sql database. i am using lookup task to compare my source file column to lookup values in the sql database. My match output is fine however my no match output is Nulls for the lookup column. I want to capture the actual column value instead of null values. Can you please let me know how to achieve that. Thanks in advance. |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47136 Posts |
Posted - 07/30/2012 : 16:04:23
|
what do you mean by that? you'll have null values returned for column in non match lookup as it doesnt have amatching value. Are you trying to populate default value instead?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
rcorp
Starting Member
12 Posts |
Posted - 07/30/2012 : 17:01:33
|
yes, I wanted to capture the default value instead of Null.
My source file has data like below
PA,East,USA
CA,West,USA
FL,South,USA
WA,North,USA
and my lookup table in SQL database has the below information
Short_Name Long_Name
PA Pennsylvania
CA California
FL Florida
And I am using a lookup task to convert my column1 in the source file to Long_Name. When I execute my package my match output is like below (As expected)
Match Output from lookup
------- ------- ----- --------
Pennsylvania,East,USA
California,West,USA
Florida,South,USA
and my NoMatch output looks like below
,North,USA
What I am saying is if it wont find a match (in case of WA from sample data) I want to see my nomatch output as below
WA,North,USA
Appreciate your help Visakh. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47136 Posts |
Posted - 07/30/2012 : 17:14:13
|
for that you need to add a dervied column task after your nomatch output and add a new column with property set as replace long_name
the expression should be like
(LEN([long_name])>0)? [long_name]: [youroriginalcolumnnamefromfile])
and link this dervied column output to your destination
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
rcorp
Starting Member
12 Posts |
Posted - 07/30/2012 : 18:49:43
|
quote:
Originally posted by visakh16
for that you need to add a dervied column task after your nomatch output and add a new column with property set as replace long_name
the expression should be like
(LEN([long_name])>0)? [long_name]: [youroriginalcolumnnamefromfile])
and link this dervied column output to your destination
Hi Visakh,
In my lookup I am creating a new column(long_name) instead of replacing the actual column. The output coming from nomatch output has only 1 column (i.e, the actual column name. i dont see long_name since that was created for match output)
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47136 Posts |
Posted - 07/30/2012 : 19:29:05
|
quote:
Originally posted by rcorp
quote:
Originally posted by visakh16
for that you need to add a dervied column task after your nomatch output and add a new column with property set as replace long_name
the expression should be like
(LEN([long_name])>0)? [long_name]: [youroriginalcolumnnamefromfile])
and link this dervied column output to your destination
Hi Visakh,
In my lookup I am creating a new column(long_name) instead of replacing the actual column. The output coming from nomatch output has only 1 column (i.e, the actual column name. i dont see long_name since that was created for match output)
nope. you should be selecting source column as well as lookup column (long_name) from the lookup table
for no match output lookup table column will be null and thats where you use other columns value (ie column having WA value in example)
then you use source column in expressions
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
rcorp
Starting Member
12 Posts |
Posted - 07/30/2012 : 20:00:23
|
I do not see where to slect the source column. All I can do on the lookup window is to map a source column with a database column. but I can choose columns from lookup columns.
so my match output is source columns + long_name column (from stored db)
non matchoutput is source columns (kick outs).
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47136 Posts |
Posted - 07/30/2012 : 20:06:35
|
quote:
Originally posted by rcorp
I do not see where to slect the source column. All I can do on the lookup window is to map a source column with a database column. but I can choose columns from lookup columns.
so my match output is source columns + long_name column (from stored db)
non matchoutput is source columns (kick outs).
and whats the following task after no match lookup?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
rcorp
Starting Member
12 Posts |
Posted - 07/30/2012 : 20:38:08
|
I am connecting the nomatch output flow to a derived column task
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47136 Posts |
Posted - 07/30/2012 : 21:32:28
|
in that you need to include source column and link it to your destinations long_name column
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
rcorp
Starting Member
12 Posts |
Posted - 07/31/2012 : 16:05:45
|
Hi visakh,
I got it resolved. For some reason it was not giving the right data yesterday. I created data viewwers in the flow and it is working fine now.
Edit: Just to clear things up, I am recieving the nomatch output as I intended. No other transformations required.
Thanks and appreciate your help. Can I give points to you? I couldnt find it. |
Edited by - rcorp on 07/31/2012 16:10:07 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47136 Posts |
Posted - 07/31/2012 : 16:43:58
|
there's no points system here. you can just appends [Resolved] to topic title if you want to indicate that this is closed
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Nomatch output from Lookup task in SSIS
