Pass a Dynamic SQL Query to an ADO.NET Source

The SSIS ADO.NET source does not expose a 'SqlCommandVariable' property, so it cannot read its SELECT statement directly from a package variable the way the OLE DB source can. This snippet works around that by giving the source a static 'DirectInput' query for design-time metadata, then attaching a Dataflow-level property expression that rewrites the source's 'SqlCommand' property at runtime from the 'User::extractSql' variable.

Two preceding Execute SQL tasks read 'LastLoadId' and 'LastLoadDate' from an 'ssis.ConfigVariable' table on the 'BIMP' connection and store them in package variables. The 'extractSql' variable is configured with 'EvaluateAsExpression="true"', so its value is composed from those load watermarks every time the package runs. Replace 'tableName' at the top of the script, point 'BIMP' at your configuration database, point 'MYSQL_SRC' at the source you want to query, and ensure the 'ssis.ConfigVariable' table exists with rows for 'LastLoadId' and 'LastLoadDate'.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <# 	var tableName = "my.table";
  var expressionSql = "SELECT * FROM " + tableName +" WHERE modified_date &gt; '\" + @[User::LastLoadDate] + \"' AND table_id &lt;= \" + @[User::LastLoadId] + \";";
  var sourceQuery = "SELECT * FROM " + tableName + ";";#>
    <Package Name="Extract <#=tableName.Replace(".", " ") #>" ConstraintMode="Linear">
      <Connections>
        <Connection ConnectionName="BIMP" />
        <Connection ConnectionName="MYSQL_SRC" />
      </Connections>
      <Variables>
        <Variable Name="extractSql" DataType="String" EvaluateAsExpression="true"><#=expressionSql#> </Variable>
        <Variable Name="LastLoadId" DataType="String" IncludeInDebugDump="Exclude">0</Variable>
        <Variable Name="LastLoadDate" DataType="String" IncludeInDebugDump="Exclude">1900-01-01 00:00:00</Variable>
      </Variables>
      <Tasks>
        <ExecuteSQL Name="SQL - Get Last Load Id" ForcedExecutionValueDataType="Empty" ConnectionName="BIMP" ResultSet="SingleRow">
          <Results>
            <Result Name="0" VariableName="User.LastLoadId" />
          </Results>
          <DirectInput>SELECT	[VariableValue]
    FROM	[ssis].[ConfigVariable]
    WHERE	[VariableName] = 'LastLoadId'</DirectInput>
          </ExecuteSQL>
          <ExecuteSQL Name="SQL - Get Last Load Date" ForcedExecutionValueDataType="Empty" ConnectionName="BIMP" ResultSet="SingleRow">
            <Results>
              <Result Name="0" VariableName="User.LastLoadDate" />
            </Results>
            <DirectInput>SELECT	[VariableValue]
    FROM	[ssis].[ConfigVariable]
    WHERE	[VariableName] = 'LastLoadDate'</DirectInput>
            </ExecuteSQL>
            <Dataflow Name="Load Table <#=tableName.Replace(".", " ") #>">
              <Expressions>
                <Expression ExternalProperty="[ADO_SRC <#=tableName.Replace(".", " ") #>].[SqlCommand]">@[User::extractSql]</Expression>
              </Expressions>
              <Transformations>
                <AdoNetSource Name="ADO_SRC <#=tableName.Replace(".", " ") #> " ConnectionName="MYSQL_SRC" ValidateExternalMetadata="false">
                  <DirectInput>
                    <#=sourceQuery #>
                  </DirectInput>
                </AdoNetSource>
              </Transformations>
            </Dataflow>
          </Tasks>
        </Package>
      </Packages>
    </Biml>