Using variables in "Dynamics CRM Source" fetchXML in SSIS

Question

I am building SSIS package in that package I am using "Dynamics CRM Source" control which uses fetchXML to retrieve data from MS Dynamics CRM.

This is my fetchXML:

<fetch> 
  <entity name='f1_workorder'> 
     <all-attributes /> 
     <filter type='and'> 
        <condition attribute='createdon' operator="on-or-after" value="5/5/2018" /> 
     </filter> 
  </entity> 
</fetch>

You can see that condition value is hard-coded "5/5/2018"

It suppose to be read from a variable called XMLPeriod.

I tried many ways to use the variable in the fetchXML with no luck. I tried this

<condition attribute='createdon' operator="on-or-after" value=@XMLPeriod />

and this

<condition attribute='createdon' operator="on-or-after" value="@XMLPeriod" />

and this

<condition attribute='createdon' operator="on-or-after" value="@[User::XMLPeriod]" />

and this

<condition attribute='createdon' operator="on-or-after" value=@[User::XMLPeriod] />

I get this error

'@' is an unexpected token. The expected token is '"' or '''. Line 5, position 71.

any idea how to get this working?

@ArunVinoth getting this error '@' is an unexpected token. The expected token is '"' or '''. Line 5, position 71. — asmgx
is this error coming for all 4 cases? IIRC 3rd option should work.. — Arun Vinoth - MVP
how are you determining a package variable before a Kingswaysoft source component? When I try to connect script component to Dynamics source, it says it has no inputs... — Don Cheadle

Daniel Cai Daniel Cai · Accepted Answer · 2018-06-06T13:31:25

As Arun has suggested in the comment, the third option (copied below) should work.

<condition attribute='createdon' operator="on-or-after" value="@[User::XMLPeriod]" />

If it is not working, please post the entire error message here, so that I can have a further look.

Using variables in "Dynamics CRM Source" fetchXML in SSIS

2 Answers