I currently use SSIS to replicate table from our source systems to our reporting data stores, which are identical to the productions systems with historical data. I am looking into switching our process to use SQL Server Replication as it is much faster, requires less support, and requires much less development time.
Four of our source systems have identical schema's with completely different data in them. With SSIS, I pull in all of the data from each of these systems, and I add a SourceSystemID to them before unioning them and putting them into one table. Is this possible with SQL Server Replication?
