Nomatch output from Lookup task in SSIS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Nomatch output from Lookup task in SSIS

Author

Topic

rcorp
Starting Member

12 Posts

Posted - 2012-07-30 : 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

52326 Posts

Posted - 2012-07-30 : 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 - 2012-07-30 : 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

52326 Posts

Posted - 2012-07-30 : 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 - 2012-07-30 : 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

52326 Posts

Posted - 2012-07-30 : 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 - 2012-07-30 : 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

52326 Posts

Posted - 2012-07-30 : 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 - 2012-07-30 : 20:38:08

I am connecting the nomatch output flow to a derived column task

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 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 - 2012-07-31 : 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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources