Excellent. Thank you for the post.
Here's what I did.
I created two variables: MonthEndDatabase and MonthEndDate as both STRINGS at the PACKAGE LEVEL. I set MonthEndDatabase = DB_MonthEnd_ and the MonthEndDate blank.
Wrote SQL: select convert(varchar(10),getdate(),112) AS [GetDate] to give me the date in YYYYMMDD
Added a Execute SQL Task inthe Control Flow, set the ResultSet to Single row, connected to any database on my instance because it doesnt matter where I get the date from, and added the above SQL statement to the SQLStatement seciton of the task.
Then clicked on the Result Set option on the left side of the window. Added a Result Name of GetDate and set it to the variable USER::MonthEndDate
Added a new OLE DB connection to the server/instance and LEAVE the Database EMPTY. Test connection to make sure we're all good...!
Right click on the new connection just created above and select properties
In the properties window, add an expression of InitialCatalog property to expression of @[User::MonthEndDatabase] + @[User::MonthEndDate]
Added a new Execute SQL Task and connected it to OLE DB connection with the empty Database Name created above
In the SQLStatement, added the EXEC <stored proc>
Press OK save and repeat as needed connecting all other tasks that need to use the new month end backup to the connection above.
Reviewed the output and I was successfully able to extra data from the generic database I created today called DB_MonthEnd_20130711... YEAH! :-D:-D:-D