Due to the vast and heterogeneous nature of research data repositories and the multidimensionality of geospatial data, finding relevant data in the field of Earth System Sciences (ESS) is challenging. Traditional lexical search approaches often fail to capture user intent, especially in complex, cross-domain queries and federated discovery requests across distributed repositories. This study introduces and evaluates two distinct large language model (LLM)-based architectures for dataset discovery: a pre-structured, deterministic workflow and a flexible, agent-based system. Both architectures utilise a robust retrieval pipeline featuring federated search, semantic re-ranking, and spatio-temporal filtering. Using two test collections (N4E-IR and ACORDAR-2.0), we conducted a comprehensive evaluation of general retrieval performance, an ablation study, spatio-temporal query handling, and multi-turn conversational capabilities. Both LLM-driven approaches significantly outperform the traditional BM25 baseline in NDCG@10 (0.86 and 0.81 versus 0.57 on N4E-IR, and 0.70 and 0.67 versus 0.53 on ACORDAR-2.0), with the agent-based architecture showing a slight advantage in multi-turn conversations. The ablation study revealed that LLM-based reranking was the most impactful component in the search pipeline. These findings confirm that LLMs are highly effective for ESS dataset discovery, enabling natural language interaction, intelligent query decomposition, and context-aware understanding of spatio-temporal constraints.