SSIS-文本文件增加标题行
来源:互联网 发布:iebook软件下载 编辑:程序博客网 时间:2024/04/28 06:47
一般来说,如果标题行没有计算,可以用表达式。
但是,如果有计算,比如,行数目,数量总计等,需要用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
- SSIS-文本文件增加标题行
- 为DataGridView控件增加行标题
- SSIS
- SSIS
- SSIS
- ssis
- SSIS
- 鼠标右键增加“新建文本文件”菜单
- ListView 动态增加列标题
- 增加discuz 7.2标题长度
- UITableview 增加分组标题喝脚标
- sql 2008的SSIS应用方法: 将多个一定格式的文本文件批量导入到数据库
- dede增加(修改)文章标题长度
- ttk.treeview增加标题单击排序功能
- Java增加窗口、设置窗口标题
- Jqgrid给序号列增加标题名称
- 调用API加载DLL方法打开文本文件及修改标题
- 读取文本文件,并为每一行增加行号
- 基于tcp的sockets的编程--客户机
- Hibernate详解(3)——一对一映射分析
- Java学习:构造函数特点
- 学习C语言- 第一步关键字特点方法(1)
- android 实时更新与通知 (android poll)
- SSIS-文本文件增加标题行
- WIN7 以管理员身份运行
- 杭电1042——求N!大数处理问题
- C语言学习之符号篇
- android自定义组件之TopMenu
- 类的构造函数调用其他构造函数
- s3c6410的RTC在linux中的驱动(3)
- 简单的文件读写函数举例
- android项目的出现小红叉,如何重构