I used to work that way about 15 years ago.

Here is a sample process developing using SSDT. It assumes that devs do development locally.

1. Dev checks out a new Git...

That is rather a large topic! But SSDT allows you to create a 'project' for each of your databases. These projects are contained in one or more 'solutions'.

Each project contains...

This has happened to me several times, and it's a pain.

But uninstalling and reinstalling 'Microsoft SQL Server Integration Services Projects' and SSDT has worked in fixing it.

Next step would be...

Deploying using SSDT takes care of this for you. Have you considered using that?

Another possibility would be to snapshot your UAT database prior to deploying and do a revert in...

Does this work? Likely not, as XML things tend to behave differently from how you would expect.

SELECT *
FROM TEST.SDE.label l
WHERE EXISTS
(
    SELECT 1
 ...

You could put the statement 'pause' at the end of your script. It will prevent the window auto-closing and maybe there will be some sort of informational message there.

What happens when you try this ... is there an error?

But this will be hosted on production server where there are all other databases get backed up and patching etc. I agree that we should not support a database...

No problem.

Just picking up on one point in your last comment, I 100% agree with using stored procs to implement business logic on the SQL Server side. I mostly use...

Thank you for the screenshots, that helps explain what is going on. But I don't think I can help. I do not understand why it is complaining about SQL syntax...

There are reasons for separating logic into multiple packages, even though it may seem less tidy and requires more config up front.

Number 1 is around error handling and the debugging...

SSIS is not the best place for DDL – it's best to do your table creates before going near SSIS.

Were you able to successfully create a Connection Manager in SSIS?...

You have created a table in MariaDb and you are using an ExecuteSQL task containing

INSERT INTO SampleTable VALUES
    ('2023-05-31', 1.5, 2.3, 3.1, 4.2, 100,...

Can you post the SQL which is causing the error?

If you can purchase third-party software products, the following may be of interest:

https://www.cozyroc.com/ssis/data-flow-task

But, as stated above, the structure of SSIS packages is fixed at design time. If you create separate...