What is the major use of recordset destination in SSIS?I heard that it is an in-memory,so the variable which is holding the data is it in raw format? Can someone explain the explain me the real time project use of Recordset destination?
A recordset destination can be used for just about anything you can think of. Some common uses I hear is to use the recordset in a foreach loop. Say you want to export several "categories" from a transaction table. Perhaps you get a recordset of the categories that exist and then call a new dataflow to export that category as it's own file. Or perhaps date ranges, months, etc.
One way I use it is in a script task to perform an action on the data that SSIS cannot do natively. I was using a script component but this particular task ran into a concurrency issue. So by dumping to a recordset I was able to use the recordset in a script task to do the logic in a manner to avoid that issue.
Another script task use is to build and send HTML emails.
I suppose a use for it might be when you have 1 data flow to get 1 record set then do a bunch of non dataflow tasks and then use that as a source in another data flow task, but that is not something I have ever done.