Post reply

Export to multiple worksheets in same spreadsheet?

  • December 16, 2009 at 1:54 am

    #137677

    Is it possible for a SSIS package to export to multiple worksheets within the same excel spreadsheet?

    I have a request to execute a query that returns data for specified accounting GL codes for the current finacial year.

    The sheet name should be that of the GL Code.

    So for instance: Query to return

    GL Code

    10001.0000

    .....10001.0001

    .....10001.0002

    .....10001.0003

    .....10001.0004

    20001.0000

    .....20001.0001

    .....20001.0002

    .....20001.0003

    .....20001.0004

    30001.0000

    .....30001.0001

    .....30001.0002

    .....30001.0003

    .....30001.0004

    SSIS package would create a spreadsheet containing 3 worksheets named '10001','20001, and '30001'.

    Each worksheet would provide details of posting to accounts for current finacial year.

    Kind Regards,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • December 16, 2009 at 4:54 am

    #1092916

    [font="Comic Sans MS"]

    Yes - this is possible. You need to use excel destination and can use for each loop container to achieve this.

    You can refer here:

    http://msdn.microsoft.com/en-us/library/ms345182.aspx

    [/font]

    [font="Comic Sans MS"]--
    Sabya[/font]

  • December 16, 2009 at 6:01 am

    #1092944

    Thanks for the link.

    I am looking to go the other way. Run a SQL query that outputs to excel. For each GL Code create a seperate worksheet in the excel file to be created by the package.

    Many Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • December 16, 2009 at 8:17 am

    #1093028

    [font="Comic Sans MS"]

    Sorry - couldn't understand the process. Can you please explain me further?

    Are you executing T-SQL to get the data exported into excel ? Are you planning for any automation or this is an adhoc operation?

    [/font]

    [font="Comic Sans MS"]--
    Sabya[/font]

  • December 16, 2009 at 1:07 pm

    #1093273

    Hi, yes T-SQL to to Excel and I was hoping to automate.

    Kind Regards,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • December 16, 2009 at 2:28 pm

    #1093318

    If you are using EXCEL earlier than 2007 have you tried OPEN ROWSET .

    An example:

    INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;Database=D:\MSSQL\Test.xls', SELECT * FROM [Sheet1$]')

    SELECT * FROM Keywords

    You would have to use a separate statement for each sheet (Sheet1$) or (Sheet2$) etc for each accounting range you desire to export. For using different rows / columns then substitute (Sheet1$a4:C )

    which will start exporting row 4 using columns a thru c (original from a forum post by Jeff Moden)

    If using EXCEL 2007 the command changes slightly in that the driver used is different: For example:

    OPENROWSET('Microsoft.ACE.OLEDB.12.0',

    'Excel 12.0;

    Other requirements are:

    1. Spread sheet must exist on the SERVER

    2. in row 1 acting as column headers must be the name

    of the table field being exported.

    A great discussion with examples is available at: simple talk

    http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • December 16, 2009 at 3:47 pm

    #1093340

    Thanks for that, very much appreciated.

    Kind Regards,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • December 17, 2009 at 10:03 am

    #1093591

    In testing I received error "Microsoft.ACE.OLEDB.12.0" has not been registered.

    I have done some research and apparently there is not a ACE provider for 64bit?

    I am running Windows 7 Ultimate 64bit + Office 2007?

    In production my query will execute within an SSIS package (imported into SSMS) and run as a scheduled job. Excel is not installed on the production server.

    Can anyone give me a heads up on what is required?

    Query Example that I would like to output to excel: \\server\share\file.xls

    DECLARE @GLType bigint

    SET @GLType = 2666

    DECLARE @Year bigint

    SET @Year = 2010;

    WITH GLPostings AS

    (

    SELECT glca.GLChartOfAccountId,SUM(gli.GLItemValue) AS GLCodeTotal,ac.AccountingYear,ac.AccountingPeriod,

    DENSE_RANK() OVER(PARTITION BY glca.GLChartOfAccountId ORDER BY glca.GLChartOfAccountId) AS dr

    FROM GLAccountTypes AS glat

    LEFT OUTER JOIN GLChartOfAccounts AS glca ON glat.GLAccountType = glca.GLAccountType

    LEFT OUTER JOIN GLItems AS gli ON glca.GLChartOfAccount = gli.GLChartOfAccount

    LEFT OUTER JOIN AccountingCalendar AS ac ON CONVERT(VARCHAR,gli.CreatedDate,103) = CONVERT(VARCHAR,ac.AccountingDate,103)

    WHERE glat.GLAccountType = @GLType AND ac.AccountingYear = @Year

    GROUP BY glca.GLChartOfAccountId,ac.AccountingYear,ac.AccountingPeriod

    )

    SELECT glp.GLChartOfAccountId,glp.GLCodeTotal,glp.AccountingYear,AccountingPeriod

    FROM GLPostings AS glp

    Thanks

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • December 17, 2009 at 11:17 am

    #1093645

    Ran across this article which might spread some light on your problem:

    http://blogs.msdn.com/data/archive/2007/06/05/64-bit-oledb-provider-for-odbc-msdasql-available-in-longhorn-server-starting-beta-3.aspx

    I believe the required driver is also available to download from:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • December 18, 2009 at 1:44 am

    #1093906

    Thanks for the links.

    Kind Regards,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • December 18, 2009 at 4:40 am

    #1093955

    OK, I have made some progress (testing on 32 bit XP laptop).

    Firstly I added a linked server:

    DECLARE @rc int

    DECLARE @server nvarchar(128)

    DECLARE @srvproduct nvarchar(128)

    DECLARE @provider nvarchar(128)

    DECLARE @datasrc nvarchar(4000)

    DECLARE @location nvarchar(4000)

    DECLARE @provstr nvarchar(4000)

    DECLARE @catalog nvarchar(128)

    -- Set parameter values

    SET @server = 'XLTEST_SP'

    SET @srvproduct = 'Excel'

    SET @provider = 'Microsoft.ACE.OLEDB.12.0'

    SET @datasrc = 'c:\book1.xlsx'

    SET @provstr = 'Excel 12.0'

    EXEC @rc = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,

    @datasrc, @location, @provstr, @catalog

    I have not referencd the linked server in preceeding code.

    1) SSIS - SQL Command

    I am able to execute SSIS package to Excel Destination (quite a simple example).

    Problem - Each run appends to the file rather than replace.

    2) SSMS - Below works OK but again data is appended on each execution

    USE [AdventureWorks];

    GO

    INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\ExportExcel\contact.xlsx;',

    'SELECT * FROM [Sheet1$]')

    SELECT TOP 5 FirstName, LastName

    FROM Person.Contact

    GO

    INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\ExportExcel\contact.xlsx;',

    'SELECT * FROM [Sheet2$]')

    SELECT TOP 15 FirstName, LastName

    FROM Person.Contact

    3) SSMS Using Stored Procedure

    CREATE PROCEDURE bsp_GLExport

    @GLType bigint,

    @Year bigint

    AS

    WITH GLPostings AS

    (

    SELECT glca.GLChartOfAccountId,SUM(gli.GLItemValue) AS GLCodeTotal,ac.AccountingYear,ac.AccountingPeriod,

    DENSE_RANK() OVER(PARTITION BY glca.GLChartOfAccountId ORDER BY glca.GLChartOfAccountId) AS dr

    FROM GLAccountTypes AS glat

    LEFT OUTER JOIN GLChartOfAccounts AS glca ON glat.GLAccountType = glca.GLAccountType

    LEFT OUTER JOIN GLItems AS gli ON glca.GLChartOfAccount = gli.GLChartOfAccount

    LEFT OUTER JOIN AccountingCalendar AS ac ON CONVERT(VARCHAR,gli.CreatedDate,103) = CONVERT(VARCHAR,ac.AccountingDate,103)

    WHERE glat.GLAccountType = @GLType AND ac.AccountingYear = @Year

    GROUP BY glca.GLChartOfAccountId,ac.AccountingYear,ac.AccountingPeriod

    )

    SELECT glp.GLChartOfAccountId,glp.GLCodeTotal,glp.AccountingYear,AccountingPeriod

    FROM GLPostings AS glp

    [/Code]

    Finally I tried to execute the stored procedure using OPENROWSET

    USE [DataBase];

    GO

    INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\ExportExcel\export.xlsx;',

    'SELECT * FROM [Sheet1$]')

    exec bsp_GLExport

    This returned error:

    Msg 7390, Level 16, State 2, Line 1

    The requested operation could not be performed because OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not support the required transaction interface.

    Can anyone point me in the right direction?

    Many Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply