使用 SSIS 导出到多个工作表 [英] Exporting to multiple worksheets using SSIS

问题描述

我刚刚开始使用 SSIS，我只是想知道使用 SSIS 使用 2 个 SQL 查询在 1 个工作簿中创建 2 个工作表是否非常简单，或者我是否应该建议另一种方法来生成数据.

I'm just starting out in SSIS, and I was just wondering if it's quite straightforward to use 2 SQL queries to create 2 worksheets in 1 workbook using SSIS, or whether I should suggest another way to produce the data.

推荐答案

是的，这非常简单.您可以在两个 Excel 目标中使用相同的 Excel 连接管理器，只需选择Excel 工作表的名称".

Yes, this is very straightforward. You can use the same excel connection manager for both and in the two Excel destinations, you just select "Name of the Excel sheet".

如果您想使用 OLEDB 创建工作表，您可以执行以下操作:

If you want to create the worksheets using OLEDB you could do something like:

        string destination = "c:\myfile.xls";

        using ( OleDbConnection conn = new OleDbConnection( 
            String.Format( "provider=Microsoft.Jet.OLEDB.4.0; Data Source='{0}';"
             + "Extended Properties='Excel 8.0;HDR=YES;'", destination ) ) )
        {
            conn.Open();

            using ( OleDbCommand cmd = new OleDbCommand( "CREATE TABLE [Sheet1$]([Column1] VARCHAR(255),"
                +"[Column2] DATE,[Column3] INTEGER,[Column4] LONGTEXT)", conn ) )
                cmd.ExecuteNonQuery();


              using ( OleDbCommand cmd = new OleDbCommand( "CREATE TABLE [Sheet2$]([Column1] VARCHAR(255),"
                +"[Column2] DATE,[Column3] INTEGER,[Column4] LONGTEXT)", conn ) )
                cmd.ExecuteNonQuery();

        }

这篇关于使用 SSIS 导出到多个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！