Upgrade Lookup Transformations

When you upgrade from SQL Server 2005 to SQL Server 2014 consider modifying packages to take advantage of the new features in the Lookup Transformation. The transformation supports the caching types and data output options available in SQL Server 2005 Integration Services (SSIS). For more information about additional the caching and data outputs, see Lookup Transformation.

In SQL Server 2005 Integration Services (SSIS), the Lookup transformation had an input, an output, and an error output. Rows in the input that had matching entries in the reference dataset were handled by the output. Rows in the input that did not have matching entries were treated as errors and could be redirected to the error output. In SQL Server 2014 Integration Services (SSIS), the Lookup transformation has two outputs: a match output and a no match output.

By default, when you run a Lookup transformation that was created in SQL Server 2005, SQL Server 2014 Integration Services (SSIS) treats the rows without matching entries as errors and enables you to redirect the rows to an error output. You have the option of configuring the Lookup transformation to treat the rows as non-errors and redirect the rows to the no match output.