SSIS: Changing connection string in Data flow dynamically

Recently I was developing an SSIS package where I had to source data values from Oracle and SQL Server. The SSIS package had multiple For..Loop containers to mimic threading structure in SSIS. Since, at design time I couldn't identify which SQL query (the source) would execute in which For..Loop container, I decided to source the connection string from a table in my SQL Server and then assign it to the OLEDB source at runtime.

For package validation purposes, I had an Oracle connection assigned to the OLEDB source at design time.

Everything works fine till here. But when I ran the package, I found that it will throw an error

Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Invalid object name 'MyTable'".

And

[DTS.Pipeline] Error: "component "Alert values from Oracle" (585)" failed validation and returned validation status "VS_ISBROKEN".

After much turmoil I found that for some weird reason the "Initial Catalog" value assigned in the connection string for SQL Server doesn't work as expected. And since the schema assigned to the package user would connect to Master database it couldn't locate the table mentioned.

Solution: Use the absolute table path in the FROM clause like

select column1 from dbname.schema.mytable

And that did the trick.