SSIS Any-Query-to-Excel Worksheet?

Question

SSIS Any-Query-to-Excel Worksheet?

Lowell

SSC Guru

Points: 323518
More actions
January 10, 2014 at 12:26 pm

#281941

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!

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

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

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Lowell SSC Guru Points: 323518 More actions · Answer 2

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!

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin