asked
SSIS variables using Excel sheet and aggregating data
I have an Excel sheet with data from a SQL query. I need to perform some aggregations on the values to get the minimum and maximum years, and the minimum and maximum quarters in the list of fields. I need to calculate the total number of counts for the volumes in each year based on the number of quarters in each year. Volume || Start Year ||Start Quarter || End Quarter || End Year 3 2013 2 4 2016 The difference in quarters between the years will give me the total count of all volumes falling in the selected years. I am trying to assign variables for Minimum and Maximum years as well as Minimum and Maximum quarters to find the total of volumes. Is it possible to assign an SSIS variable for each item in the Excel sheet and then calculate the number of volumes for the selected years?
Do you want to calculate for each row or in a different way? If it is for each row then no need for using variables...derived column should do the trick.
I need to calculate the total volumes for each year and each quarter of the year. I have a function in SQL which does this but the business wants an SSIS package. My function is (total volumes, start quarter, start year, end quarter, end year). This gives me the total volumes for each year and each quarter in that year. Can I do something similar with derived columns?
Its doable depending upon the business logic...Can you post the business logic along with some raw data...derived column is basically a calculated column.
VolRow || Vol || StartYear || StartQuarter || End Quarter || End Year 28 2 2013 2 4 2016 28 4 2014 1 3 2015 The logic is that I will add the total volumes for the volume row selected. Then I will get the earliest year, earliest quarter, latest year and latest quarter for the volume row selected. Using the function I mentioned earlier, I will get a breakdown of total volumes for each year. I understand using a derived column will break down the values, but how would I use the derived columns to get the total volumes for the year?
Glad...please mark below as the answer if it solved the problem
Ok here is what you can do After the excel source use aggregate transformation (google it for how to use it) You would be doing group by volrow Use sum of volumes. Min or max of year....min or max of quarter So you will get volums. Min year. Max year This should do the job and give you breakdown ....then we could decide whether to use derived column or not
Ok here is what you can do After the excel source use aggregate transformation (google it for how to use it) You would be doing group by volrow Use sum of volumes. Min or max of year....min or max of quarter So you will get volums. Min year. Max year This should do the job and give you breakdown ....then we could decide whether to use derived column or not
19 People are following this question.
