38 OLE DB Command Transformation in SSIS | OLEDB Command Transformation 

best channel on youtube

is it possible to create this with insert select?

Hi

Let's say I have a flat file that ONLY contains data for every student that graduated that year (student_list AS s). Now let's say in my SQL Server database, I have a table (active_students AS a) that only has the students that have NOT graduated. My goal is to DELETE from the active_students table WHERE a.student_id = s.student_id So I say hey, with SSIS, I can use a flat source and connect that to an OLE DB Command Transformation, and simply run the query: DELETE FROM active_students WHERE student_id = ? In the column mappings, I now map s.student_id -> Param_0 Now, let's say the active_students list is 100,000 students. and the graduated student_list is 10,000 students, and it's taking the query SEVERAL HOURS to complete. Would it not be faster to just: 1.) Create a temp table 2.) Copy the data from the student list flat file to the temp table 3.) Run the Execute SQL Task in Control Flow with the statement: DELETE FROM active_students WHERE student_id IN (SELECT student_id FROM student_list) 4.) Drop temp table If this gives the same result and is significantly faster, why should I use the OLE DB Command Transformation in this case?