在 SSIS 中为变量动态赋值 [英] Dynamically assign value to variable in SSIS

问题描述

我有一个 SSIS 包，我需要在其中获取包上次从 ADO NET 源运行的日期，然后将其分配给一个变量，以便我可以在另一个 ADO NET 源的查询中使用它.我在谷歌上找不到实际工作的例子.我正在运行 VS 2012 并连接到 SQL Server 2012 实例.如果需要更多信息，请告诉我.

解决方案

在 SSIS 的 ADO.NET 数据源中使用参数化查询并不像 OLE DB 那样容易.基本上，您将不得不使用表达式语言编写查询，并祈祷您的源代码不会被 sql 注入.

• ，所以我使用以下 Biml 来生成这个包.对于那些在家玩的人，您需要编辑第三行，以便 ADO.NET 连接管理器指向有效的服务器和数据库.

  <连接><AdoNetConnection Name="CM_ADO_DB" ConnectionString="Data Source=localhost\dev2014;Integrated Security=SSPI;Connect Timeout=30;Database=tempdb;"提供者=SQL"/></连接><包><Package Name="so_25125838" ConstraintMode="Linear"><变量><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] + "';"</变量><任务><执行SQL名称="SQL GetLastRunDate"连接名称="CM_ADO_DB"结果集=单行"><DirectInput>SELECT MAX(RJ.LastRunDate) AS LastRunDate FROM dbo.RussJohnson AS RJ;</DirectInput><结果><Result Name="0" VariableName="User.LastRunDate"/></结果></执行SQL><数据流名称="DFT POC"><转换><AdoNetSource Name="ADO_SRC 获取新数据" ConnectionName="CM_ADO_DB"><DirectInput>SELECT RD.* FROM dbo.RunData AS RD</DirectInput></AdoNetSource><RowCount Name="CNT 原始行数" VariableName="User.RowCountOriginal"/></转换><表达式><Expression ExternalProperty="[ADO_SRC 获取新数据].[SqlCommand]">@[User::QueryAdo]</Expression></表达式></数据流></任务></包></包></Biml>

  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.

  解决方案

  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>
  

  这篇关于在 SSIS 中为变量动态赋值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！