Dynamically assign value to variable in SSIS

Working with parameterized queries in an ADO.NET Data Source in SSIS is not as easy as an OLE DB one. Basically, you're going to have to write the query with the expression language and pray your source doesn't lend itself to sql injection.

• How to Pass parameter in ADO.NET Source SSIS
• how to pass parameters to an ado.net source in ssis?

I created a package with 3 variables as shown below

Package

Variables

I have LastRunDate as a DateTime and a QueryAdo as a string. This evaluated as an Expression with the expression being "SELECT RD.* FROM dbo.RunData AS RD WHERE RD.InsertDate > '" + (DT_WSTR, 25) @[User::LastRunDate] + "';"

Execute SQL Task

I create an Execute sql task that uses a query and is set to return a single row. I assign this value into my SSIS Variable.

In my results tab, I assign the zeroeth column to my variable LastRunDate

Data flow

Note there is an expression here. On the ADO.NET source, I originally used SELECT RD.* FROM dbo.RunData AS RD to get my meta data set.

After I was happy with my data flow, I then went to the control flow and substituted my Query variable in as the expression on the ADO.NET Source component (see the referenced questions).

Try it, try it, you will see

I used the following script to build out my demo environment

create table dbo.RussJohnson
(
    LastRunDate datetime NOT NULL
);


create table dbo.RunData
(
    SomeValue int NOT NULL
,   InsertDate datetime NOT NULL
);


insert into dbo.RussJohnson
SELECT '2014-08-01' AS LastRunDate

INSERT INTO
    dbo.RunData
(
    SomeValue
,   InsertDate
)
SELECT
    D.rc AS Somevalue
,   dateadd(d, D.rc, '2014-07-30') AS InsertDate
FROM
(
    SELECT TOP 15 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rc
    FROM sys.all_columns AS SC
) D;

Since I have BIDS Helper installed, I used the following Biml to generate this package as described. For those playing along at home, you will need to edit the third line so that the ADO.NET connection manager is pointing to a valid server and database.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AdoNetConnection Name="CM_ADO_DB" ConnectionString="Data Source=localhost\dev2014;Integrated Security=SSPI;Connect Timeout=30;Database=tempdb;" Provider="SQL"  />
    </Connections>
    <Packages>
        <Package Name="so_25125838" ConstraintMode="Linear">
            <Variables>
                <Variable DataType="DateTime" Name="LastRunDate" >2014-01-01</Variable>
                <Variable DataType="Int32" Name="RowCountOriginal" >0</Variable>
                <Variable DataType="String" Name="QueryAdo" EvaluateAsExpression="true">"SELECT RD.* FROM dbo.RunData AS RD WHERE RD.InsertDate > '" + (DT_WSTR, 25) @[User::LastRunDate] + "';"</Variable>
            </Variables>
            <Tasks>
                <ExecuteSQL 
                    Name="SQL GetLastRunDate" 
                    ConnectionName="CM_ADO_DB"
                    ResultSet="SingleRow"
                    >
                    <DirectInput>SELECT MAX(RJ.LastRunDate) AS LastRunDate FROM dbo.RussJohnson AS RJ;</DirectInput>
                    <Results>
                        <Result Name="0" VariableName="User.LastRunDate" />
                    </Results>
                </ExecuteSQL>
                <Dataflow Name="DFT POC">
                    <Transformations>
                        <AdoNetSource Name="ADO_SRC Get New Data" ConnectionName="CM_ADO_DB">
                            <DirectInput>SELECT RD.* FROM dbo.RunData AS RD</DirectInput>
                        </AdoNetSource>
                        <RowCount Name="CNT Original rows" VariableName="User.RowCountOriginal" />
                    </Transformations>
                    <Expressions>
                        <Expression ExternalProperty="[ADO_SRC Get New Data].[SqlCommand]">@[User::QueryAdo]</Expression>
                    </Expressions>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>