asked
ssis-conditional split script
I have 2 temp tables (#class) (#student) it performes delete from #class where studentID in (select studentID from #student) I want to automate that process ...and i am trying to do it by using the conditional split but since i am not so good at it.. its working differently. Please let me know how can split the data based on the above condition. Or is there anyother way to work around with this? **I cannot use a staging table.** I have a simple t-sql script which needs to be automated and needs to run every end of the month. The t-sql is using 2 temp
tables.It takes about 15mins to
run.As I cannot use the temp tables in the OLEDB source. I have modified to table variables which is taking long time to run (18hrs). The script is something like Select a.StudentID, b.Name, c.rollno, d.payment, c.amount, into #class from number a left join rollno b on..... select a.StudentID, k.name, d.description, f.amount, k.paidamount into #student from class a left join rollno b on....... DELETE FROM #class where StudentID in (SELECT StudentID from #student) (final output) select a.studentID sum(amount), from #class group by a.studentID This is the whole script, so i was trying to automate this process. I hve used 2 OLEDB sources one for #class and other for #student And using conditional split to split based on the condition: DELETE FROM #class where StudentID in (SELECT StudentID from #student) The challenge is that i cannot use any staging table nor I can create any
sproc.So i hve to do all in the dataflow. Please suggest. How can i achive this task. Thanks in advance
@palum Seems like you can group the data before moving to the conditional split. This way you could enhance speed of the exclusion process. Rest is left to SSIS GURU @Pavel Pawlowski, as 2 or 3 options are available to handle this. I was thinking of Merge Join as well to handle it differently if I had the sorted inputs :)
What you are trying to automate? What you want to split u sing a Conditional split? Can you clarify your problem a little bit more closer?
So it seems, you want to return students from `#class` temp table, which are not present in `#student` temp table. As @Usman Butt mentioned, based on your example, you can group the data in the initial SELECT. Also you can remove all the `LEFT OUTER JOIN` which are not used in the final output. As example, you have in the first select `left join rollno b` and `b.name` but in the fial output you use only `StudentID` and `Amount`. The same applies to the second select. It has only sense to include only `INNER JOINs` or joins which can limit the number of output records. Based on those assumption, you can use eg. a **`Lookup Tranformation`** and use **`No MatchOutput`**. **THIS IS FOR SSIS Solution** And the final solution will be as follow: **OLE DB Source 1** Select a.StudentID, SUM(c.amount) from number a JOIN someTable c ON .... GROUP BY a.StudentID **Lookup Transformation** Connect OLE DB Source to Lookup Transformation, use Full Cache mode and select **`Redirect rows to no match output'**. In the Connection use query: SELECT DISTINCT a.StudentID FROM class a Connect `SudentID` fields in the columns and use the **`No match output`** which should contain records requested. It is also possible to use a **`Merge Join transformation`** (inner join) followed by **`Conditional split transformation`** as again @Usman Butt mentioned. One or other solution can be chosen depending on the profile of the output of either queries. **SINGLE QUERY SOLUTION** You wrote, you cannot use temp tables, but from the sample it seems, you do not need the temp tables at all. You can write single query to receive results. There is no need to use temp tables. Select a.StudentID, SUM(c.amount) from number a JOIN someTable c ON .... WHERE a.StudentID NOT IN (SELECT StudentiID from class) GROUP BY a.StudentID **In general I would prefer the single query solution here, unless you have to make this in SSIS because of any reason.**
