Stored Procedure Transformation

原创 2015年07月09日 12:33:43

To use a Stored Procedure transformation, complete the following steps:
1. Create the stored procedure in the database.
2. Import or create the Stored Procedure transformation.
3. Determine whether to use the transformation as connected or unconnected.
4. If connected, map the appropriate input and output ports.
5. If unconnected, either configure the stored procedure to run pre- or post-session, or configure it to run from an expression in another transformation.

6. Configure the session.
The session properties in the Workflow Manager includes options for error handling when running stored procedures and several SQL override options.

If a database errors during a post-session stored procedureand  you configure the session to stop upon stored procedure error, the Integration Service fails the session.However, the Integration Service has already committed all data to session targets.


Specifying when the Stored Procedure Runs

¨ Normal  Connected stored procedures run only in normal mode.
¨ Pre-load of the Source. Before the session retrieves data from the source, the stored procedure runs. This is
useful for verifying the existence of tables or performing joins of data in a temporary table.
¨ Post-load of the Source. After the session retrieves data from the source, the stored procedure runs. This is
useful for removing temporary tables.
¨ Pre-load of the Target. Before the session sends data to the target, the stored procedure runs. This is useful
for verifying target tables or disk space on the target system.
¨ Post-load of the Target. After the session sends data to the target, the stored procedure runs. This is useful
for re-creating indexes on the database.


If the mapping calls more than one source or target pre- or post-load stored procedure in a mapping, the
Integration Service executes the stored procedures in the execution order that you specify in the mapping.


Configuring an Unconnected Transformation

¨ From an expression. Called from an expression written in the Expression Editor within another transformation
in the mapping.
¨ Pre- or post-session. Runs before or after a session.


When using an unconnected Stored Procedure transformation in an expression, you need a method of returning
the value of output parameters to a port. Use one of the following methods to capture the output values:
¨ Assign the output value to a local variable.
¨ Assign the output value to the system variable PROC_RESULT.
By using PROC_RESULT, you assign the value of the return parameter directly to an output port, which can apply
directly to a target. You can also combine the two options by assigning one output parameter as PROC_RESULT,
and the other parameter as a variable.


 inID can be either an input port for the transformation or a variable in the transformation. The value of PROC_RESULT is applied to the output port for the expression.


The value of the second output port is applied to the output port for the expression, and the value of the first output port is applied to local variable varOUTPUT1 .

The output parameters are returned in the order they are declared inthe stored procedure. With all these expressions, the datatypes for the ports and variables must match the datatypes for the input/output variables and return value.


Calling a Pre- or Post-Session Stored Procedure

Double-click the Stored Procedure transformation, and select the Properties tab.

Enter the call text of the stored procedure,You do not need to include the SQL statement EXEC


SP(“12/31/2000 11:45:59”)

You can use PowerCenter parameters and variables in the call text. Use any parameter or variable type that
you can define in the parameter file.

Unconnected Stored Procedure transformation

使用存储过程一般目的是: (1). 加载数据之前检查目标数据库的状态 (2). 确定数据库是否有足够的空间 (3). 完成特定计算 (4). 删除或重建索引 Unconnected ...
  • Iamhonest
  • Iamhonest
  • 2013年01月09日 12:58
  • 386

SQL存储过程(Stored Procedure)

SQL存储过程(Stored Procedure) 什么是存储过程? 存储过程(procedure)类似于C语言中的函数用来执行管理任务或应用复杂的业务规则存储过程可以带参数,也可以返回结...
  • lhy2199
  • lhy2199
  • 2015年12月28日 10:37
  • 6155

Stored Procedure存储过程基础知识详解

存储过程是做项目的必备技术,只要你面试找工作,数据库及存储过程也是必考的,下面一起来了解存储过程的简单基本技术知识: 一、存储过程基本概念? 存储过程就是作为可执行对象存放在数据库中的一个或多个S...
  • u013519551
  • u013519551
  • 2017年05月19日 15:04
  • 770

数据库之存储过程(stored procedure)简述

存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。...
  • xunileida
  • xunileida
  • 2012年04月22日 12:00
  • 1364

Could not find stored procedure 'sp_sdidebug'

 在VS2003开发中,出现Could not find stored procedure sp_sdidebug错误,把调试时以上图中红色标示的选项勾除即可;在VS2005中支持此功能。...
  • guoqiang1983
  • guoqiang1983
  • 2008年07月30日 21:10
  • 2271

MySQL Stored Procedure Parameters

Summary: in this tutorial, we will show you how to write MySQL stored procedures with parameters. ...
  • kezhen
  • kezhen
  • 2014年04月13日 20:42
  • 1377

Spring StoredProcedure Cursor 使用

Java Code import java.sql.ResultSet; import java.sql.SQLException; import java.text.DateFormat; i...
  • feiyu8607
  • feiyu8607
  • 2013年11月07日 11:53
  • 1379

Authid Current_User--存储过程中也能使用role角色权限(转)

我们知道,用户拥有的role权限在存储过程是不可用的。遇到这种情况,我们一般需要显式进行系统权限,如grant create table to suk;但这种方法太麻烦,有时候可能需要进行非常多的授权...
  • wdnq1022
  • wdnq1022
  • 2014年04月16日 23:22
  • 335


  • wq7570875
  • wq7570875
  • 2014年05月22日 11:48
  • 1786

ADO.NET 如何取得 Stored Procedure 的回傳值 (如何获得Stored Procedure 的Last inset id?)

若使用 ADO.NET 連接資料庫並呼叫預儲程序(Stored Procedure)的話,基本上有三種方式可以取得執行後的結果,分別如下: 在預儲程序中最後一行直接用 SELECT 語法回傳表格...
  • kezhen
  • kezhen
  • 2014年04月16日 10:26
  • 1152
您举报文章:Stored Procedure Transformation