With all the data moving in and out of your system, security is a big concern. I explain the security within SSIS in this section; before I do, however, you need to be aware of some other security aspects.
When you create your import schema, it is tempting to focus on only the technical challenges and design solutions. But you should resist the urge to jump right in and begin coding. Make sure you follow the process of gathering the requirements, documenting the process, and then implementing the solution.
When you do that, you should include each source system and the data path. You might think that if you are pulling production data from the human resources system and then stripping out the names and address before you move it into your system that you are safe. But if you leave the data on an FTP server or Web service before you bring it over, you need to identify that location as a risk factor and protect it accordingly. In most of the systems I have consulted on, I have found major security breaches on the "staging" areas.
After you have the data within the destination, standard SQL Server security takes over. I have covered what you need to be concerned about in that area earlier in this book. Within SSIS, you can apply three roles to allow your developers access to create and manage packages.
You can also increase security by protecting the package when you create it, and even the objects it contains. Access the Properties panel of the object or package to set the following protection levels.