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)....