tailieunhanh - Microsoft SQL Server 2005 Developer’s Guide- P42

Microsoft SQL Server 2005 Developer’s Guide- P42:SQL Server 2005 is a feature-rich release that provides a host of new tools and technologies for the database developer. This book is written to help database developers and DBAs become productive immediately with the new features and capabilities found in SQL Server 2005. | Chapter 10 SQL Server Integration Services 389 In this case it will also match the ProductID column of the Product table which will later be used to perform a data lookup. After all of the data type changes have been made click the OK button to save the Flat File property changes. The next step is to set up the database lookup that will be used to verify that the vendor-supplied product numbers are correct. In this example the AdventureWorks product number is supplied in the AdWProductID field that s found in the FTP output file. If the value for the AdWproductID matches a value from the Production .ProductID column then the data will be written to the SQL Server destination table. Otherwise the data will be written to an error file. To define the Lookup doubleclick the Lookup transformation on the data flow design surface. This will display the Lookup Transformation Editor shown in Figure 10-15. Figure 10-15 Lookup Transformation Editor Select Connection Manager 390 Microsoft SQL Server 2005 Developer s Guide Under the Reference Table tab use the drop-down box to select the OLE DB Connection Manager that was created earlier. Here you can see that the OLE DB Connection Manager is set to . After assigning the connection the next step is to specify the table or query that will be used for the lookup operation. This example uses the table from the AdventureWorks database. After selecting the table the next step is to specify the columns that will be used in the lookup. To select the columns click the Columns tab as is shown in Figure 10-16. On the Columns tab first select the column from the list of Available Input columns that you will use to perform the lookup. Then drag that column over to the matching column in the Available Lookup Columns list. In Figure 10-16 you can see that the value from the incoming AdWProductID column will be used to look up values in the ProductID column from the table that was