You can either execute the SQL statement you provided in an Execute SQL Task, but put an INSERT INTO before it of course 🙂

Or you could use the SQL statement in an OLE DB source in a dataflow and write the results to table C using an OLE DB Destination with fast load option.

Another alternative that doesn't use the staging database is reading the two flat files A and B in a data flow, sorting them and do a MERGE JOIN on PRODUCT_GROUP_ID and writing the result to table C. The sort is necessary as it is a prerequisite for the MERGE JOIN. If your files are very large, I would choose for the staging area.