Lookup+ Component NULL/NoMatch from variable?

[Tim Huemmer]

I would like to know how I can set the NULL and/or No Match values in
the Lookup+ to a value based on a variable. The data type is GUID so
I need to know what data type I should use in my variable as well
since you cannot create a variable in SSIS with type GUID.

Thanks,

Tim

[Ivan Peev]

Hi Tim,

Setting these parameters from variable is not supported at this time.
Can you elaborate why do you need this capability? Thank you!

Ivan

[Tim Huemmer]

Here is the scenario:

I am wanting to default a MSCRM lookup value (GUID) to a specific
value if the lookup doesn't match anything.

We have a development and production environment and the GUIDs are
different in those environments. I would like to store these values
in a database so the package may simply be moved from one environment
to the next without having a hardcoded GUID value that needs to be
updated each time the package is updated/tested.

> > Tim- Hide quoted text -
>
> - Show quoted text -

[Ivan Peev]

Hi Tim,

There is a quick workaround, which will allow you to setup custom
match/no match value for the Lookup Plus. The trick is to expose the
property containing the Lookup PLus component setup available for
setup with SSIS expression. Here is how to do it:

1. Open the package containing the Lookup Plus component in a text
editor.
2. Search for LookupMetadataXml. This property contains the setup.
3. Change expressionType attribute for this property to be like this:
expressionType="Notify"
4. Change typeConverter attribute for this property to be like this:
typeConverter=""
5. Save and open the package in the SSIS designer.

Now you should have to the property containing the setup available for
use in dynamic SSIS expression. Let me know if this solution works for
you.

[Tim Huemmer]

Ivan,

Thanks for the suggestion. I followed the steps you suggested and now
I can see the XML configuration for the component in SSIS designer.
My question is how do I modify this property from script?

What I would like to do is the following:

1. Read the LookupMetadataXml
2. Use script to replace the existing Guids with ones that come from
a variable (this should be easy to do using a xpath query)
3. Replace the LookupMetadataXml on the component with the modified
XML

The lookup+ component has 9 steps on it. I want the customer to be
able add additional steps to this component and not break my logic to
replace the hard-coded GUIDs

> > > - Show quoted text -- Hide quoted text -

[Ivan Peev]

Hi Tim,

Unfortunately you cannot exactly follow the steps you have described.
What you have to do is have the complete LookupMetadataXml data stored
outside in your configuration file. Then you have to setup the
following expression on the LookupMetadataXml property:

REPLACE( @[User::DefaultLookupXml], "{DefaultGuid}",
@[User::ProductionGuid] )

Because the GUID is very unique, you don't have to do any special
parsing to do the replacement. The above function will work just fine.

p.s.
Keep in mind the SSIS expressions cannot process text longer than 4000
characters.

[Tim Huemmer]

Ivan,

Thanks for your help. I think that I can work with this. Adding
another lookup in the future will be more complicated, but at least I
can store these values as variables in the configuration database
now! I also appreciate the warning in regards to the 4000 character
limit. I ended up having to split up my single Lookup+ step into
multiple steps since my lookupXML was 4080 characters.

Tim