SSIS 2008、Excel 2007 正确格式化 Excel 列（导出，而不是导入）

在SSIS 2008中，我们可以使用自然语言和一些简单的步骤来正确格式化Excel列，以便在导出数据时获得所需的格式。同时，我们需要使用Excel 2007作为目标文件格式。下面将详细介绍如何实现这一操作。

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
namespace SSIS_Excel_Formatting
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a new SSIS package
            Package package = new Package();
            // Create a new Excel source component
            ExcelSource excelSource = (ExcelSource)package.Connections.Add("EXCEL");
            
            // Set the Excel file path and worksheet name
            excelSource.ExcelFilePath = "C:\\Data\\SourceData.xlsx";
            excelSource.ExcelWorksheetName = "Sheet1";
            
            // Create a new Excel destination component
            ExcelDestination excelDestination = (ExcelDestination)package.Connections.Add("EXCEL");
            
            // Set the Excel file path and worksheet name
            excelDestination.ExcelFilePath = "C:\\Data\\FormattedData.xlsx";
            excelDestination.ExcelWorksheetName = "Sheet1";
            
            // Create a new data flow task
            MainPipe dataFlowTask = (MainPipe)package.Executables.Add("DTS.Pipeline.2");
            
            // Connect the Excel source to the Excel destination
            IDTSComponentMetaData100 excelSourceMetadata = dataFlowTask.ComponentMetaDataCollection.New();
            excelSourceMetadata.ComponentClassID = "Microsoft.ExcelSource";
            excelSourceMetadata.ValidateExternalMetadata = true;
            excelSourceMetadata.Name = "Excel Source";
            excelSourceMetadata.ComponentType = "Source";
            
            IDTSComponentMetaData100 excelDestinationMetadata = dataFlowTask.ComponentMetaDataCollection.New();
            excelDestinationMetadata.ComponentClassID = "Microsoft.ExcelDestination";
            excelDestinationMetadata.ValidateExternalMetadata = true;
            excelDestinationMetadata.Name = "Excel Destination";
            excelDestinationMetadata.ComponentType = "Destination";
            
            IDTSPath100 path = dataFlowTask.PathCollection.New();
            path.AttachPathAndPropagateNotifications(excelSourceMetadata.OutputCollection[0], excelDestinationMetadata.InputCollection[0]);
            
            // Set the column mappings for formatting
            excelDestinationMetadata.CustomPropertyCollection["TableMapping"].Value = "Column1,Date";
            
            // Save the package
            package.SaveToXml("C:\\Data\\SSISPackage.dtsx", null);
            
            // Execute the package
            DTSExecResult result = package.Execute();
            
            // Display the execution result
            MessageBox.Show("Package executed with result: " + result.ToString());
        }
    }
}