Thanks all. I will be entering a development and testing phase and I will post back if I have any questions or something to add to the conversation.

Thanks for you response.

>>Are you allowed to enter a status for a date in the past, before an existing status?

No, but a date range could be split in which...

If you record sets are fairly small, your networking robust and your security context allows it, linked severs can be a reasonable solution. Performance and troubleshooting can be difficult.

I hear transactional replication is complicated and difficult to support.

http://www.brentozar.com/archive/2013/09/transactional-replication-change-tracking-data-capture/

How often do you need to refresh the destination server?

Ah yes I shouldn't speed read through posts....

Sounds like a job for SSIS or possibly a linked server.

Is your source and destination database on the same server or different servers?

Have a look at Jeff's article:

http://www.sqlservercentral.com/articles/T-SQL/63681/

Think about how you may want to report on or search your data when you consider your categorization, things can go from simple to complicated real fast.

Simple-One category per ingredient....

Given what I imagine is limited data in your ingredients table I would not break the table up for performance reasons. When I say limited I am assuming you have...

Read this http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Your memory theory sounds plausible but I would look at query optimization first before you throw money at the problem.

Thanks Matt. I'm studying your solution.

Sorry about the incorrect desired results as Douglas H pointed out.

Your Foreach Loop container will loop once for every record in your table. Each time it should reset your variables including variable StoredProcedureName and execute the Data Flow Task. If...

Can you add a new variable ExecutionString and concatenate StoredProcedureName, StartDate, EndDate together to give you a string like: EXEC usp_SPName1 '12/20/2013', '12/26/2013'

Then set the SQLCommandVariable to the new variable...

Try setting the AccessMode property of the OLE DB source to 'SQL Command from Variable'. Then try setting the SQLCommandVariable to the name of your variable.

Do all your source stored procedures return identical recordsets? Or another way of asking this, do all you flat file outputs have the same format?