Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Modifying SQL in a Lookup Transform

    Oftentimes you need to make a more advanced join that the default LookUp Transform will allow. The challenge is when you go to the advanced option in the left hand menu, the SQL Statement is grayed out as shown below.

    clip_image001

    This is because the SQL cannot be modified when the cache setting is set to Full Cache. If you wish to modify the SQL, you’ll need to modify the General Portion of the dialog to Partial or no cache. There will be some performance tradeoff, but will give you some serious flexibility.

    Possible Reasons for this type of change would include:

  1. Joining between two dates to get an effective date range match.
  2. Looking up based on a parameter.
  3. Pass in a Join based on a loop parameter.

Whatever the reason, once you remove the Full Cache setting, you can also make cache changes here on the same tab. There are some good recommendations for this setting based on your particular application. Check out the other articles on BIDN.com for more details on this subject. Once you’re Cache setting is correct, your page should look like this.

    clip_image002

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.