在SSIS中添加页眉和页脚行平面文件

本文关键字：平面文件添加 SSIS | 更新日期: 2023-09-27 18:17:35

我有一个SSIS包，它将查询中的数据导出到平面文件中，该文件将用于导入到数据仓库中。我的要求之一是添加具有当前日期的标题行和具有总行数的页脚行。

我想在一个脚本组件或使用c#的任务中理想地做到这一点，以便在包中整洁。说到写代码，我还是个新手。如何做到这一点?我在网上找了一遍，但似乎找不到任何接近我想要的。

在SSIS中添加页眉和页脚行平面文件

下面是您可以用于脚本任务的代码，它将允许您输出带有页眉和页脚的CSV:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
namespace ST_80294de8b8dd4779a54f707270089f8c.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()
        {
            int ErrorFlag = 0;
            // Try-Catch block 
            try
            {
                int RowCount = 0;
                bool fireAgain = true;
                string SQLCommandText = "SELECT ColumnA = 1, ColumnB = 'A' UNION SELECT ColumnA = 2, ColumnB = 'B' UNION SELECT ColumnA = 3, ColumnB = 'C';";
                SqlConnection SQLConnection = new SqlConnection("Data Source=LocalHost;Initial Catalog=master;Integrated Security=SSPI;Application Name=SSIS-My Package Name;Connect Timeout=600");
                SqlCommand SQLCommand = new SqlCommand(SQLCommandText, SQLConnection);
                SQLCommand.CommandTimeout = 60 * 60;
                SqlDataAdapter SQLDataAdapter = new SqlDataAdapter(SQLCommand);
                DataTable dt = new DataTable();
                SQLDataAdapter.Fill(dt);
                SQLConnection.Close();
                RowCount = dt.Rows.Count;
                Dts.Events.FireInformation(0, "DataTable Rows", RowCount.ToString(), "", 0, ref fireAgain);
                StreamWriter sw = new StreamWriter("C:''Test.csv", false);
                // Write the header.
                sw.Write("Today's date is " + DateTime.Now.ToLongDateString());
                // Write the column headers.
                sw.Write(sw.NewLine);
                int iColCount = dt.Columns.Count;
                for (int i = 0; i < iColCount; i++)
                {
                    sw.Write(dt.Columns[i]);
                    if (i < iColCount - 1)
                    {
                        sw.Write(",");
                    }
                }
                // Write the details.
                sw.Write(sw.NewLine);
                foreach (DataRow dr in dt.Rows)
                {
                    for (int i = 0; i < iColCount; i++)
                    {
                        if (!Convert.IsDBNull(dr[i]))
                        {
                            sw.Write(dr[i].ToString());
                        }
                        if (i < iColCount - 1)
                        {
                            sw.Write(",");
                        }
                    }
                    sw.Write(sw.NewLine);
                }
                // Write the footer.
                sw.Write("Row count: " + RowCount.ToString());
                sw.Close();
            }
            catch (SqlException e)
            {
                Dts.Events.FireError(0, "SqlException", e.Message, "", 0);
                ErrorFlag = 1;
            }
            catch (IOException e)
            {
                Dts.Events.FireError(0, "IOException", e.Message, "", 0);
                ErrorFlag = 1;
            }
            catch (Exception e)
            {
                Dts.Events.FireError(0, "Exception", e.Message, "", 0);
                ErrorFlag = 1;
            }
            // Return results. 
            if (ErrorFlag == 0)
            {
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            else
            {
                Dts.TaskResult = (int)ScriptResults.Failure;
            } 
        }
    }
}

你也可以不借助c#来完成这个，但是这会有点难看:

变量1:用于分配数据流2的行数的Int变量
变量2:带有生成SQL命令的表达式的字符串变量。如果变量1命名为RowCount，那么下面是它的示例代码集:
"选择一列圆柱 = '" + ( DT_WSTR, 1252)(@[用户::RowCount]) +"ColumnB = NULL"
数据流1:执行SQL命令生成文件头，并输出到平面文件目的地。设置"覆盖文件中的数据"为true
数据流2:执行SQL命令生成平面文件的详细信息。将"覆盖文件中的数据"设置为false。包含行数转换并将值赋给变量1。
数据流3:执行SQL命令生成平面文件的页脚。源程序应该"从变量设置命令"，并且应该执行变量2。设置"覆盖文件中的数据"为false

嗨，这些会帮助你…

http://www.bidn.com/blogs/KeithHyer/bidn-blog/1990/adding-a-header-or-footer-row-to-a-fixed-width-file-using-ssis

http://agilebi.com/jwelch/2008/02/08/adding-headers-and-footers-to-flat-files/

问候,杰森

这就是我最终想出的!这是我能找到的最干净、最简单的方法。它基本上只是建立头和尾行，然后追加到数据集。一旦你做到了，就会觉得很简单!这需要一些c#知识，但是比起尝试用SQL来做，这是值得的。

Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
using System;
using System.Text;
using System.IO;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
namespace ST_db04adc927b941d19b3817996ff885c2.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
        /*
        The execution engine calls this method when the task executes.
        To access the object model, use the Dts property. Connections, variables, events,
        and logging features are available as members of the Dts property as shown in the following examples.
        To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
        To post a log entry, call Dts.Log("This is my log text", 999, null);
        To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
        To use the connections collection use something like the following:
        ConnectionManager cm = Dts.Connections.Add("OLEDB");
        cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
        Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        To open Help, press F1.
    */
        public void Main()
        {
            const string dirPath = @"C:'SSIS'Dev'";
            DateTime minusoneweek = DateTime.Today.AddDays(-7);
            DateTime minusoneday = DateTime.Today.AddDays(-1);
            var headerRecord = ("0|" + DateTime.Today.ToString("ddMMyyyy") + "|" + Dts.Variables["LastSequenceNumber"].Value + "|" 
                + Dts.Variables["FileName"].Value) + "|" + minusoneweek.ToString("ddMMyyyy") + "|" + minusoneday.ToString("ddMMyyyy");
            var fileBody = AddHeaderAndFooter.GetFileText(dirPath + "blank.txt");
            var trailerRecord = "9|" + AddHeaderAndFooter.CountRecords(dirPath + "blank.txt").ToString();
            var outPutData = headerRecord + "'r'n" + fileBody + trailerRecord + "'r'n";
            AddHeaderAndFooter.WriteToFile(dirPath + "blank.txt", outPutData);
        }
    }
    public static class AddHeaderAndFooter
    {
        public static int CountRecords(string filePath)
        {
            return (File.ReadAllLines(filePath).Length + 2);  
        }
        public static string GetFileText(string filePath)
        {
            var sr = new StreamReader(filePath, Encoding.Default);
            var recs = sr.ReadToEnd();
            sr.Close();
            return recs;
        }
        public static void WriteToFile(string filePath, string fileText)
        {
            var sw = new StreamWriter(filePath, false);
            sw.Write(fileText, Encoding.ASCII);
            sw.Close();
        }
    }
}