I would love some suggestions of tools to assist with the ETL or ELT process. I have a Dev environment in a domain that I pull new records into which is an SQL VM in Azure that I need to massage and push to our Prod SQL Server in a different domain. Traditionally I have done SSIS but I know there are much better solutions now. I am playing with data pipelines but struggling with messaging when the pipeline errors out. I'm thinking about a NoSQL solution to stage all the data and massage before pushing to the Prod SQL Server but not sure if that's really the right way to go either. Normally I would move Dev to Stage to Prod all in SQL Server.
Things need to go in a particular order and most of it is in Stored procs right now. The amount of data I need to pull and push is in the thousands, not the millions so it's not huge.
Does anyone have any suggestions on the strategy of doing this with better technology?