SQL Server Performance

LookUp Transformation in SSIS

Discussion in 'SQL Server 2005 Integration Services' started by Vel, Feb 1, 2010.

  1. Vel New Member

    I have a lengthy query including case statements
    I want create lookup transformation for those case statements
    Plz any help me to do this one
    Thank!
    Vel
  2. dineshasanka Moderator

    we need more information like query, table structure
  3. satya Moderator

    <P mce_keep="true">Welcome to the forums.</P><P mce_keep="true">As Dinesh referred we need more information, to the point on the subject you can configure the Lookup transformation to use full cache mode and a Cache connection manager. In full cache mode, the reference dataset is loaded into cache before the Lookup transformation runs.</P><P mce_keep="true">Books Online (installed from SQL setup) refers on How-TO:</P><P mce_keep="true">To implement a Lookup transformation in full cache mode in one package by using Cache connection manager and a data source in the data flow</P><DIV class=section><OL><LI><?XML:NAMESPACE PREFIX = [default] http://ddue.schemas.microsoft.com/authoring/2003/5 NS = "http://ddue.schemas.microsoft.com/authoring/2003/5" /><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5"><P xmlns="">In Business Intelligence Development Studio, open a Integration Services project, and then open a package.</P></content><LI><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5"><P xmlns="">On the <B>Control Flow</B> tab, add two Data Flow tasks, and then connect the tasks by using a green connector:</P></content><LI><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5"><P xmlns="">In the first data flow, add a Cache Transform transformation, and then connect the transformation to a data source.</P><P xmlns="">Configure the data source as needed.</P></content><LI><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5"><P xmlns="">Double-click the Cache Transform, and then in the <B>Cache Transformation Editor</B>, on the <B>Connection Manager</B> page, click <B>New</B> to create a new Cache connection manager.</P></content><LI><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5"><P xmlns="">Click the <B>Columns</B> tab of the <B>Cache Connection Manager Editor</B> dialog box, and then specify which columns are the index columns by using the <B>Index Position</B> option.</P><P xmlns="">For non-index columns, the index position is 0. For index columns, the index position is a sequential, positive number.</P><DIV class=alert xmlns=""><TABLE cellSpacing=0 cellPadding=0 width="100%"><TBODY><TR><TH align=left><IMG class=note src="ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10is_1devconc/local/note.gif">Note: </TH></TR><TR><TD>When the Lookup transformation is configured to use a Cache connection manager, only index columns in the reference dataset can be mapped to input columns. Also, all index columns must be mapped. For more information, see <?XML:NAMESPACE PREFIX = MSHelp NS = "http://msdn.microsoft.com/mshelp" /><MSHelp:link tabIndex=0 xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5" keywords="0d8f9324-0c35-4eea-b06d-da3cc2426d2c" filterString='(("ProductVers"="kbsqlserv105") OR ("DocSet"="NETFramework"))'>Cache Connection Manager Editor</MSHelp:link>. <P mce_keep="true">&nbsp;</P></TD></TR></TBODY></TABLE><P mce_keep="true">&nbsp;</P></DIV></content><LI><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5"><P xmlns="">To save the cache to a file, in the <B>Cache Connection Manager Editor</B>, on the <B>General</B> tab, configure the Cache connection manager by setting the following options:</P><UL xmlns=""><LI>Select <B>Use file cache</B>.<BR><BR><LI>For <B>File name</B>, either type the file path or click <B>Browse</B> to select the file.<BR><BR>If you type a path for a file that does not exist, the system creates the file when you run the package.<BR><BR></LI></UL><DIV class=alert xmlns=""><TABLE cellSpacing=0 cellPadding=0 width="100%"><TBODY><TR><TH align=left><IMG class=note src="ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10is_1devconc/local/note.gif">Note: </TH></TR><TR><TD>The protection level of the package does not apply to the cache file. If the cache file contains sensitive information, use an access control list (ACL) to restrict access to the location or folder in which you store the file. You should enable access only to certain accounts. For more information, see <MSHelp:link tabIndex=0 xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5" keywords="2e3ddea9-5289-4289-a70e-11c018f34977" filterString='(("ProductVers"="kbsqlserv105") OR ("DocSet"="NETFramework"))'>Controlling Access to Files Used by Packages</MSHelp:link>. <P mce_keep="true">&nbsp;</P></TD></TR></TBODY></TABLE><P mce_keep="true">&nbsp;</P></DIV></content><LI><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5"><P xmlns="">Configure the Cache Transform as needed. For more information, see <MSHelp:link tabIndex=0 keywords="cec4f3d8-a767-41cb-af67-166d5cdde783" filterString='(("ProductVers"="kbsqlserv105") OR ("DocSet"="NETFramework"))'>Cache Transformation Editor (Connection Manager Page)</MSHelp:link> and <MSHelp:link tabIndex=0 keywords="ffd53f18-9646-458a-a84a-f2467d601ea5" filterString='(("ProductVers"="kbsqlserv105") OR ("DocSet"="NETFramework"))'>Cache Transformation Editor (Mappings Page)</MSHelp:link>.</P></content><LI><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5"><P xmlns="">In the second data flow, add a Lookup transformation, and then configure the transformation by doing the following tasks:</P><OL xmlns=""><LI>Connect the Lookup transformation to the data flow by dragging a connector—the green or red arrow—from a source or a previous transformation to the Lookup transformation.<BR><BR><DIV class=alert><TABLE cellSpacing=0 cellPadding=0 width="100%"><TBODY><TR><TH align=left><IMG class=note src="ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10is_1devconc/local/note.gif">Note: </TH></TR><TR><TD>A Lookup transformation might not validate if that transformation connects to a flat file that contains an empty date field. Whether the transformation validates depends on whether the connection manager for the flat file has been configured to retain null values. To ensure that the Lookup transformation validates, in the <B>Flat File Source Editor</B>, on the <B>Connection Manager Page</B>, select the <B>Retain null values from the source as null values in the data flow</B> option. <P mce_keep="true">&nbsp;</P></TD></TR></TBODY></TABLE><P mce_keep="true">&nbsp;</P></DIV><LI>Double-click the source or previous transformation to configure the component.<BR><BR><LI>Double-click the Lookup transformation, and then in the <B>Lookup Transformation Editor</B>, on the <B>General</B> page, select <B>Full cache</B>.<BR><BR><LI>In the <B>Connection type</B> area, select <B>Cache connection manager</B>. <BR><BR><LI>From the <B>Specify how to handle rows with no matching entries</B> list, select an error handling option.<BR><BR><LI>On the <B>Connection</B> page, from the <B>Cache connection manager</B> list, select a Cache connection manager.<BR><BR><LI>Click the <B>Columns</B> page, and then drag at least one column from the <B>Available Input Columns</B> list to a column in the <B>Available Lookup Column</B> list. <BR><BR><DIV class=alert><TABLE cellSpacing=0 cellPadding=0 width="100%"><TBODY><TR><TH align=left><IMG class=note src="ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10is_1devconc/local/note.gif">Note: </TH></TR><TR><TD>The Lookup transformation automatically maps columns that have the same name and the same data type. <P mce_keep="true">&nbsp;</P></TD></TR></TBODY></TABLE><P mce_keep="true">&nbsp;</P></DIV><DIV class=alert><TABLE cellSpacing=0 cellPadding=0 width="100%"><TBODY><TR><TH align=left><IMG class=note src="ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10is_1devconc/local/note.gif">Note: </TH></TR><TR><TD>Columns must have matching data types to be mapped. For more information, see <MSHelp:link tabIndex=0 xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5" keywords="896fc3e8-3aa6-4396-ba82-5d7741cffa56" filterString='(("ProductVers"="kbsqlserv105") OR ("DocSet"="NETFramework"))'>Integration Services Data Types</MSHelp:link>. <P mce_keep="true">&nbsp;</P></TD></TR></TBODY></TABLE><P mce_keep="true">&nbsp;</P></DIV><LI>In the <B>Available Lookup Columns</B> list, select columns. Then in the <B>Lookup Operation</B> list, specify whether the values from the lookup columns replace values in the input column or are written to a new column.<BR><BR><LI>To configure the error output, click the <B>Error Output</B> page and set the error handling options. For more information, see <MSHelp:link tabIndex=0 keywords="15d53bb0-8be1-46fb-b459-04a397e75fac" filterString='(("ProductVers"="kbsqlserv105") OR ("DocSet"="NETFramework"))'>Lookup Transformation Editor (Error Output Page)</MSHelp:link>.<BR><BR><LI>Click <B>OK</B> to save your changes to the Lookup transformation.<BR><BR></LI></OL></content><LI><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5"><P xmlns="">Run the package.</P></content></LI></OL></DIV></ xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5" 2003 authoring ddue.schemas.microsoft.com 5:content><P xmlns="">&nbsp;FYI</P></ 2003 authoring ddue.schemas.microsoft.com 5:content>

Share This Page