Question/Problem Description
As a developer, I used VS DataTools to create simple, straight-forward ETLs to feed data into my projects. I get to know about using a 3rd party tool that adds auditing to SSIS packages. What tools should I use to help manage complexity and improve productivity?
Answer
Auditing does not help to manage complexity and/or improve your productivity. Maybe logging and tracing, but this is a different thing than auditing. Only need SSMS, stored procedures and SQL Server Agent jobs for this.
BI xPress which is part of the Pragmatic Workbench for auditing, but the product is EOL. The biggest way that auditing helps with productivity is when an SSIS package fails loading a flat file and it can pinpoint you to the line that’s causing the failure. If you have time-sensitive processes, that’s a benefit, especially when processing large files.
Idera is a company that has tools offer for free. I’ve used Idera’s free tools to examine the queries and identify indexes that need to be added for performance.
The most important SQL writing tool in SSMS: Redgate SQL Prompt can speed up SQL writing up to 25% to 75%.
The rest is using a source code repository and a good, automate test environment.