Upgrade Lookup Transformations
When you upgrade from SQL Server 2005 to SQL Server 2012 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 available caching types are full caching, partial caching, and no caching. In SQL Server 2012 Integration Services (SSIS), you can configure a Lookup transformation to use one of these caching types. For more information about how to implement partial caching or no caching, see Implement a Lookup in No Cache or Partial Cache Mode. For information about how to implement full caching, see Implement a Lookup Transformation in Full Cache Mode Using the Cache Connection Manager Transformation and Implement a Lookup Transformation in Full Cache Mode Using the OLE DB Connection Manager.
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 2012 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 2012 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.
For more information, see Lookup Transformation Editor (General Page).