title: SSIS Variable expression evaluation during run time Unusual Behavior
body: <p>In SSIS I have a variable called "Contract" whose expression is as below</p>
  
  <pre><code>"SELECT C.CONTRACTNUM AS BL_CONTRACTNUM,  C.EXTREFERENCE AS BL_EXTREFERENCE,  C.REVISIONNUM AS BL_REVISIONNUM,  A.BL_SERIALNUM,  A.BL_DESCRIPTION,  A.BL_COMPANY,  A.BL_PARTNUM,  CASE WHEN  A.BL_WARRANTYSTARTDATE &lt; TO_DATE('01-01-1753','DD-MM-YYYY') THEN NULL ELSE to_char( A.BL_WARRANTYSTARTDATE, 'YYYY-MM-DD HH24:MI:SS') END AS BL_WARRANTYSTARTDATE,  CASE WHEN  A.BL_WARRANTYENDDATE &lt; TO_DATE('01-01-1753','DD-MM-YYYY') THEN NULL ELSE to_char( A.BL_WARRANTYENDDATE, 'YYYY-MM-DD HH24:MI:SS') END AS BL_WARRANTYENDDATE,  C.CUSTOMER BL_CUSTOMER,  A.BL_CONTRACTLINENUM,  A.BL_COVERAGE,  CASE WHEN  A.BL_ENDOFMAINTDATE &lt; TO_DATE('01-01-1753','DD-MM-YYYY') THEN NULL ELSE to_char( A.BL_ENDOFMAINTDATE, 'YYYY-MM-DD HH24:MI:SS') END AS BL_ENDOFMAINTDATE,  CASE WHEN  A.BL_ENDOFSALESDATE &lt; TO_DATE('01-01-1753','DD-MM-YYYY') THEN NULL ELSE to_char( A.BL_ENDOFSALESDATE, 'YYYY-MM-DD HH24:MI:SS') END AS BL_ENDOFSALESDATE,  CASE WHEN  A.BL_ENDOFSUPPORTDATE &lt; TO_DATE('01-01-1753','DD-MM-YYYY') THEN NULL ELSE to_char(A.BL_ENDOFSUPPORTDATE, 'YYYY-MM-DD HH24:MI:SS') END AS BL_ENDOFSUPPORTDATE,  A.BL_CINUM,  NULL as CIDESCRIPTION,  C.STATUS,  A.BL_ISLIGHTCI,  A.BL_LOCATION,  CASE WHEN  A.BL_CREATEDDATE &lt; TO_DATE('01-01-1753','DD-MM-YYYY') THEN NULL ELSE to_char( A.BL_CREATEDDATE, 'YYYY-MM-DD HH24:MI:SS') END AS BL_CREATEDDATE,  CASE WHEN  A.BL_CHANGEDATE &lt; TO_DATE('01-01-1753','DD-MM-YYYY') THEN NULL ELSE to_char( A.BL_CHANGEDATE, 'YYYY-MM-DD HH24:MI:SS') END AS BL_CHANGEDATE,  A.BL_CHANGEBY,  CASE WHEN  A.BL_LOCATIONCHANGEDATE &lt; TO_DATE('01-01-1753','DD-MM-YYYY') THEN NULL ELSE to_char( A.BL_LOCATIONCHANGEDATE, 'YYYY-MM-DD HH24:MI:SS') END AS BL_LOCATIONCHANGEDATEFROM MAXIMOMIS.BL_MSINV_CONTRACT CLEFT JOIN MAXIMOMIS.BL_MSINV_ALL_EQUIP A on C.CONTRACTNUM = A.BL_CONTRACTNUM AND C.REVISIONNUM = A.BL_REVISIONNUMLEFT JOIN MAXIMOMIS.BL_MSINV_MAXIMOCI CI ON CI.CINUM = a.BL_CINUM AND CI.SERIALNUMBER = a.BL_SERIALNUMWHERE C.EXTREFERENCE is not nullAND c.REVISIONNUM is not nullAND c.STATUS = 'APPR' " + (@[User::varLoadType] == "Daily" ?  "AND (C.MODIFIEDDATE &gt; (sysdate - " + @[User::varDays]  + "))"  : "")
  </code></pre>
  
  <p>When I evaluate this expression I get the below</p>
  
  <pre><code>SELECT C.CONTRACTNUM AS BL_CONTRACTNUM,C.EXTREFERENCE AS BL_EXTREFERENCE,C.REVISIONNUM AS BL_REVISIONNUM,A.BL_SERIALNUM,A.BL_DESCRIPTION,A.BL_COMPANY,A.BL_PARTNUM,CASE WHEN  A.BL_WARRANTYSTARTDATE &lt; TO_DATE('01-01-1753','DD-MM-YYYY') THEN NULL ELSE to_char( A.BL_WARRANTYSTARTDATE, 'YYYY-MM-DD HH24:MI:SS') END AS BL_WARRANTYSTARTDATE, CASE WHEN  A.BL_WARRANTYENDDATE &lt; TO_DATE('01-01-1753','DD-MM-YYYY') THEN NULL ELSE to_char( A.BL_WARRANTYENDDATE, 'YYYY-MM-DD HH24:MI:SS') END AS BL_WARRANTYENDDATE,C.CUSTOMER BL_CUSTOMER,A.BL_CONTRACTLINENUM, A.BL_COVERAGE,CASE WHEN  A.BL_ENDOFMAINTDATE &lt; TO_DATE('01-01-1753','DD-MM-YYYY') THEN NULL ELSE to_char( A.BL_ENDOFMAINTDATE, 'YYYY-MM-DD HH24:MI:SS') END AS BL_ENDOFMAINTDATE,CASE WHEN  A.BL_ENDOFSALESDATE &lt; TO_DATE('01-01-1753','DD-MM-YYYY') THEN NULL ELSE to_char( A.BL_ENDOFSALESDATE, 'YYYY-MM-DD HH24:MI:SS') END AS BL_ENDOFSALESDATE,CASE WHEN  A.BL_ENDOFSUPPORTDATE &lt; TO_DATE('01-01-1753','DD-MM-YYYY') THEN NULL ELSE to_char(A.BL_ENDOFSUPPORTDATE, 'YYYY-MM-DD HH24:MI:SS') END AS BL_ENDOFSUPPORTDATE,A.BL_CINUM,NULL as CIDESCRIPTION,C.STATUS,A.BL_ISLIGHTCI, A.BL_LOCATION,CASE WHEN  A.BL_CREATEDDATE &lt; TO_DATE('01-01-1753','DD-MM-YYYY') THEN NULL ELSE to_char( A.BL_CREATEDDATE, 'YYYY-MM-DD HH24:MI:SS') END AS BL_CREATEDDATE,CASE WHEN  A.BL_CHANGEDATE &lt; TO_DATE('01-01-1753','DD-MM-YYYY') THEN NULL ELSE to_char( A.BL_CHANGEDATE, 'YYYY-MM-DD HH24:MI:SS') END AS BL_CHANGEDATE,A.BL_CHANGEBY,CASE WHEN  A.BL_LOCATIONCHANGEDATE &lt; TO_DATE('01-01-1753','DD-MM-YYYY') THEN NULL ELSE to_char( A.BL_LOCATIONCHANGEDATE, 'YYYY-MM-DD HH24:MI:SS') END AS BL_LOCATIONCHANGEDATEFROM MAXIMOMIS.BL_MSINV_CONTRACT CLEFT JOIN MAXIMOMIS.BL_MSINV_ALL_EQUIP A on C.CONTRACTNUM = A.BL_CONTRACTNUM AND C.REVISIONNUM = A.BL_REVISIONNUM LEFT JOIN MAXIMOMIS.BL_MSINV_MAXIMOCI CI ON CI.CINUM = a.BL_CINUM AND CI.SERIALNUMBER = a.BL_SERIALNUM WHERE C.EXTREFERENCE is not null AND c.REVISIONNUM is not null AND c.STATUS = 'APPR' AND (C.MODIFIEDDATE &gt; (sysdate - 7))
  </code></pre>
  
  <p>When I run this above query in Oracle sql/developer tool. I get 18 rows.</p>
  
  <p>But when the variable "Contract" is used in an execute sql task during runtime it produces 3775 rows. I have set breakpoint and ensured that the query evaluates to the same value (in the watch window during debug) as above and it does.</p>
  
  <p>So  I am not confused as to why it wouldn't produce 18 rows instead produce 3775 rows during run time?</p>
  
  <p>What is even more confusing is, I have other variables with exact similar expression except the select and joins are different and they all work fine.</p>
  
  <p>I even checked the xml code of the dtsx package <code>&lt;DtsVariable&gt;</code> value is showing correctly. I am not sure what I am missing here, can someone please point out if anything is wrong?</p>