How to use siunitx and tabularx together?

The X column in tabularx is then converted to p{<width>} where width is automatically calculated. You can change this by redefining the \tabularxcolumn macro like described in the package manual:

\renewcommand{\tabularxcolumn}[1]{<column definition where #1 is the width>}

The S column uses the c column internally. To replace this with p{<width>} you have to manually place the internal column definition of S into \tabularxcolumn. The following code worked in my tests.

\documentclass{article}

\usepackage{array}
\usepackage{tabularx}
\usepackage{siunitx}


\begingroup
% Allow `_` and `:` in macro names (LaTeX3 style)
\catcode`\_=11
\catcode`\:=11
% Internal code of `S`
\gdef\tabularxcolumn#1{%
    >{\__siunitx_table_collect_begin:Nn S{} }%
    p{#1}%  <- this is different (is `c` in normal `S`)
    <{\__siunitx_table_print:}%
}
\endgroup

\begin{document}

\begin{tabularx}{\textwidth}{p{1.5cm} XXX}
     bla & 1.23 & 4.5  & 67.89 \\
     bla & 1.2  & 4.50 &  7.89 \\
     bla &  .2  &  .50 & 67.8 \\
\end{tabularx}
\end{document}

Taking Martin's solution and re-coding for version 1 of siunitx, you'd do something like

\documentclass{article}

\usepackage{siunitx,tabularx}

\makeatletter
\def\tabularxcolumn#1{%
    >{\si@tab@begin@S[]}%
    p{#1}%  <- this is different (is `c` in normal `S`)
    <{\si@tab@end@S}%
}
\makeatother

\begin{document}

\begin{tabularx}{\textwidth}{p{1.5cm} XXX}
     bla & 1.23 & 4.5  & 67.89 \\
     bla & 1.2  & 4.50 &  7.89 \\
     bla &  .2  &  .50 & 67.8 \\
\end{tabularx}
\end{document}

The quite new tabu package seems to be very encouraging. Section 1.5 of its manual introduces explicitly an example for how to embed siunitx's »S« columns in »X« columns. Regarding tables this package seems to be an allrounder.