I need to make packages on two different servers.
From one server, I need to export data on daily basis in to flat files and another package will import data from those flat files to database.
But I need to make package in systematic way as below:
1. Configuration manager (so that manage at run time at deployment). FYI, i can manage it through XML configuration.
2. Logging i.e. how many insert count and update count from a particular table at destination server when import from flat files will be occur.
3. If session break in mid either import/export then all transaction should be rollback. Also, need to give option to user in XML configuration to execute package on date range basis.
4. How can i manage packages like first master records should be inserted and after that transaction records will be inserted so that Foreign Key constraints should not be violated?
4. The package should be executed by SQL Server Agent on daily basis.
Kindly give answers as gradually aforesaid questions.