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.

:SP.GET_NAME_FROM_ID(inID, PROC_RESULT)

 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.

:SP.GET_NAME_FROM_ID(inID, varOUTPUT1,PROC_RESULT)

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

check_disk_space(oracle_db)

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.

SQL Server中存储过程Stored Procedure创建及C#调用

存储过程就是已经编译好的、优化过的放在数据库服务器中的一些SQL语句;可供应用程序直接调用。使用存储过程有以下几个优点: 1、执行速度比普通的SQL语句快 2、便于集中控制 3、可以降低网络的通...
  • tanzhangwen
  • tanzhangwen
  • 2015年02月25日 15:36
  • 3318

SQL Server追踪StoredProcedure(存储过程)执行语句

VS上报出SqlException在系统上做一操作,需要在数据库上操作,但报出SqlException。如下图在下方的自动窗口中,可以找到当前执行的存储过程名称,以及执行这个存储过程所使用的参数的值。...
  • youngsend
  • youngsend
  • 2016年04月20日 11:25
  • 1634

SQL Server 存储过程

本章内容简介: • 存储过程的定义以及何时需要使用一个存储过程 • 如何创建、修改和删除存储过程 • 传递输入和输出参数的方式 • 错误处理 • 性能考虑事项 • 如何使用调试器   ...
  • ChristopherChen
  • ChristopherChen
  • 2016年07月21日 23:18
  • 1972

A tool to generate class files to implement stored procedure

  • 2006年02月23日 09:05
  • 354KB
  • 下载

mysql存储过程编程 MySQL.Stored.Procedure.Programming

  • 2010年03月11日 14:44
  • 4.09MB
  • 下载

sybase stored procedure

  • 2012年08月13日 21:51
  • 7MB
  • 下载

MySQL利用Stored Procedure存储过程准备数据

会使用到的工具:sqlyog,或其他第三方工具如navicat step1    右键数据库下的“Stored Process”,选择“create Stored Procedure...”,在跳...
  • sylvia2016
  • sylvia2016
  • 2018年01月01日 14:16
  • 110

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

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

Use Stored Procedure in JPA

  • wuminlang
  • wuminlang
  • 2013年05月22日 17:28
  • 244

Stored Procedure/存储过程

存储过程就是已经编译好的、优化过的放在数据库服务器中的一些SQL语句;可供应用程序直接调用。使用存储过程有以下几个优点: 1、执行速度比普通的SQL语句快       再运行存储过程前,数据库...
  • blizmax6
  • blizmax6
  • 2012年10月09日 20:34
  • 730
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Stored Procedure Transformation
举报原因:
原因补充:

(最多只允许输入30个字)