[This subject has been brought up a couple times, but the original post is from 2017. I'm reposting my recent comment here because I feel like nobody will see the old 2017 post because this site only shows the most recent by default. Original post: https://support.timextender.com/hc/en-us/community/posts/115018657763-Ability-to-include-powershell-script-actions-in-TX]
We and several of our clients would benefit greatly by having the ability to execute a PowerShell script from within TimeXtender as Script Actions.
Here are a couple scenarios:
Scaling Azure SQL DB instances with SQL is problematic. I can use a SQL statement to scale a DB up prior to the load, but we are unable to create a loop and wait for the DB to finish scaling. The problem is that we can wait.... but the session disconnects when the larger DB is ready and Azure SQL switches the connection. The SQL Stored Proc is never able to determine if the database scaled successfully.
A Powershell script could be called in order scale the DB as an alternative and since it's session will not be disconnected during the resizing process, it would allow for a scale, wait, confirm, and continue process.
This could be implemented as an external executable for execution packages, but that would require multiple execution packages to properly sequence the scale up, load, scale down process.
If Powershell was available as a type of Script Action which could be called at the table level, it would also satisfy the requirement of a client of ours as well. (See #2)
My client has written C# processes to extract data from another system. Ideally, these extracts needs to be a part of the Discovery Hub load so the data in refreshed in a timely manner without trying coordinate many different schedules.
Their C# process to pump data into custom tables may be rather unique to the user community except for a mention here:
However, this is something else which Powershell could help with instead of creating many clumsy SSIS Packages and External Executables. Custom SSIS Packages really are not an option for them since there would be so many to create.
SSIS *Could* be an option IF the external executable SSIS Package calls allowed parameters to be passed in. Then a more generic SSIS package could be created to execute a process for which the name is passed. This however doesn't help in an Azure scenario where SSIS isn't available.