SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Any-Query-to-Excel Worksheet?


SSIS Any-Query-to-Excel Worksheet?

Author
Message
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28566 Visits: 39977
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!

Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18843 Visits: 20460
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28566 Visits: 39977
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
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18843 Visits: 20460
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search