I do have the variable @FOO populated correctly. Now when click the Parameters... button I get the following error regardless as to which method I use above.
Something else I forgot to mention. The global variable @FOO is populated via an Execute SQL Task via a third connection to the destination database (SQL Server). Then...