[custom transformation] dbt transform fails: "Could not find adapter type sqlserver!"

[ibrentlam]

I'm importing JSONL data. I have airbyte transforms that work with both PostgreSQL and SQL Server Destinations.
I want to tweak the dbt transform for SQL Server.
so I follow the procedure at:
https://docs.airbyte.com/operator-guides/transformation-and-normalization/transformations-with-dbt
and the first step is to verify as:

#!/usr/bin/bash

sql server

NORMALIZE_WORKSPACE="672/0"
export NORMALIZE_WORKSPACE

docker run --rm -i -v airbyte_workspace:/data -w /data/$NORMALIZE_WORKSPACE/normalize --network host --entrypoint /usr/local/bin/dbt airbyte/normalization debug --profiles-dir=. --project-dir=.

This works fine with the PostgreSQL workspace, but with the SQL Server workspace, I get:

14:15:58 Running with dbt=1.0.0
dbt version: 1.0.0
python version: 3.9.9
python path: /usr/local/bin/python
os info: Linux-6.2.0-1018-azure-x86_64-with-glibc2.31
Using profiles.yml file at /data/672/0/normalize/profiles.yml
Using dbt_project.yml file at /data/672/0/normalize/dbt_project.yml

14:15:58 Error importing adapter: No module named 'dbt.adapters.sqlserver'
Configuration:
profiles.yml file [ERROR invalid]
dbt_project.yml file [OK found and valid]

Required dependencies:

• git [OK found]

1 check failed:
Profile loading failed for the following reason:
Runtime Error
Credentials in profile "normalize", target "prod" invalid: Runtime Error
Could not find adapter type sqlserver!

I'm running Airbyte 50.39 on Ubuntu 22
How can I make the CLI transform work so I can tweak it?
I've seen several discussions on similar topics in the old Discord discussions, but no real solution.

On a more meta-level, the tweak I need to make is to change the STRING imports from NVARCHAR(MAX) to NVARCHAR(1000) globally.
If there's a better way than tweaking the dbt casts, I'm open to it.

[marcosmarxm]

The default image used by custom transformation fishtownanalytics/dbt:1.0.0 doesn't support community adapters.
You must create a custom Docker image to install the mssql-adapter lib, you can get some inspiration on the custom docker used by Airbyte. Why this image won't work as a custom transformation? The entrypoints are different.

I think something like this works but I cant confirm.

FROM fishtownanalytics/dbt:1.0.0
RUN pip install dbt-sqlserver==1.0.0
ENTRYPOINT ["dbt"]

And yes, you need to update the macro folder to change the NVARCHAR(MAX) to NVARCHAR(1000).