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


SSIS not producing information from my COMPUTE BY clause when exporting to Excel


SSIS not producing information from my COMPUTE BY clause when exporting to Excel

Author
Message
J M-314995
J M-314995
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 328
When I run a script that uses COMPUTE BY in SSMS I get the desired results. When I try and create an SSIS package to export out to Excel, it completely ignores the COMPUTE BY clause. I need to do a subtotal by a certain group but I don't need to sum any of the columns.

I've been reading about GROUP BY (ROLLUP) but I can't get that to work. It wants me to add every single column name into the GROUP BY clause which of course ruins how I want the data to look when it's exported out to Excel.

Here's a sample of how I'd like the data to look like once it gets to Excel...




This is where I'd want a subtotal on the post_amt on every change in admit_svc_code. Like I said this works just fine if I use COMPUTE BY within SSMS but that line is ignored when I try and export it out to Excel. I know how to make this work if I export the raw data into Excel but I'd like the data to already look like that from the SSIS export.
Orlando Colamatteo
Orlando Colamatteo
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: 18969 Visits: 14398
Per Books Online "The COMPUTE and COMPUTE BY clauses are provided for backward compatibility." so use those in new development at your own risk. They do not exist in SQL Server 2012.

If you still want to use COMPUTE BY, know that it returns a second resultset. You can capture both if you execute your query to Fill a DataSet within a Script Task. You can then iterate over the DataSet to write your Excel file, but all of this would be custom .NET coding and you wouldn't really be using much of the built in SSIS functionality at that point.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Dung Dinh
Dung Dinh
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 1231
J M-314995 (3/5/2013)
When I run a script that uses COMPUTE BY in SSMS I get the desired results. When I try and create an SSIS package to export out to Excel, it completely ignores the COMPUTE BY clause. I need to do a subtotal by a certain group but I don't need to sum any of the columns.

I've been reading about GROUP BY (ROLLUP) but I can't get that to work. It wants me to add every single column name into the GROUP BY clause which of course ruins how I want the data to look when it's exported out to Excel.

Here's a sample of how I'd like the data to look like once it gets to Excel...




This is where I'd want a subtotal on the post_amt on every change in admit_svc_code. Like I said this works just fine if I use COMPUTE BY within SSMS but that line is ignored when I try and export it out to Excel. I know how to make this work if I export the raw data into Excel but I'd like the data to already look like that from the SSIS export.


How about using SSRS to export data into the excel file? SSRS supports sub-total on which columns you want.
J M-314995
J M-314995
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 328
I'm not really a .Net programmer so I can't go that route. I do understand short and simple C# and VB scripts but I know more python and ruby than I do .Net languages.

I quit using SSRS a few years ago when the "double hop" issue was introduced on our system when our software vendor integrated the main set of apps we use with active directory. Looks like I'll have to try and re-tackle this issue to get my subtotals. I know this should be on a separate thread but I've been hesitant to setup an SPN because I haven't had the time to look into the security risks in using SPN's (if there are any).

Thank you for the responses though. =)
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