Hi ...been awhile since I last signed in... new job back to my favorite db platform SQL Server 🙂
I'm stuck a dilema and wanted to lean on wisdom of experienced DBAs/Devs on this site. (It's a great community on this website)
I've now the defacto DBA/IT guy for our Servers/DBs. The organization has been rebooted every 2-3 years and new people come in and out... it's a mess... however some great ideas on DB / Dev side but nothing fully implemented.
I'm trying to work towards consistency and a long term vision...
What aspect are guidelines when to use SSIS vs SPROC?
(Do you have a centralize SSIS/ETL server? )
- I see staff here embed a few sprocs into SSIS -
The sproc does no ETL really.. just execution of small sets of updates or insert small rows of data. (nothing large of vast) ..less than 1000 records
..."seems" like a candidate for plain old SQL agent job with multiple job steps (maybe) .. however if devs use SSIS they can wrap the package in a transaction.. Developers may not do that with a sproc/SQL script.. (unless policed/audited thoroughly)
- Then I see staff use SSIS with ETL for it's true purpose pulling a ton of data which seems to be right approach in those circumstances.
However SSIS allows for centralizing ETL/job processing and management much easier perhaps.
What have other people done with Guidelines or (if you have some) dictated or used to help guide teams in the right direction. I know there's not a one size fits all, but interested in more consistency.
Thank you for any thoughts/feedback
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀