SSIS高级转换任务—执行SQL语句

下面的随笔中将讲述SSIS中的高级转换任务,和老旧的SQL Server 2000 DTS相比,我们会发现现在以前的dark-arrow,data-pump任务没有了。在转换任务中隐藏ActiveX脚本和嵌入连接字符的方法也被去除了。在将Package指向不同的数据库的时候也不会忘记修改转换对象,在修改对象连接的时候也一样。现在新建全局连接。转换任务可以被更加容易的管理,使用便捷界面可以浏览任务的设计界面并修改属性。

在使用SSIS package的时候,有些时候需要避免一些过度依赖的情况。没有必要在package中使用ActiveX脚本任务暴力的解决一些很复杂问题。事实上如果一开始就过度依赖脚本任务,就需要停下来想一想,有没有可以替代的任务。SSIS中有许多任务可以解决譬如去掉平面文件中的无用数据,分隔数据输入内容,循环执行几个任务。我们还是可能遇到SSIS不能解决的问题,如果遇到了就需要使用灵活的.net脚本任务来解决。

这里我们将使用这些新的工具来解决生产环境的遇到的问题,我们需要重新来设置存储过程的输出参数吗?根据行数来有条件地设置分支?使用模糊逻辑任务来代替在表中查找数据如何?我们将看到更加复杂的转换任务。假定大家已经熟悉了BIDS的常见环境设置,所以在这下面的随笔中我们将不再详细解释每一个例子的具体操作步骤,而只说明重要的属性设置。

  

执行SQL语句任务

执行SQL任务算不上是一种高级的SSIS任务,但是在SQL Server 2000中它是一种最常用的任务。可以把它作为一个出发点来学习变量,表达式和其他SSIS中的高级属性设置。

在做一些测试的时候经常使用执行SQL任务常用来删除存储表中的数据。另一种用来调用存储过程来完成DTS中不能完成的任务。SSIS还提供一种的工具,现在使用配置管理可以编辑存储过程的需要使用的参数数据,或者保存输出参数数据。

新建一个Package,在Control Flow中拖放一个执行SQL任务。这个任务会显示一个红色的标志,一个错误信息显示提示这个任务没有连接到一个数据连接上。为了解决这个错误,双击打开编辑界面如图6-1。

图6-1

这个任务有四个标签界面:

  • General:设置任务的名字,描述,和数据连接相关的设置,和如何执行SQL语句相关的设置,以及要执行的SQL语句
  • Parameter Mapping:包含Package级或容器级的变量集合。变量为SQL语句或者存储过程提供变量输入值
  • Result Set:包含执行SQL语句或存储过程之后得到的数据集
  • Expressions:包含设置这个任务的属性的表达式,这个是需要动态设置属性的

这个任务中的主要的属性设置如下:

  • ConnectionType,Connection:这些属性用来设置数据连接,如类型,名字。
  • SQL Statement:为task提供要执行的SQL语句。这里的SQL语句可以是一个简单的SELECT语句,一个复杂的带GO语句的,或者调用一个存储过程
  • SQL Source Type:这个属性是新添加的一个属性。它提供属性配置SQLStatement放置在一个变量,文件,或者直接输入SQL语句
  • ResultSet property:这个属性可以设置为执行SQL语句之后得到的一个单一结果,多行多列的结果,或者一个XML数据。如果在General标签内将ResultSet设置为NONE,在Result Set标签界面内的表格将不可用

在使用这个task之前需要理解SSIS如何处理变量,还有一个很重要的属性expressions。

  

变量

在SSIS中变量时有范围的,Package范围内的只能在整个Package内使用,就相当于全局变量。变量可以由不同的分离的范围如图6-2.默认的名称空间是User。变量名是区分大小写的,这些细节会导致错误的Package逻辑。图6-2显示在同一个Package中存在的两个同名的但是作用范围不同的两个变量sSQL。在SSIS中可以方便地使用变量,首先,配置工具允许使用XML文件,环境变量,甚至注册设置来配置Package。当Package设置是静态的,在运行之前配置的,这些技术对于多环境开发更加容易管理。当从输入流中获得设置信息,或者需要在运行时修改属性,这种技术就不太容易了。三种使用非静态配置的例子是调用带参数的Package按照命名规范规范修改输出文件名在运行时修改连接属性

 

图6-2

变量可以存储一个任务中的值并传递到另外一个任务中。变量可以通过SSIS中的属性表达式设置IS(Integrated Service)组件的属性。可以使用变量设置任务中的信息:标记,计数器,或者控制Package的字符串。由于变量的灵活性,可以使用更多的变量设置。

  

表达式

大多数的任务,转换,容器都有一个属性是表达式,Package本身也有这种属性。表达式集合使用逻辑表达式来显示容器等的属性。执行SQL语句任务中可以使用表达式设置的属性是SQL StatementSource,就是将要执行的SQL语句或存储过程的源。另外一个属性ConnectionString为OLE DB连接设置连接字符串。在任务中右击选择属性,在表达式这一栏点击表达式傍边的按钮打开编辑表达式对话框。

如图6-3显示的是执行SQL任务的属性编辑对话框,点击Property下拉列表框显示的是属性表达式。

图6-3

点击表达式栏傍边的按钮创建一个表达式。如图6-4,使用这个工具创建一个逻辑表达式,可以使用的选择项有字面值,系统,用户自定义变量,操作符,内建函数。表达式语言中的函数和操作符类似于C#,C和TSQL语言,但有区别。使用VB语言时==代表着判断相等,&&代表着逻辑和,TSQL语言中使用双引号包含字符串而不是单引号。表达式语言不是大小写敏感的,所以C#和C程序员可以不必在意变量名的大小写问题。花一点时间很快就会熟悉表达式语言。

图6-4

现在我们继续讨论如何在执行SQL任务中使用两种不同范围的变量。图6-4显示SQLStatementSource属性的表达式属性,展开的变量节点显示所有的变量值。点击变量User::sSQL并拖放到Expression文本框内,这样就创建了一个表达式。在运行时表达式的值将会替代SQLStatementSource属性的值。点击下方的Evaluate Expression按钮查看表达式的值。可以看到表达式的值是SELECT 2。在这例子中,两个变量的名字相同,范围不同。在执行任务时,属性的值将会是SELECT 2。

  

使用SQL 输出参数动态改变package设置

在这个例子中我们将导出AdventureWorks数据库中的[HumanResources].[Shift]中的数据到一个txt文件中,并在运行时修改属性的值。在执行SQL任务中使用输出表达式来修改导出路径。

假设客户要求将一个Package和一个包含系统配置信息的数据库整合起来,在这个数据库中分别存放着开发,测试和产品环境。在载入数据的时候,所有的设置需要在运行时使用存储过程从数据库中抽取。从开发环境迁移到测试环境的时候通过修改数据库中存放的配置信息实现修改迁移目的的功能。

因为需要在运行时获得属性设置,需要使用存储过程来重新获得设置,设置信息在Package中是不能获得的。这里使用表达式来解决这个问题。

  1. 新建Package,在Control flow界面类拖放一个执行SQL语句任务
  2. 在Control Flow设计界面右击添加一个变量,为变量命名为MyFile,值为c:\Execute SQL Sample.txt   注意这里不要使用引号将这个值包含起来。
  3. 新建一个存储过程,为简单起见,将存储过程放在同一个数据库中。这个存储过程用来模拟在AdventureWorks数据库中运行下面的语句,注意这个存储过程会修改传入的参数

    USE adventureworks
    go
    CREATE PROC dbo.usp_GetConfigParamValue (
    @ApplicationName Varchar(30), -- the name of the application.

    @ParameterName Varchar(50), -- the name of the parameter
    @ParameterTypeName Varchar(30), -- the name of the parameter type
    @ParameterValueVar Varchar(255) OUTPUT -- output buffer for value
    )

    AS
    Set NOCOUNT ON

    --Dummy proc to simulate the real usp_GetConfigParamValue
    --Always outputs ‘c:\ Execute SQL Sample Changed.txt‘
    SET @PARAMETERVALUEVAR=‘c:\Execute SQL Sample Changed.txt‘

    Set NOCOUNT OFF

  4. 设置执行SQL任务的连接类型为ADO.NET,首先新建一个ADO.NET连接,在server name栏中输入”.”表示本地服务器,在下拉列表框中选择AdventureWorks数据库,保持默认访问机制NT Authentication。注意:在这个例子中使用OLE DB连接将不再适用,不同的连接中处理存储过程参数的方法不同
  5. 设置Execute SQL SQLStatemnet属性为以下的代码:EXEC usp_GetConfigParamValue ‘MYAPP‘, ‘MYPARM‘, ‘STRING‘, @MYVALUE OUTPUT
  6. 在参数映射标标签界面添加一个映射MyFile,设置variable name为User::MyFile,direction为Output,数据类型为String,Parameter Name为MYVALUE,点击OK保存设置。这里要注意这个变量名字一定要和上面的SQL语句中的变量@MYVALUE一致,可以不加@
  7. 从工具栏中拖放一个Data Flow任务,然后将Execute SQL和Data Flow连接起来
  8. 在Data Flow 设计界面拖放一个ADO NET Source和一个Flat File Destination。注意:检查你拖放的是一个Flat File Destination而不是一个Flat File Source,这两个容易混淆
  9. 配置OLE DB Source选择AdventureWorks连接设置SQLCommand属性为:Select * from [HumanResources].[Shift]
  10. 把OLE DB Source和AdventureWorks连接起来
  11. 双击Flat File Destination打开编辑界面,新建一个delimited文件,暂时地设置文件名为c:\myfile.txt,点击Mapping标签查看列名,保持默认设置点击OK退出编辑界面
  12. 这里要演示的是在运行时修改task的属性,现在这个Package能完成的任务是将数据库中[HumanResources].[Shift]表中的数据导入到c:\Execute SQL Sample.txt 。现在想要在运行时修改文件名字,需要一个表达式修改连Flat File Connection的连接字符串,我们使用表达式来达到这个目的
  13. 右击Connection Managers中的Flat File Connection Manager选择属性,在属性栏中点击Expressions傍边的按钮,要修改的属性是ConnectionString,在Expression Builder界面内选择变量@[User::MyFile]。在运行时表达式将会被赋值为变量MyFile中的值

运行这个Package检查路径C:\SSISDemos下查看文件Execute SQL Sample Changed.txt,我们本来是要将数据导入到Execute SQL Sample.txt中的,现在修改了路径,数据将传到新的文件Execute SQL Sample Changed.txt中。这样就实现了动态修改Flat File Connection的属性值的目的,这样可以不用手动修改package的配置,直接修改存储过程中的这一句:SET @PARAMETERVALUEVAR=‘c:\Execute SQL Sample Changed.txt‘ ,这样每次都可以重新设置数据的存放地址。

执行完成之后的效果如图6-5

这个例子的应用情景是首选将数据输出到一个开发环境,然后再使用表达式将属性设置为输出到一个生产环境。这样做的好处是一旦设置被一直到一个不同的环境,不必修改所有的SSIS嵌入环境设置就可以修改Package的输出路径。这个例子也使用了SSIS表达式,使用ADO.NET演示了输出参数,有些地方需要注意:

  • 如果数据提供者没有识别出输出参数的名字和次序,一个变通的方法是是存储过程返回一个结果集并映射到变量中。如果不能修改存储过程,也可以执行简单的SQL语句查询结果并返回。下面是SQLStatement设置的语句:
    DECLARE @MYVALUE AS VARCHAR(255)
    EXEC usp_GetConfigParamValue ‘MYAPP‘, ‘MYPARM‘, ‘STRING‘, @MYVALUE OUTPUT
    SELECT @MYVALUE AS MyValue
  • 这里IsQueryStoredProduced属性默认设置为false,看上去应该设置为true。但是设置为true会产生错误,显示找不到储存过程。

原文地址:https://www.cnblogs.com/purple5252/p/10337810.html

时间: 01-30

SSIS高级转换任务—执行SQL语句的相关文章

EF中执行sql语句

EF原理 EF 会自动把 Where().OrderBy().Select()等这些编译成"表达式树(Expression Tree)",然后会把表达式树翻译成 SQL 语句去执行.(编译原理,AST)因此不是"把数据都取到内存中,然后使用集合的方法进行数据过滤",因此性能不会低.但是如果这个操作不能被翻译成 SQL 语句,则或者报错,或者被放到内存中操作,性能就会非常低 跟踪EF的查询Sql语句: DbContext 有一个 Database 属性,其中的 Log

JDBC系列:(3)使用PreparedStatement执行sql语句

执行sql语句的接口 接口 作用 Statement接口 用于执行静态的sql语句 PreparedStatement接口 用于执行预编译sql语句 CallableStatement接口 用于执行存储过程的sql语句(call xxx) PreparedStatement Vs Statement 序号 不同 描述 1 语法不同 PreparedStatement可以使用预编译的sql,而Statment只能使用静态的sql 2 效率不同 PreparedStatement可以使用sql缓存区

循环执行sql语句

DECLARE--声明变量SQL_ALLTABLES LONG; SQL_INSERT LONG; TYPE THE_CURSOR_TYPE IS REF CURSOR; --定义引用游标的数据类型CURSOR_D THE_CURSOR_TYPE; --定义游标 DATAUP VARCHAR2(200);BEGIN--井筒文档SQL_ALLTABLES := 'SELECT DISTINCT (TABLE_NAME) FROM USER_TAB_COLUMNS WHERE COLUMN_NAME

EntityFramework执行SQL语句

在EF中执行Sql语句. using (var context = new EFRecipesEntities()) { string sql = @"insert into Chapter3.Payment(Amount, Vendor) values (@Amount, @Vendor)"; var args = new DbParameter[] { new SqlParameter { ParameterName = "Amount", Value = 99

linux程序设计——执行SQL语句(第八章)

8.3    使用C语言访问MySQL数据 8.3.3 执行SQL语句 执行SQL语句的主要API函数被恰当的命名为: int mysql_query(MYSQL *connection, const char *query); 这个例程接受连接结构指针和文本字符串形式的有效SQL语句,如果成功,它返回0. 1.不返回数据的SQL语句 为简单起见,先看一些不返回任何数据的SQL语句:UPDATE,DELETE和INSERT. 下面的函数用于检查受查询影响的行数: my_ulonglong mys

怎样在dos里进入mysql,执行sql语句

1.进入mysql bin目录下,执行mysql.exe 2.用mysql -uroot -p登陆mysql之后就可以使用 怎样在dos里进入mysql,执行sql语句,布布扣,bubuko.com

让你提前认识软件开发(20):如何在C语言里面执行SQL语句?

[文章摘要] 在通信类软件中,程序经常需要与数据库打交道.为了实现诸如从数据库中获取数据.更新数据库表某字段.插入或删除某条数据等功能,就需要在C语言程序中构造一些SQL语句,并用函数来执行这些SQL语句. 本文介绍如何在C语言程序中构造并执行SQL语句,为相关软件开发工作的开展提供了参考. [关键词] SQL语句  C语言  程序  流程  开发 一.为什么要在C语言程序中执行SQL语句? 在C语言程序中执行SQL语句的原因有以下几个: (1) 程序需要获取数据库中某数据表的字段值,并对这些字

.net core下直接执行SQL语句并生成DataTable

.net core可以执行SQL语句,但是只能生成强类型的返回结果.例如var blogs = context.Blogs.FromSql("SELECT * FROM dbo.Blogs").ToList().而不允许返回DataSet.DataTable等弱类型.可能由于这个原因没有实现在.net core中DataTable,然而DataTable还是可能会用到的.我们这里就有一个数据仓库的需求,允许用户自行编写类似SQL语句,然后执行,以表格展示.因为语句是千变万化的,因此我也

JDBC系列:(2)使用Statement执行sql语句

执行sql语句的接口 接口 作用 Statement接口 用于执行静态的sql语句 PreparedStatement接口 用于执行预编译sql语句 CallableStatement接口 用于执行存储过程的sql语句(call xxx) 1.执行DDL语句 package com.rk.db.b_statement; import java.sql.DriverManager; import java.sql.Connection; import java.sql.SQLException;

在EF中执行SQL语句

你可能要问,我用EF不就为了避免写SQL吗?如果要写SQL我不如直接用ADO.NET得了.话虽然这么说没错,可有些时候使用EF操作数据还是有一些不方便,例如让你根据条件删除一组记录,如果按照正常的流程来走,你就得先把这些数据查出来,然后再一条一条地删除它们,这样不仅麻烦而且性能也比较低.这种情况下SQL就显示出它的威力了. 而使用EF执行SQL又比ADO.NET方便,特别是在执行查询语句的时候,EF会把查询到的数据自动保存到数据实体中,省去了使用DataReader的麻烦.同时查询出来的数据还会