Somewhere, you have trailing spaces, either in your source or your lookup.
Consider the following source query.
SELECT
D.SourceColumn
, D.Description
FROM
(
VALUES
(CAST('T' AS varchar(4)), 'T')
, (CAST('R' AS varchar(4)), 'R')
, (CAST('' AS varchar(4)), 'Empty string')
, (CAST(' ' AS varchar(4)), 'Blanks')
, (NULL, 'NULL')
) D (SourceColumn, Description);
For my lookup, I restricted the above query to just T, R and the Empty String rows.
You can see that for the 5 source rows, T, R and Empty String matched and went to the Match Output path. Where I used a NULL or explicitly used spaces, did not make a match.
If I change my lookup mode from Full Cache to Partial, the NULL continues to not match while the explicit spaces does match.
Wut?
In full cache mode, the Lookup transformation executes the source query and keeps the data locally on the machine SSIS is executing on. This lookup is going to be an exact match using .NET equality rules. In that case, '' will not match ' '.
However, when we change our cache mode to None or Partial, we will no longer be relying on the .NET matching rules and instead, we'll use the source Database's matching rules. In TSQL, '' will match ' '
To make your Full Cache mode work as expected, you will need to apply an RTRIM in your Source and/or Lookup transformation. If you are convinced RTRIM isn't working your source, add a Derived Column Transformation and then apply your RTRIM there but I find it's better to abuse the database instead of SSIS.
Biml
Biml, the Business Intelligence Markup Language, describes the platform for business intelligence. BIDS Helper, is a free add on for Visual Studio/BIDS/SSDT that we're going to use to transform a Biml file below into an SSIS package.
The following biml will generate the
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<OleDbConnection Name="CM_OLE" ConnectionString="Data Source=localhost\dev2012;Initial Catalog=tempdb;Provider=SQLNCLI11.0;Integrated Security=SSPI;" />
</Connections>
<Packages>
<Package ConstraintMode="Linear" Name="so_26719974">
<Tasks>
<Dataflow Name="DFT Demo">
<Transformations>
<OleDbSource
ConnectionName="CM_OLE"
Name="OLESRC Source">
<DirectInput>
SELECT
D.SourceColumn
, D.Description
FROM
(
VALUES
(CAST('T' AS varchar(4)), 'T')
, (CAST('R' AS varchar(4)), 'R')
, (CAST('' AS varchar(4)), 'Empty string')
, (CAST(' ' AS varchar(4)), 'Blanks')
, (NULL, 'NULL')
) D (SourceColumn, Description);
</DirectInput>
</OleDbSource>
<Lookup
Name="LKP POC"
OleDbConnectionName="CM_OLE"
NoMatchBehavior="RedirectRowsToNoMatchOutput"
>
<DirectInput>
SELECT
D.SourceColumn
FROM
(
VALUES
(CAST('T' AS varchar(4)))
, (CAST('R' AS varchar(4)))
, (CAST('' AS varchar(4)))
) D (SourceColumn);
</DirectInput>
<Inputs>
<Column SourceColumn="SourceColumn" TargetColumn="SourceColumn"></Column>
</Inputs>
</Lookup>
<DerivedColumns Name="DER Default catcher" />
<DerivedColumns Name="DER NoMatch catcher">
<InputPath OutputPathName="LKP POC.NoMatch" />
</DerivedColumns>
</Transformations>
</Dataflow>
</Tasks>
</Package>
</Packages>
</Biml>
