Nevermind, just select subtotal on context menu again and it goes away. How obvious.
Still, after reading various posts here I realize that it's not obvious at all how to put column subtotals at bottom of a matrix. let's say I have a simple matrix with one row group and one column. All I want is to have total at bottom of each data column.
If you add a "Union All" task to your Data Flow the output columns get configured by the first input you connect to it.
However, if you later want to modify the input columns on the inputs (perhaps after adding an upstream Data Conversion) there seems to be no way to get it to forget the original set of output columns.
Ideally, there would be a button you can press to reset the output columns based on the earliest added input. At the very least it ought to forget its output columns when the last input is removed.
Other tasks offer a dialogue to resolve input / output mismatches but not Union All.
or am I missing something?
jks
If you right-click on a column name underneath "Output Column Name" you are given the option to delete that column.
Is that what you want?
-Jamie
|||I agree, while deleting or adding back one column is not the most painful process, many times while working on a package my upstream compenents changed a lot and I found myself deleting my Union All and Merge tasks and adding them back in because it was the easiest way to update them.
It would be nice if there was a refreash of some sort that would reexamine and update any added/deleted columns.
|||Thanks Jamie,
That helps - I had seen Delete in the right-click menu but it was always disabled when I needed it. Probably because I was trying to delete a new column I had just added without saving the task in between. I guess I'm used to records being committed when you move off them.
However, I still think it would be useful if Union All offered the same sort of intelligent reconfiguration options presented by other tasks. Any columns you've added to a data flow upstream ought to be propogated through the first input of a Union All to its output.
regards
jks
|||I can see why this (i.e. propogating the first input) would be useful but by the same token there may be people that would want the behaviour to be as it is now. As with most things you can't please all the people all of the time. Personally I think the functionality is fine as it is. Matter of opinion I guess.
Its worth saying that UNION ALL is an asynchronous component and this is the fundamental reason that any inputs don't automatically propogate through to the output. You'll find that the same is true of all asynchronous components.
-Jamie
|||Ok - I hadn't appreciated the distinction between sync and async components. If this is a consistent architectural feature I'll bite my tongue until I understand the architecture a little better!
This is just SSIS day 3 for me.
regards
jks
|||jks,
Well it sounds like you are headlong into it already. I recommend that you get your head around a few concepts pretty early. Namely:
The container hierarchysqlQuote:
I have an application that I inherited, and I have a annoying problem. We're using stored procedures to return most of our data, and occasionally we receive errors stating that a particular column cannot be found in the resulting data table. When I run the stored procedure against SQL Server I receive the expected output. What would make this random act happen, any ideas?
Also, I keep receiving errors stating that a connection is already open and needs to be closed before an action to the database is performed. I'm explicitly closing each connection in a finally block for every method in my data access code, so a connection should always be closed, right?
Does the code the runs the stored procedure site inside a try...catch, if so make the catch log the parameter that were supplied to the stored procedure. Also I would log the number of rows in the datatable. This should allow the problem to be reproduced.
>before an action to the database is performed
What action(s) give rise to this message.
For completeness, which version of SQL Server and ASP.NET are you using?
|||Here is an example of the code:
SqlDataAdapter da = new SqlDataAdapter("GetWFSAInfo",SQLcn);
DataSet ds = new DataSet();
da.SelectCommand.Parameters.Add("@.WFSAID",SqlDbType.Int).Value = WFSAID;
da.SelectCommand.CommandType = CommandType.StoredProcedure;try {
da.Fill(ds,"GetWFSAInfo");
}
catch (Exception ex) {
NotifyOfException (ex);
}
finally {
SQLcn.Close();
}return ds;
Originally, none of this code was in try/catch/finally blocks at all, so I had to weed through over 11,000 lines of data access code to put these constructs in. That seems to curb some of the problems. I prefer to use a class library that I created that takes care of all of this for me. I swear, object orientation, inheritance and the like and the previous programmer were never introduced. Aggravating to say the least...
NotifyOfException logs the exception to a log file. I am using SQL Server 2000/ASP.NET 1.1.
Thanks for all of your help...
|||You will need to modify the catch
catch (Exception ex) {
NotifyOfException (ex);
}
to
catch (Exception ex) {
ex.Data = WFSAID;
NotifyOfException (ex);
}
This assumes of course that WFSAID is a variable.
