I gave the question some more thought and what I enjoy most about the main story is, when it is more a theme that the other stories are riffing of than an actual story. In Act 2 everything is about gods and the relationships the characters have with them. Lae'zel and Shadowheart have their different brands of deep devotion, Gale and Halsin have more personal relationships with their respective deity, Kethric changes gods according to his needs, Astarion has given up on them and sees faith as a tool of worldly power, etc. I like how they explored theses themes and I like finding about more about the cult and about what happened to Kethric's army. I am not sure if the main story is good but I like the investigative work you can put in to piece everything together.
What I don't like - in general and thus in relationship to the main story - is when my character is singled out as a hero despite always acting in a group. I hate it, when the player dialogue reads "I did heroic thing". Why put so much emphasis on bonding with your companions, when you cannot reflect it in dialogue? It especially bugs me on the origins who on their "good" routes learn the value partnership and teamwork. I know many have complaint that the original ending narration - "you are the heroes of Baldur's Gate" - isn't fitting for "evil" characters. It feels a bit out of place for someone like Gale too, who on his good route sheds his fancy monicker and reclaims his family name (I would have loved an opportunity to do that in his Origin.) and I could imagine a few Durges might share the sentiment, maybe?
Another thing that I didn't like was the whole second Emperor reveal. I have no special sentiment on the topic itself but I strongly dislike how it was handled. I think the fancy, somewhat pretentious little dungeon is cute. It's also very fitting content for poor Wyll (if you drag him along) who hasn't much stuff dedicated to him that he doesn't have to share with other characters, and it feels somehow special in the way it was presented. It could have been fun to actually research why things end up not being as expected, instead of having the Emperor hijack the scene. If you send me out to search for a dragon, I want to focus on the dragon and not on my squid frenemy. I am very done with characters hijacking scenes that aren't their's at this point.
