使用 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 导出到多个工作表

相关推荐