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 not producing information from my COMPUTE BY clause when exporting to Excel Expand / Collapse
Author
Message
Posted Tuesday, March 5, 2013 8:50 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:53 PM
Points: 96, Visits: 296
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.
Post #1427148
Posted Wednesday, March 6, 2013 12:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
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
Post #1427195
Posted Wednesday, March 6, 2013 12:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:16 AM
Points: 141, Visits: 975
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.
Post #1427211
Posted Wednesday, March 6, 2013 9:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:53 PM
Points: 96, Visits: 296
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. =)
Post #1427500
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse