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.

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

        }