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

 

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, 0, buf.Length);                            output.Write(buf, 0, 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, 0, buf.Length)                        output.Write(buf, 0, 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, "Write header", ex.Message, String.Empty, 0)             ' Close Script Task with Failure            Dts.TaskResult = ScriptResults.Failure        End Try    End SubEnd Class

 

 

TWO VARIABLES:

ROWCOUNT INT32

TOTALSALES INT32