Consider the case where $S=\bigcup_{n\in\Bbb N}P_n$ and each $P_n$ is finite, but $S$ does not have a countably infinite subset. In particular, $\prod P_n=\varnothing$ (this is the typical case when we give the "socks and shoes" example).
Since $S$ is infinite, but not countable, it is uncountable. For every $s\in S$ consider $X_s=\{n\mid s\in P_n\}$. In this case clearly $\bigcup_{s\in S} X_s=\Bbb N$, but there is no $J\subseteq S$ such that $|J|=\aleph_0$ to begin with. And if we weaken this to require $|J|\leq\aleph_0$, then we still don't cover everything because $J\subseteq S$ and $|J|\leq\aleph_0$ implies that $J$ is finite.
But we can do slightly better. Consider such $S'=S\cup\Bbb N$ and partition it to pairs, $P'_k$ such that:
$$P'_k=\begin{cases}P_n & k=2n\\\{k,k-1\} & k=2n+1\end{cases}$$
Then $S'$ has a countably infinite subset, but the product is still empty. In that case we can find $J$ non vacuously, but as long as the product of these pairs is empty we cannot have a well-ordered subcollection of $X_s$ covering the entire natural numbers.
Alright, so we can have this situation with countable subcovers being insufficient, what about well-ordered subcovers?
If you consider Andre's answer, you will see that if a [sub]cover is well-orderable then we can do the same trick. In particular if you consider an uncountable ordinal, then the union of that subcover was already achieved by a countable subset.
This by no mean that we can't do this in an almost trivial manner. Consider $S$ as before, or in fact any example of the failure at $|J|=\aleph_0$, and let $S'=S\cup\Bbb R$ now partition $\Bbb N$ into even and odds. The indexing with $S$ witnessing the failure will cover exactly the even integers, and $\Bbb R$ will index all the subsets of the odd integers.
We still have that every countable subcover only covers a proper subset of $\Bbb N$. Even if $\Bbb R$ is well-orderable, and as large as you want it to be. But this happens trivially, in the sense that every uncountable well-orderable cover has a countable subcover which covers the same set.