在脚本中检索源表和目标表名称 [英] Retrieving source and destination table names in script
问题描述
我创建了一个带有数据流的 SSIS 包.
I've created an SSIS package with a dataflow.
oledb 源码使用了一个带有 sql 语句的变量.
The oledb source uses a variable with a sql statement.
在oledb源之后，有一个派生的列转换来转换数据.如果中断，则重定向到日志表，如果没有中断，则转到 oledb 目标表.
Following the oledb source, there is a derived column transformation that converts the data. If that breaks, it is redirected to a log table, if it doesnt break it goes to the oledb destination table.
问题:有没有一种方法可以确定转换中断时源表和目标表是什么，以用于记录目的?
Question: Is there a way I can determine what the source and destination tables are when the conversion breaks, for logging purposes?
注意:源表在oledb源使用的变量中，目的在oledb目标任务中选择.
Note: The source table is in the variable used by oledb source, and the destination is selected in the oledb destination task.
感谢您的时间.
推荐答案
听起来您已经在使用数据访问模式来自变量的 SQL 命令".假设您的变量名为SourceQuery"，并且您在数据流任务之前在包中构造它.再添加 2 个包变量:
It sounds like you are already using data access mode "SQL command from variable". Let's say your variable is named "SourceQuery" and that you construct this in your package before the data flow task. Add 2 more package variables:
- 源表名称
- 目标表名称
按照您现在的方式构造SourceQuery"，但也要将表名存储在 SourceTableName 中.
Construct "SourceQuery" similar to the way you are doing now, but also store the table name in SourceTableName.
将目标任务的数据访问方式从表或视图"更改为表或视图名称变量"并选择变量名称DestinationTableName".这可以使用正确的表名作为其初始值，也可以在数据流任务之前设置.
Change the destination task's data access mode from "Table or view" to "Table or view name variable" and select variable name "DestinationTableName". This could either have the correct table name as its initial value, or be set before the data flow task.
变量 SourceTableName 和 DestinationTableName 现在将包含您想要的日志信息.
The variables SourceTableName and DestinationTableName will now contain the information you want for your logging.
这篇关于在脚本中检索源表和目标表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
