1

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?

asmgx
  • 7,328
  • 15
  • 82
  • 143

2 Answers2

2

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.

Daniel Cai
  • 446
  • 2
  • 7
  • Macaulay i am getting different error for this one [The date-time format for @[User::XMLPeriod] is invalid, or value is outside the supported range.] – asmgx Jun 07 '18 at 00:04
  • @asmgx are you passing value to that variable? This error will come even when null or empty string passed to date type.. – Arun Vinoth-Precog Tech - MVP Jun 07 '18 at 03:25
  • @ArunVinoth I am using this expression for the variable << DATEADD("DD", -7, GetDate() ) >> – asmgx Jun 07 '18 at 04:22
  • @asmgx where did you specify this variable expression? Is " [The date-time format for @[User::XMLPeriod] is invalid, or value is outside the supported range." the exact error message reported by our software? – Daniel Cai Jun 07 '18 at 04:31
  • @DanielCai I specified it in the variable expression section – asmgx Jun 07 '18 at 04:33
  • @asmgx please reach out to our technical support using the contact information on http://www.kingswaysoft.com/about-us/contact-us, someone from our team will have a look of the situation with you remotely. This should be a simple issue. – Daniel Cai Jun 07 '18 at 13:47
  • @DanielCai the website you reffered to me has problems it cant take script apprantly and crashes when i send the message getting "Server Error in '/' Application." :) – asmgx Jun 07 '18 at 22:37
  • i changed the variable value to "05/05/2018" but still getting the same error, yet if i just used this inside the XML directly it works fine.! – asmgx Jun 12 '18 at 03:03
  • 1
    Works for me! Thank you very much. – Mohammad Anini Feb 08 '19 at 08:47
1

I suggest that you create an SSIS variable like String as FetchXML.

You make your own FetchXML with that variable in that string in a previous step, so you can use the fetchxml variable directly in the expression setup:

I think you are using CozyRoc, so you can configure your Fetchxml variable in your component "Dynamic data Flow task plus", into the setup configuration through: "Your Dynamic data Flow task plus">Advanced>Expressions>["Name of your data Flow task plues"].[@[User::YourVariableFetchXml]

CSM
  • 21
  • 3
  • 1
    I tried to format your answer a little but the last part was a little confusing. You might have to [edit] things and make sure you are clear what is code and what is explanation. –  Dec 20 '18 at 22:20