- Our IT team is saying that if we move to production... [users] can access the database only via API or Tableau or reporting services. is that so?
This very much sounds like a policy. From technical point of view, there is no reason why direct connections would not be possible. That being said, production admins are responsible for the production environment not going haywire, so this kind of restrictions are quite common in the industry. The current buzzword DevOps is pushing this limit a bit, but on more traditionally oriented houses there is little if any change.
The reason why production staff are quite strict about the access limits is simple. If something breaks in the production, it's their fault. Even if the real culprit was a developer running a cross-join-o-matic, dropping indexes and generally being a clown. Production admins are supposed to have the environments running at top performance, and letting those down for any reason is a sign for problems.
For a solution, talk with your team lead and boss. Write a memo that outlines the pros and cons the new method would introduce. Don't just list the obstacles, tell what's good too and propose solutions. The memo should give an impression that you are a problem solver, not a whiny complainer. After that, it's your boss' call what to do.
- Can I connect to the production database via SSMS?
In theory, yes. There might be firewalling or company policy that prevents such an action. Do not try to work around policies or technical barriers on your own! You might get into serious trouble.
- If I can't connect to the production server, how to develop ssis packages ?
Develop the packages on development environment. Push those to the test and let production admins move packages into the production.
