Dynamically assign value to variable in SSIS

Question

I have an SSIS package where I need to get the date the package last ran from an ADO NET Source then assign it to a variable so what I can use it in a query for another ADO NET Source. I can't find an example on the Googles that actually works. I'm running VS 2012 and connecting to a SQL Server 2012 instance. If there is more information needed let me know.

Answer 1

1. Create a variable @User::LastRanDate.
2. Create an Execute SQL task.
3. Set the ConnectionType property to ADO.NET.
4. Set the Connection property to your ADO.NET connection.
5. Set the SQLStatement property to the statement which will return the date you want. Make sure the first column returned is the date.
6. Set the ResultSet property to Single row.
7. On the Result Set tab of the Task editor, hit Add and set the Result Name value to 0 and the Variable Name value to @User::LastRanDate. (ADO.NET result sets are returned as indexed arrays.)

Upon completion of the Task, @User::LastRanDate will now be set to whatever the query returned and you can use it to build up your query for your other ADO.NET source.

Answer 2

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>