BIML Parameterized query on Ole db source

Question

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:

<Dataflow Name="DFT Insert into <#=TableName#>">
    <Transformations>
        <OleDbSource Name="Retreive from Source (<#=TableName#>)" ConnectionName="AS400">
            <DirectInput>
                SELECT s.* 
                FROM <#=TableSchema#>.<#=TableName#> s
                WHERE s.date &gt; ? AND s.date &lt;= ?
            </DirectInput>
            <Parameters>
                <Parameter Name="0" VariableName="User.StartDate"/>
                <Parameter Name="1" VariableName="User.MiddleDate"/>
            </Parameters>
        </OleDbSource>

        <OleDbDestination Name="Insert into Destination (<#=TableName#>)" ConnectionName="DB2Mirror" KeepNulls="true" CheckConstraints="false">
            <ExternalTableOutput Table="[<#=TableSchema#>].[<#=TableName#>]" />
        </OleDbDestination>   
    </Transformations>
</Dataflow>

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:

Could not execute Query on Connection AS400
OleDbCommand.Prepare method requires all variable length parameters to have an explicitly set non-zero Size.

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 <DirectInput> instead of the <VariableInput>

Additional information

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: <Variable Name="Query" DataType="String" EvaluateAsExpression="true">&quot;SELECT * FROM table WHERE col &gt; '&quot; + @[User::StartDate] + &quot;'&quot;</Variable> and it works. One thing i forgot to mention and it might be important is that the source dbms is a db2

Answer 1

Given the following table definition

CREATE TABLE dbo.so_42623962
(
    RowSk int NOT NULL
,   [Date] date
);

This was my minimum, viable Biml that uses parameters with OLE DB Sources

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
    <OleDbConnection Name="tempdb" ConnectionString="Data Source=localhost\dev2016;Initial Catalog=tempdb;Provider=SQLNCLI11.0;Integrated Security=SSPI;"/>
</Connections>
<Packages>
    <Package Name="so_42623962" >
        <Variables>
            <Variable Name="StartDate" DataType="String">2017-01-01</Variable>
            <Variable Name="MiddleDate" DataType="String">2017-01-01</Variable>
        </Variables>
        <Tasks>
            <Dataflow Name="DFT Demo">
                <Transformations>
                    <OleDbSource Name="SRC Query" ConnectionName="tempdb">
                        <DirectInput><![CDATA[SELECT * 
                        FROM dbo.so_42623962 AS X 
                        WHERE X.[Date] > ? AND X.[Date] <= ? ;]]></DirectInput>
                        <Parameters>
                            <Parameter Name="0" VariableName="User.StartDate" />
                            <Parameter Name="1" VariableName="User.MiddleDate" />
                        </Parameters>
                    </OleDbSource>
                    <DerivedColumns Name="DER Placeholder" />
                </Transformations>
            </Dataflow>
        </Tasks>
    </Package>
</Packages>
</Biml>

That'll build a package just fine. If I take out the CDATA, which requires putting escaping the greater than/less than characters, it still works

        <Dataflow Name="DFT Escaped">
            <Transformations>
                <OleDbSource Name="SRC Query" ConnectionName="tempdb">
                    <DirectInput>SELECT * 
                    FROM dbo.so_42623962 AS X 
                    WHERE X.[Date] &gt; ? AND X.[Date] &lt;= ?;</DirectInput>
                    <Parameters>
                        <Parameter Name="0" VariableName="User.StartDate" />
                        <Parameter Name="1" VariableName="User.MiddleDate" />
                    </Parameters>
                </OleDbSource>
                <DerivedColumns Name="DER Placeholder" />
            </Transformations>
        </Dataflow>

If I don't wrap the Date column with braces, it still works

        <Dataflow Name="DFT Escapedx2">
            <Transformations>
                <OleDbSource Name="SRC Query" ConnectionName="tempdb">
                    <DirectInput>SELECT * 
                    FROM dbo.so_42623962 AS X 
                    WHERE X.Date &gt; ? AND X.Date &lt;= ?;</DirectInput>
                    <Parameters>
                        <Parameter Name="0" VariableName="User.StartDate" />
                        <Parameter Name="1" VariableName="User.MiddleDate" />
                    </Parameters>
                </OleDbSource>
                <DerivedColumns Name="DER Placeholder" />
            </Transformations>
        </Dataflow>

So what's left between my working Demo and yours? The easier thing to tackle is the value of your dates (Start/Middle Date). I used yyyy-mm-dd aka ccyy-mm-dd tends to be more universally understood by the systems I work with but you might try the ISO standard of yyyymmdd.

The harder piece to work with will be the fact that your connection manager was named AS400. In fact, that rings a bell because I had to deal with a "weird" date on another question they used Julian dates but I see AS400 uses a CYMD format which is similarly "odd" and you'd have to write your own expression with a Derived Column to convert yyyy-mm-dd date to a CYMD. Shouldn't be hard but I won't hazard a guess given the richness of possible date formats.