I have a requirement to refresh some tables in dev from prod...and we need to do this every week...so i need to automate...
I thought of using SSIS, but never did it....if anyone did please let me know the process or your suggestions.
When you mean refresh, you mean drop and recreate the whole table? Are you keeping some records, if that's the case, how many? ... how big the table is or will be? ...
SSIS maybe faster but also more difficult to maintain.
Depending of how many records and where those two instances are, you may consider bcp as well. You export via bcp, then import via bcp. You can put that logic inside a job and there you go, you have it scheduled.
If you need to wipe out the whole table, you can drop indexes prior import, truncate, and re-import. Take a look on dependencies though, like constraints or identity keys. That may be an issue when refreshing the table, even with SSIS.
If you go that route, here's a good link or article for bcp: https://www.simple-talk.com/sql/database-administration/working-with-the-bcp-command-line-utility/