title: BIML Parameterized query on Ole db source
body: <p>I'm building multiple SSIS packages with BIML and i have to retrieve data from an OLE DB Source between a date interval. Here's the xml that is going to generate the packages:</p>
  
  <pre><code>&lt;Dataflow Name="DFT Insert into &lt;#=TableName#&gt;"&gt;
      &lt;Transformations&gt;
          &lt;OleDbSource Name="Retreive from Source (&lt;#=TableName#&gt;)" ConnectionName="AS400"&gt;
              &lt;DirectInput&gt;
                  SELECT s.* 
                  FROM &lt;#=TableSchema#&gt;.&lt;#=TableName#&gt; s
                  WHERE s.date &amp;gt; ? AND s.date &amp;lt;= ?
              &lt;/DirectInput&gt;
              &lt;Parameters&gt;
                  &lt;Parameter Name="0" VariableName="User.StartDate"/&gt;
                  &lt;Parameter Name="1" VariableName="User.MiddleDate"/&gt;
              &lt;/Parameters&gt;
          &lt;/OleDbSource&gt;
  
          &lt;OleDbDestination Name="Insert into Destination (&lt;#=TableName#&gt;)" ConnectionName="DB2Mirror" KeepNulls="true" CheckConstraints="false"&gt;
              &lt;ExternalTableOutput Table="[&lt;#=TableSchema#&gt;].[&lt;#=TableName#&gt;]" /&gt;
          &lt;/OleDbDestination&gt;   
      &lt;/Transformations&gt;
  &lt;/Dataflow&gt;
  </code></pre>
  
  <p>This structure would normally work, because i have already tried it but with IDs (int) instead of dates (string). The error that i'm receiving when trying to generate the package is the following: </p>
  
  <pre><code>Could not execute Query on Connection AS400
  OleDbCommand.Prepare method requires all variable length parameters to have an explicitly set non-zero Size.
  </code></pre>
  
  <p>I know that i could create a ssis variable and concatenate the dates in the query as an expression, but i would like to stick with the <code>&lt;DirectInput&gt;</code> instead of the <code>&lt;VariableInput&gt;</code></p>
  
  <p>Additional information</p>
  
  <p>The datetime (sorry I forgot to mention it was datetime, but i don't think it changes much) format in the variable is correct: yyyy-mm-dd hh:mm:ss. I'm pretty sure that there's nothing wrong with the format because i tried to create a variable as an expression, concatenating the dates like this: <code>&lt;Variable Name="Query" DataType="String" EvaluateAsExpression="true"&gt;&amp;quot;SELECT * FROM table WHERE col &amp;gt; '&amp;quot; + @[User::StartDate] + &amp;quot;'&amp;quot;&lt;/Variable&gt;</code> and it works. One thing i forgot to mention and it might be important is that the source dbms is a db2</p>