Hands-On Microsoft SQL Server 2008 Integration Services part 35. Deploy and manage high-performance data transformation solutions across your enterprise using the step-by-step techniques in this fully revised guide. Hands-On Microsoft SQL Server 2008 Integration Services, Second Edition explains the tools and methods necessary to extract conclusive business intelligence from disparate corporate data. Learn how to build and secure packages, load and cleanse data, establish workflow, and optimize performance. Real-world examples, detailed illustrations, and hands-on exercises are included throughout this practical resource. . | 318 Hands-On Microsoft SQL Server 2008 Integration Services Hands-On Extending the Contacting Opportunities Package with Property Expressions In Chapter 4 you created an Integration Services project called Contacting Opportunities that sends mail to persons who raised a query in October 2009. However during the package development you used static values in the To field Subject field and Message field. You are now to extend the package so that these values are read from the table and evaluated for each person to create personalized messages. Method You will add the Mailing Opportunities package from the Contacting Opportunities package to a new package to keep the package separate from a learning point of view. The Mailing Opportunities package sends mails to six persons who made enquiries in October 2009 using static values. To make the package dynamic you will update e-mail addresses in the Prospects table with your e-mail address so that you can see the messages that are being generated and sent out by this package. Then you will create variables that will capture values from the Prospects table to pass on to property expressions at run time. Finally you will create property expressions and will also learn about the DelayValidation property toward the end of this exercise. Exercise Build Property Expressions for Mailing Opportunities Package Having understood the method follow the steps below to work with property expressions. 1. Open SQL Server Management Studio and run the following query on the local server. UPDATE Campaign . dbo . Prospects SET email youremailaddress WHERE ENQUIRYDATE BETWEEN 2009 10 01 AND 2009 10 31 Replace youremailaddress in the preceding query with your e-mail address and then execute this query to update the six records. 2. Open BIDS and create a new Integration Services project with the following details Name Contacting Opportunities with Property Expressions Location C SSIS Projects Chapter 8 Advanced Features of Integration Services
