SSIS-文本文件增加标题行

概述一般来说，如果标题行没有计算，可以用表达式。 但是，如果有计算，比如，行数目，数量总计等，需要用SCRIPT TAKS AND SCRIPT COMPONENT.   DATA FLOW TASK:  FLAT FILE-ROW COUNT-SCRIPT COMPONENT-FLAT FILE WITHOUT HEADRE CONTRO TASK: DATA FLOW-SCRIPT TASK  

一般来说，如果标题行没有计算，可以用表达式。

但是，如果有计算，比如，行数目，数量总计等，需要用SCRIPT TAKS AND SCRIPT COMPONENT.

DATA FLOW TASK:

FLAT file-ROW COUNT-SCRIPT COMPONENT-FLAT file WITHOUT headRE

CONTRO TASK:

DATA FLOW-SCRIPT TASK

COMPONENT:

// C# Code// Basic script for calculating// row totals without aggregate.using System;using System.Data;using Microsoft.sqlServer.Dts.Pipeline.Wrapper;using Microsoft.sqlServer.Dts.Runtime.Wrapper; [Microsoft.sqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]public class ScriptMain : UserComponent{    // Declare variable for the total sales    int ColumnTotal = 0;     public overrIDe voID PostExecute()    {        base.PostExecute();        // copy value of script variable to SSIS variable        this.Variables.TotalSales = ColumnTotal;    }     public overrIDe voID input0_ProcessinputRow(input0Buffer Row)    {        // Add value of sales column to script variable        ColumnTotal += Row.Sales;    }}

' VB.Net Code' Basic script for calculating' row totals without aggregate.imports Systemimports System.Dataimports System.Mathimports Microsoft.sqlServer.Dts.Pipeline.Wrapperimports Microsoft.sqlServer.Dts.Runtime.Wrapper <Microsoft.sqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _<CLSCompliant(False)> _Public Class ScriptMain    inherits UserComponent     Dim ColumnTotal As Integer = 0     Public OverrIDes Sub PostExecute()        MyBase.PostExecute()        ' copy value of script variable to SSIS variable        Me.Variables.TotalSales2 = ColumnTotal    End Sub     Public OverrIDes Sub input0_ProcessinputRow(ByVal Row As input0Buffer)        ColumnTotal = ColumnTotal + Row.Sales    End SubEnd Class

TASK:

// C# Code// Script Task for appending header row on top of textfileusing System;using System.Data;using System.IO;      // Addedusing Microsoft.sqlServer.Dts.Runtime;using System.windows.Forms; namespace ST_cc10f7cb927344e7a8239df8ebf9bca3.csproj{    [System.AddIn.AddIn("ScriptMain",Version = "1.0",Publisher = "",Description = "")]    public partial class ScriptMain : Microsoft.sqlServer.Dts.Tasks.ScriptTask.vstaRTScriptObjectModelBase    {         #region vsta generated code        enum ScriptResults        {            Success = Microsoft.sqlServer.Dts.Runtime.DTSExecResult.Success,Failure = Microsoft.sqlServer.Dts.Runtime.DTSExecResult.Failure        };        #endregion         public voID Main()        {            try            {                // Determine filenames using the Flat file Connection manager                string filename = Dts.Connections["MyFlatfile"].ConnectionString;                string tempfilename = filename + ".temp";                 // Create temporary copy of source file                file.Move(filename,tempfilename);                 using (StreamReader input = new StreamReader(tempfilename))                {                    using (StreamWriter output = new StreamWriter(filename,false))                    {                        // Create header in empty file                        output.Writeline("ROWCOUNT: " + Dts.Variables["RowCount"].Value.ToString() +                            " TOTAL: " + Dts.Variables["TotalSales"].Value.ToString());                         // Create a buffer. This is needed for large files that won't fit in the servers RAM                        var buf = new char[4096];                         // Read temporary copy of source file in blocks                        // and write in blocks to empty file with header                        int read = 0;                        do                        {                            read = input.ReadBlock(buf,buf.Length);                            output.Write(buf,read);                        } while (read > 0);                         // Clear and close                        output.Flush();                        output.Close();                        input.Close();                    }                }                 // temporary copy of source file                file.Delete(tempfilename);                 // Close Script Task with Succes                Dts.TaskResult = (int)ScriptResults.Success;            }            catch (Exception ex)            {                // Log error                Dts.Events.FireError(0,"Write header",ex.Message,string.Empty,0);                 // Close Script Task with Failure                Dts.TaskResult = (int)ScriptResults.Failure;            }        }    }}

' VB.Net Code' Script Task for appending header row on top of textfileimports Systemimports System.Dataimports System.IO       ' Addedimports System.Mathimports Microsoft.sqlServer.Dts.Runtime <System.AddIn.AddIn("ScriptMain",Version:="1.0",Publisher:="",Description:="")> _<System.CLSCompliantAttribute(False)> _Partial Public Class ScriptMain inherits Microsoft.sqlServer.Dts.Tasks.ScriptTask.vstaRTScriptObjectModelBase  Enum ScriptResults  Success = Microsoft.sqlServer.Dts.Runtime.DTSExecResult.Success  Failure = Microsoft.sqlServer.Dts.Runtime.DTSExecResult.Failure End Enum      Public Sub Main()        Try            ' Determine filenames using the Flat file Connection manager            Dim filename As String = Dts.Connections("MyFlatfile").ConnectionString            Dim tempfilename As String = filename + ".temp"             ' Create temporary copy of source file            file.Move(filename,tempfilename)             Using input As StreamReader = New StreamReader(tempfilename)                Using output As StreamWriter = New StreamWriter(filename,False)                    ' Create header in empty file                    output.Writeline("ROWCOUNT: " + Dts.Variables("RowCount").Value.ToString() + _                                     " TOTAL: " + Dts.Variables("TotalSales").Value.ToString())                     ' Create a buffer. This is needed for large files that won't fit in the servers RAM                    Dim buf = New Char(4095) {}                     ' Read temporary copy of source file in blocks                    ' and write in blocks to empty file with header                    Dim read As Integer = 0                    Do                        read = input.ReadBlock(buf,buf.Length)                        output.Write(buf,read)                    Loop While read > 0                     ' Clear and close                    output.Flush()                    output.Close()                    input.Close()                 End Using            End Using             ' temporary copy of source file            file.Delete(tempfilename)             ' Close Script Task with Succes            Dts.TaskResult = ScriptResults.Success         Catch ex As Exception            ' Log error            Dts.Events.FireError(0,String.Empty,0)             ' Close Script Task with Failure            Dts.TaskResult = ScriptResults.Failure        End Try    End SubEnd Class

TWO VARIABLES:

ROWCOUNT INT32

TOTALSALES INT32

总结

以上是内存溢出为你收集整理的SSIS-文本文件增加标题行全部内容，希望文章能够帮你解决SSIS-文本文件增加标题行所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。