February 10, 2015 at 5:54 am
Hi Team,
I 've got the below RESULT SET after several JOINs & deriving columns from different sources
STUD_ID, ANNUAL_YEAR, COURSES_ID, SUBJECTS_OPTED, CHAPTERS, YEAR_JOINED, Course_Tenure, Sub_ID, SUB_MARKS, EXAM_ID, ATTEMPT_YEAR, ATTEMPT_NO and many more.
Now, I've only one result set available in my DATA FLOW. And I want the below few,
1) STUD_RESULTS:TABLE : Evaluate COURSE-Annual year completion:
Have columns Students, Annual_yr, Results (IF ANY SUB _MARKS are below 35? 'PASS': 'Fail') and few more.
<--I've got to group the result set on STUD_ID and ANNUAL_YEAR.
2) STUD_COURSE_PERF: TABLE: for course performance.
I've got to group the result set on STUD_ID,ANNUAL_YEAR, Course_ID and the other essential
3) STUD_SUB_PREF: TABLE: Evaluating Subjects difficulty
I've got to group the result on STUD_ID,ANNUAL_YEAR, Course_ID, SUBJECTS_OPTED and other essentail.
How to use the only result set to carry on different evaluations?
I Applied Multicast on the only result set. But now What?
Please help.
February 10, 2015 at 6:50 am
Technically SSIS isn't designed for this type of thing.
You can do Item 1 is simple just use a derived column with IIF logic see here : http://www.programmersedge.com/post/2008/09/30/SSIS-Using-IF(IIF)-logic-in-Derived-Column.aspx#.VNoLb02zVQs
The issue with the other two items is that they are different sets so you need to split the stream and pass the data through two different aggregation steps. You should also note that the Aggregation task like the Sort is a blocking component, and will act as a bottleneck especially on large datasets.
If you're piping into different destinations its not a problem if its a single destination then you have to re-join the datasets, which can be PITA.
I always try and push the aggregations and other logic back into the database, so that in this case I have three base queries. then use SSIS to do only minor manipulations.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply