SSIS Lookup fails eventhough record match is correct

Question

Error Message:

Reason: Fail - Row yielded no match during lookup. SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "LKP Staging and Location" failed because error code 0xC020901E occurred, and the error row disposition on "LKP Staging and Location.Outputs[Lookup Match Output]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "LKP Staging and Location" (2) failed with error code 0xC0209029 while processing input "Lookup Input" (16). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

I am reading data from Excel and storing it into Statging table. The same data is stored into another table called Location.Now by using lookup i am matching both data and trying to take identity value from Location table.

So data is same, but i am not sure why it is failing. If there is no match i am failing the task. Since same data is stored in both table it should match right?

The package is running successfully in development DB, but the same package not running in SIT DB.

In Dev i am using SQL authentication and in SIT Windows authentication.

Any suggestion?

Solution 1

After few analysis found the answer myself. Two things to be noted here, Even though values are same in both table, if the data type is different (char in 1st table and varchar in 2nd table) error will be thrown saying that no match.

In lookup properties set validate metadata to false. Because it is validating metadata as well. This should be done if you are so sure that both table structure is sync.

The above 2 check have solved my issue.

Solution 2

In my case it was simply because I connected the "No match output" from the lookup component and didn't set to "Redirect rows to no match output" in the General section.

Comments

[JoeK]

I'm seeing this, but the Source data joins fully to the Lookup table data ??