The idea that SOE had James Bond-esque 'missions' with a specific objective is a bit of a misconception. SOE agents were generally not given specific tasks to "kill X" or "steal information from Y", although these kinds of missions did occur. Instead, SOE agents were tasked with building resistance networks in their target areas with a view to carrying out attacks, conducting sabotage and gathering intelligence. Specific targeted missions (at least in coastal areas) were more the responsibility of more militarised units like the SAS.

However, while some agents were inserted with the general task of building up networks in specific areas, others were told to build networks with specific priorities in mind. For example, Claude de Baissac was sent to Bordeaux to set up a network, codenamed "Scientist", which would have the specific priority of sinking German merchant ships carrying valuable cargo from the Far East which used Bordeaux. Unfortunately before the network could commence their attack, a unit of commandos unaware of the existence of the Bordeaux network kayaked up the estuary and attacked the ships with limpet mines.

Similarly, in the case of the destruction of the heavy water plant, an initial party of operatives was parachuted in to lay the groundwork for the attack by doing a reconnaissance of the plant and making contact with resistance members in the area. They were supposed to be joined by an assault party of Royal Engineers but the two gliders carrying them crashed and the party was either killed or captured. In a second attempt, a smaller team joined the initial party in carrying out the attack with great success.

Most agents however were tasked with building up networks in their areas for general tasks, and were given priority targets. For example, a network near the coast would be tasked with scouting out beaches for potential landings, gathering intelligence on ship and U-Boat movements and where possible carrying out acts of sabotage against military targets. A network in Paris on the other hand might be more concerned with gathering intelligence.

In terms of the 'significance' of missions, it's not really possible to say what actions were most significant or had the greatest effect, because there's no objective metric like "days the war was shortened by" or "allied lives saved". The big ticket missions like the assassination of Reinhard Heydrich by SOE agents in Prague were few and far between and most actions carried out by SOE operatives were relatively small; the destruction of a railway bridge or assassination of an officer. In one notable instance, two SOE agents kidnapped the garrison commander from Crete and smuggled him back to Egypt.

You mention D-Day as being a significant mission, but in actual fact it was hundreds of smaller actions carried out across the country which made such a difference during the landings. These actions certainly had a quantifiable effect. For example, the 2nd SS Panzer division was sent to attack the Allied landings on the 6th of June, but was so delayed by sabotage and allied air attacks co-ordinated from the ground that it took 22 days to get there.

Throughout the war, SOE agents and networks carried out hundreds of assassinations and acts of sabotage, and SOE operated escape lines helped hundreds of people to safety outside of occupied Europe. In pretty much every book which has even a brief mention of SOE, you'll find Winston Churchill's famous order to "set Europe ablaze". You might well say that instead of having significant missions, SOE had one mission, and in that it was extraordinarily successful.