SSIS⾼级转换任务—执⾏SQL语句
下⾯的随笔中将讲述SSIS中的⾼级转换任务，和⽼旧的SQL Server 2000 DTS相⽐，我们会发现现在以前的dark-arrow，data-pump任务没有了。在转换任务中隐藏ActiveX脚本和嵌⼊连接字符的⽅法也被去除了。在将Package指向不同的数据库的时候也不会忘记修改转换对象，在修改对象连接的时候也⼀样。现在新建全局连接。转换任务可以被更加容易的管理，使⽤便捷界⾯可以浏览任务的设计界⾯并修改属性。
在使⽤SSIS package的时候，有些时候需要避免⼀些过度依赖的情况。没有必要在package中使⽤ActiveX脚本任务暴⼒的解决⼀些很复杂问题。事实上如果⼀开始就过度依赖脚本任务，就需要停下来想⼀想，有没有可以替代的任务。SSIS中有许多任务可以解决譬如去掉平⾯⽂件中的⽆⽤数据，分隔数据输⼊内容，循环执⾏⼏个任务。我们还是可能遇到SSIS不能解决的问题，如果遇到了就需要使⽤灵活的脚本任务来解决。
这⾥我们将使⽤这些新的⼯具来解决⽣产环境的遇到的问题，我们需要重新来设置存储过程的输出参数吗？根据⾏数来有条件地设置分⽀？使⽤模糊逻辑任务来代替在表中查数据如何？我们将看到更加复杂的转换任务。假定⼤家已经熟悉了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   注意这⾥不要使⽤引号将这个值
包含起来。
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 '
SET@PARAMETERVALUEVAR='c:\Execute SQL '
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:\，点击Mapping标签查看列名，保持
默认设置点击OK退出编辑界⾯
12. 这⾥要演⽰的是在运⾏时修改task的属性，现在这个Package能完成的任务是将数据库中[HumanResources].[Shift]表中的数据导⼊到
c:\Execute 。现在想要在运⾏时修改⽂件名字，需要⼀个表达式修改连Flat File Connection的连接字符串，我们使⽤表达式来达到这个⽬的sql容易学吗
13. 右击Connection Managers中的Flat File Connection Manager选择属性，在属性栏中点击Expressions傍边的按钮，要修改的属性是
ConnectionString，在Expression Builder界⾯内选择变量@[User::MyFile]。在运⾏时表达式将会被赋值为变量MyFile中的值
运⾏这个Package检查路径C:\SSISDemos下查看⽂件Execute SQL ，我们本来是要将数据导⼊到Execute 中的，现在修改了路径，数据将传到新的⽂件Execute SQL 中。这样就实现了动态修改Flat File Connection 的属性值的⽬的，这样可以不⽤⼿动修改package的配置，直接修改存储过程中的这⼀句：SET @PARAMETERVALUEVAR='c:\Execute SQL ' ，这样每次都可以重新设置数据的存放地址。
执⾏完成之后的效果如图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会产⽣错误，显⽰不到储存过程。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。