38 OLE DB Command Transformation in SSIS | OLEDB Command Transformation

OLE DB Command Transformation in SSIS | OLEDB Command Transformation
Download the file\script used in the Video from below link
drive.google.c...
SSIS Tutorials: • SSIS Tutorials
SSIS real time scenarios examples: • SSIS real time scenari...
SSIS Interview questions and answers: • SSIS Interview questio...
OLE DB Command Transformation in SSIS
OLEDB Command Transformation
What is difference between OLE DB command and execute SQL task in SSIS?
How do you use variables in OLE DB command in SSIS?
What is Ole in SSIS?
What is OLE DB Destination in SSIS?
If you have any questions or suggestions please comment on the video or write to me at “[e-mail]”

[@bermagot9238]

best channel on youtube

[@learnssis]

Thanks for appreciating bermagot.

[@gustavosiq90]

is it possible to create this with insert select?

[@learnssis]

Yes, Insert and select is possible as well, but for that you would need to create an sp with input and output parameters and then map the columns accordingly from data flow to stored procedure.

[@divyakudeti7825]

Hi

[@divyakudeti7825]

SSIS in Full video in how to create in laptop

[@learnssis]

@@divyakudeti7825 Sorry Divya did not get you.

[@bermagot9238]

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?

[@learnssis]

Yeah OLE DB Command is not recommended for large datasets. The second method will be very faster thus you can avoid using the OLE DB Command in this case.
OLE DB Command should be used if you don't have any other option.
Once I had a requirement that I had to load data from DB2 and insert it to sql and before inserting it to SQL table, I had to check for a group id which was need to be calculated from another table based on few input columns. Now the challenge was that if the group id for those columns was already in the another table then I had to pick the existing group id and if group id for those columns is not in the sql table then I had to insert a new record to the table and GroupId was an identity to it will create a new group id for those columns and then finally pick that new group id and insert to final destination table. Thus I created a stored procedure to check if group id exists or not and return the correct group id based on input columns and I used OLE DB command in this case. Thus OLE DB Command should only be used if you don't have any other option. Thanks.