The quickest way around this issue would be to rename the scripts with a leading 0, e.g. 01, 02, 03 etc. This down to how the file names are encoded, specifically as they are encoded as strings this ordering will happen.
If you can let me know which step you are using I can investigate further and see if there is anything we can do on the encoding front, however that will take some time.
Thanks Ayan, let me know if there is anything else you need help with.
I would request you to validate if below suggestion will help us to deploy script in correct order -
• If scripts need to be executed in particular sequence, then scripts have to organize chronologically, tool will always run them in the correct order
For Eg: Naming scripts as below will give us the correct order :
Script0001xxx.sql / Script0001-GetCustomTask.sql
Script0002xxx.sql / Script0002-GetGlobalSearch.sql
Script0003xxx.sql / Script0003-UsersAccess.sql
• Ensure file name shouldn’t start with ‘ ) ‘. For Eg: 1.) ABC_OEMS.sql
Error line from Log File:
Invoke-ExecuteSQLScript : System.Management.Automation.RuntimeException: The regular expression pattern
1)XYZ_OEMS.sql is not valid. —> System.ArgumentException: parsing “1)ABC_OEMS.sql” - Too many )'s
• Would suggest refraining from using any of these characters [^$.|?*+() in your filenames, as they have special meaning in Regex
Please let me know if you want to add or modify here.
That all seems logical to us. We use a similar convention for our database scripts internally. E.g. Script0001-ScriptName.sql etc.
Are you finding that this is working for your scenario?
The best way to validate this is to create a test project in Octopus with a test dacpac package, then you can test various combinations and ensure you have correct naming conventions in place. You can use empty/dummy SQL scripts in your package and run this against a test database, then watch the output to ensure you get the expected order.
