Clever Geek Handbook

Dynamically assign a value to a variable in SSIS

I have an SSIS package where I need to get the last run date of the package from an ADO NET source and then assign it to a variable so that I can use it in a request for another ADO NET source. I cannot find an example in Googles that actually works. I am running VS 2012 and connecting to an instance of SQL Server 2012. If there is more information, let me know.

+3


source to share


2 answers


Working with parameterized queries in an ADO.NET data source in SSIS is not as easy as OLE DB. Basically, you will have to write a query with an expression language and pray that your source will not succumb to SQL injection.

I have created a package with three variables as shown below

Package

enter image description here

Variables

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

enter image description here

Executing SQL Task

I am creating an Execute sql task that uses a query and is configured to return a single row. I am assigning this value to my SSIS variable.

enter image description here



In the Results tab, I assign column "0" to my LastRunDate variable

enter image description here

Data stream

Note that there is an expression here. In the ADO.NET source, I originally used SELECT RD.* FROM dbo.RunData AS RD

to get a set of metadata.

enter image description here

After I was happy with the data flow, I went to the control flow and replaced the Query variable as an expression in the ADO.NET source component (see the links to the questions).

Try, try, you will see

I used the following script to create 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 build this package as described. For those playing at home, you need to edit the third line so that the ADO.NET connection manager points 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>
+2


source


  • Create a variable @User::LastRanDate

    .
  • Create an Execute SQL task.
  • Set the property ConnectionType

    for ADO.NET.
  • Set the property Connection

    for your ADO.NET connection.
  • Set the property SQLStatement

    to a statement that will return the date you want. Make sure the first column returned is date.
  • Set the property ResultSet

    for one line.
  • On the Select Result tab of the Task Editor, click Add and set the parameter Result Name

    to 0 and the value Variable Name

    for @User :: LastRanDate. (ADO.NET result set is returned as indexed arrays.)


When the task completes, @User :: LastRanDate will now be set to what the request returned, and you can use it to create your request for another ADO.NET source.

+4


source






More articles:


All Articles