Hi Heather
There is a lot to consider.
Will you be truncating features in the older SDE sql server table and then appending new features into this same table? In this scenario, any configuration made in SDE will not be migrated across. SDE configuration in each database remain independent. How much backward compatibility between enterprise geodatabases is an important consideration here too. There are limits in how far back a version you can go between SQL Server and ArcGIS.
If you want to delete the older SDE sql server table completely and migrate a new copy as a fresh table - then any SDE configuration is deleted and you have to reconfigure these tables as feature classes\enterprise geodatabase artifacts. Also consider downstream impacts of deleting an artifact. For example, if you have a ArcGIS Server service registered this feature class as a data source and it gets deleted, then in many cases, a data outage occurs until you republish.
From my limited experience, SSIS doesn't 'out of the box' understand enterprise geodatabases concepts like feature classes unless you specifically include updates to SDE system tables. I strongly recommend not to modify SDE system tables directly.
I'm my opinion, other products like ArcGIS Data Interoperability, FME Workbench, even python using arcpy can handle interoperability between data model frameworks and formats and backwards compatibility of enterprise geodatabases much better and a bit more maturely than SSIS.
I would be interested to see what others have done with SSIS too.
Cheers Ed
