Were the scientists working on the Manhattan Project (or their German counterparts) expecting their project would be successful, or did they envision it as an abstract theoretical project, such as attempts at fusion? Were there any dissenters/conscientious objectors over ethical considerations?

The explicit goal of the Manhattan Project — its entire reason for being — was to produce working nuclear weapons that would be usable within the time constraints of World War II. That is what, essentially, made it the Manhattan Project: it was a production project, as opposed to the exploratory (Uranium Committee) and pilot (S-1 Committee) projects that had proceeded it. It was only undertaken because the scientists believed there was a very good chance it would be successful. In fact, they were overly optimistic. They thought it would cost five times less than it did, imagined it involving nearly an order of magnitude fewer people, and thought they'd have success sometime in 1944, not late summer 1945. So if anything, they overestimated their chances of success. By the time they realized it was going to be harder than they thought, those involved in planning were so invested that they were willing to push it to completion, no matter the increase in costs.

So it was definitely never considered abstract and theoretical. The abstract and theoretical work was the Uranium Committee stage of things (from 1939-1941). Then came an acceleration that sought to prove that the theories were true on a modest scale (the S-1 Committee, 1941-1942), then the approval of the crash production program (the Manhattan Project, 1942-1946).

There was, during this time, some tolerance of work that was not immediately expected to "pay off" in terms of a wartime outcome. For example, there were very small-scale studies of nuclear fusion as a weapons possibility. These were justified primarily by the way they might impact the future of nuclear weapons: if the hydrogen bomb was a possibility, that would influence postwar thinking about whether and how to control the spread and development of nuclear technology. There was no expectation that nuclear fusion would play a role in World War II, however; it was understood that even if it was possible (which was not clear), it would still require the creation of a fission bomb first, and that was the goal of the Manhattan Project.

On the question of dissenters — it's complicated. There was only one person who left the project early on because of concerns about it (Edward Condon, who quit Los Alamos because he felt the secrecy was too strict), and only one person who left in the late stages supposedly because of ethical issues (Joseph Rotblat, who left after the Nazis were defeated; whether this is because, as is often claimed, he felt that he had only agreed to work on the project to defeat the Nazis, or because he was eager to search for his family, who had been sucked up by the Holocaust, is not entirely clear). There was a small group of scientists who attempted to argue that the atomic bomb should not be used on Japanese cities without warning (and wrote a report to this effect), and there were scientists who circulated a petition making this plea. But these efforts were hindered by secrecy and compartmentalization, deliberately marginalized by the Manhattan Project leadership, and not that representative of overall opinion of the scientists who knew about the purpose of the project (most of whom were too caught up in the rush of the work to debate or even consider the ethics until after the weapons were used). And most of the people who worked on the project (the hundreds of thousands of workers, nearly 1% of the entire US civilian labor force during WWII) had no idea that they were contributing to an atomic bomb project.

Anyway, there is much that could be said more on this, but the general gist of your question seems to be whether or not the scientists knew that they were making atomic bombs that would be used in war and whether they had a problem with that. The answer is that there were many scientists who did know what they were doing, they were definitely trying to make a usable weapon, and their opinions on whether it ought to be used varied, though most of them don't seem to have voiced any views on that at the time (and were deliberately discouraged from doing so by the military). In the postwar period, however, these questions about the ethical responsibilities of scientists became absolutely central ones.