Script component 用法

在SSIS中,可以使用C#编写脚本,这是十分激动人心的事,能够使用C#代码,使得Script Component无所不能。

第一部分:组件简介
Script Component 有三种类型:Source, Destination and Transformation

1,每种类型的脚本,都有两种类型的参数:ReadOnly 和ReadWrite,在脚本中可以使用 this.Variables.VariableName 来获取或设置参数变量的值

示例:创建四个Variable,并传递给Script component

 

 

SSIS十分友好,在脚本中自动生成了一个子类,将Variable Name作为属性添加到子类中,引用Variable Name十分简单

public class ScriptMain : UserComponent

在脚本代码中,使用 this.Variables.VariableName 来获取或设置参数变量的值

 

2,可以为 Script component 指定connection ,如果在脚本中使用Ado.net,可以直接创建Ado.net connection manager,在脚本中,使用以下代码来引用connection

IDTSConnectionManager100 cnManager = this.Connections.Connection;

 

3,Script component 不仅有输入的Variable,而且还有output / input columns,设置output / input columns 以便输出或输入表数据

示例中增加两列Code和name,分别是string类型

 

第二部分:Source 组件示例

4,如果Script Component 作为Source,那么使用脚本获取数据之后,可以使用将数据逐行添加到Source 的输出buff中。

在将获得的数据集插入到output buff中时,SSIS使用的代码逻辑是:先向output buff中插入一行,然后为该行的字段赋值

    DataRow dr=dt.Rows[0];

    this.Output0Buffer.AddRow();
            this.Output0Buffer.Code = dr["code"].ToString();
            this.Output0Buffer.Name = dr["name"].ToString();

示例Code

复制代码
    DataTable dt;
    IDTSConnectionManager100 cnManager;
SqlConnection cnn; /// <summary> /// This method is called once, before rows begin to be processed in the data flow. /// /// You can remove this method if you don't need to do anything here. /// </summary> public override void PreExecute() { base.PreExecute(); cnManager = this.Connections.Connection; cnn = (SqlConnection)cnManager.AcquireConnection(null); SqlCommand cmd = cnn.CreateCommand(); cmd.CommandText = "select code,name from [dbo].[tbExcel]"; cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 60000; dt = new DataTable("dt"); SqlDataAdapter sda = new SqlDataAdapter(cmd); sda.Fill(dt); } /// <summary> /// This method is called after all the rows have passed through this component. /// /// You can delete this method if you don't need to do anything here. /// </summary> public override void PostExecute() { base.PostExecute();
     cnManager.ReleaseConnection(cnn); } public override void CreateNewOutputRows() { foreach (DataRow dr in dt.Rows) { this.Output0Buffer.AddRow(); this.Output0Buffer.Code = dr["code"].ToString(); this.Output0Buffer.Name = dr["name"].ToString(); } }
复制代码

 

5,Script Component做为Destination,既然是作为Destination,那么肯定是有input column,用以接收上个数据源组件或转换组件的输出数据流。

 

示例代码如下

复制代码
    SqlCommand cmd = new SqlCommand();
    DataTable dt = new DataTable("dt");
    IDTSConnectionManager100 cnManager;
SqlConnection cnn; /// <summary> /// This method is called once, before rows begin to be processed in the data flow. /// /// You can remove this method if you don't need to do anything here. /// </summary> public override void PreExecute() { base.PreExecute(); cnManager = this.Connections.Connection; cnn = (SqlConnection)cnManager.AcquireConnection(null); cmd.Connection = cnn; cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 60000; dt.Columns.Add("code", typeof(string)); dt.Columns.Add("name", typeof(string)); } /// <summary> /// This method is called after all the rows have passed through this component. /// /// You can delete this method if you don't need to do anything here. /// </summary> public override void PostExecute() { base.PostExecute(); foreach(DataRow dr in dt.Rows) { string strSql = string.Format(@" insert into dbo.tbExcel2(code,name) values('{0}','{1}')" , dr["code"].ToString(), dr["name"].ToString()); cmd.CommandText = strSql; cmd.ExecuteNonQuery(); }

cnManager.ReleaseConnection(cnn); } /// <summary> /// This method is called once for every row that passes through the component from Input0. /// /// Example of reading a value from a column in the the row: /// string zipCode = Row.ZipCode /// /// Example of writing a value to a column in the row: /// Row.ZipCode = zipCode /// </summary> /// <param name="Row">The row that is currently passing through the component</param> public override void Input0_ProcessInputRow(Input0Buffer Row) { DataRow dr = dt.NewRow(); dr["code"] = Row.code; dr["name"] = Row.name; dt.Rows.Add(dr); }
复制代码


6,Script Component 作为 Transformation ,转换,顾名思义是将输入进行转换成符合要求的输出,所以,作为 Transformation 的Script Component 既有input columns,也有output columns。

 

示例代码如下,Input0Buffer 这个类中即包含了InputColumns,也包含了OutputColumns,InputColumns的Column是ReadOnly的,通过Input0Buffer 实例对OutputColumns进行赋值,转换数据流。

复制代码
    /// <summary>
    /// This method is called once, before rows begin to be processed in the data flow.
    ///
    /// You can remove this method if you don't need to do anything here.
    /// </summary>
    public override void PreExecute()
    {
        base.PreExecute();
        /*
         * Add your code here
         */
    }

    /// <summary>
    /// This method is called after all the rows have passed through this component.
    ///
    /// You can delete this method if you don't need to do anything here.
    /// </summary>
    public override void PostExecute()
    {
        base.PostExecute();
        /*
         * Add your code here
         */
    }

    /// <summary>
    /// This method is called once for every row that passes through the component from Input0.
    ///
    /// Example of reading a value from a column in the the row:
    ///  string zipCode = Row.ZipCode
    ///
    /// Example of writing a value to a column in the row:
    ///  Row.ZipCode = zipCode
    /// </summary>
    /// <param name="Row">The row that is currently passing through the component</param>
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        Row.codeout = Row.code + "_out";
        Row.nameout = Row.name + "_out";
    }
posted on 2017-04-10 11:12  郑兴鹏  阅读(276)  评论(0编辑  收藏  举报

友情链接:上海回力鞋专卖店 | 中老年高档女装 | 新航道 | 雅思培训一般多少钱 | 托福网课