Exporting to multiple worksheets using SSIS

问题:

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.

回答1:

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".

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();

        }