Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Any-Query-to-Excel Worksheet? Expand / Collapse
Author
Message
Posted Friday, January 10, 2014 12:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 12,905, Visits: 32,166
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1529933
Posted Saturday, January 11, 2014 5:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 5,180, Visits: 12,033
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1530026
Posted Saturday, January 11, 2014 6:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 12,905, Visits: 32,166
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1530027
Posted Saturday, January 11, 2014 6:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 5,180, Visits: 12,033
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.





Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1530029
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse