SSIS Any-Query-to-Excel Worksheet?

  • I've got just enough exposure to SSIS to think the following is true: an SSIS project defines all it'sc olumns and datatypes before it does anything like insert them or export them to excel;

    I hope there's a bit more advanced stuff I'm not familiar with.

    I've got a task where the basic idea is going to be some developer gives me 1 to N queries which represent a collection of reports. the columns are not defined in advance.

    So the task would be to create an excel worksheet, and for each query, add a worksheet and populate it with the script results.

    I can do this easily in C#/VB.net, but they want a re-usable SSIS model that a BIDS guys might use use for future similar issues, without involving a developer and a block box executable.

    more specifically, say i receive 4 queries that are going to return results. I'd want to build a specific package for those four queries, that generates an excel sheet with four worksheets;

    then if the query changes, it's a rather simple matter to tweak the query.

    if i have a different group of reports, i'd create a new project , and use the original as a model for doing the same steps.

    does anyone know if it's possible? the resultset would always come from a query against SQL server.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (1/10/2014)


    I've got just enough exposure to SSIS to think the following is true: an SSIS project defines all it'sc olumns and datatypes before it does anything like insert them or export them to excel;

    I hope there's a bit more advanced stuff I'm not familiar with.

    I've got a task where the basic idea is going to be some developer gives me 1 to N queries which represent a collection of reports. the columns are not defined in advance.

    So the task would be to create an excel worksheet, and for each query, add a worksheet and populate it with the script results.

    I can do this easily in C#/VB.net, but they want a re-usable SSIS model that a BIDS guys might use use for future similar issues, without involving a developer and a block box executable.

    more specifically, say i receive 4 queries that are going to return results. I'd want to build a specific package for those four queries, that generates an excel sheet with four worksheets;

    then if the query changes, it's a rather simple matter to tweak the query.

    if i have a different group of reports, i'd create a new project , and use the original as a model for doing the same steps.

    does anyone know if it's possible? the resultset would always come from a query against SQL server.

    Based on the forum, I assume that you're doing this in SSIS 2005?

    Do the same 'black-box' considerations mean that you want to accomplish this without recourse to an SSIS script task? Your VB.NET knowledge should mean that you could eat this problem for breakfast if not.

    --Edit: fix typo.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thanks for looking Phil!

    pretty sure the office is using vs2008 for ssis and ssrs, so that's probably 2008; i couldn't seem to find a 2008 SSIS forum.

    Ironically, i had done something similar in .net already; and i'm in the middle of building an variation this morning with a copy-paste GUI ;

    the code is simple, stick a query's data in a datatable, and then i created a variety of export options (excel,html, pdf, etc)

    the multi page worksheet was a new wrinkle on an old example.

    the issue really is i wanted to help someone else who only used bids as far as ssis/ssrs, and needs a working model of something in ssis so he can accomplish his task of hybrid excel sheets like this.

    the conversation started as how can i make this report in SSRS, and that kind of shaped the original direction.

    i found an example here that i'll check out

    http://www.codeproject.com/Articles/301542/Creating-Excel-File-and-ExcelSheets-dynamically-fr

    that project gets all the tables from sysobjects and creates worksheets of their data.i'd think i can modify it enough to do the same thing i' did in .net.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • i couldn't seem to find a 2008 SSIS forum.

    No, there isn't one - at least last time I checked. But there is a generic SSIS forum here which seems to be used by those running 2008 and later ...

    That article looks promising. One additional complexity you will face is around datatypes - somehow, you'll need to assess the queries' column datatypes (if you use the same method as in the article). My brain is heating up just imagining that 🙂

    But your existing method must get round this somehow, so you should be OK.

    Good luck - sounds like a fun little project.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 4 posts - 1 through 3 (of 3 total)

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