Custom SQL template for odbc external connection

[appuputti]

Hi guys,

Have anybody tried something like this before? I have custom external datasource that i was able to connect to adobe and it works fine if i just pull some data and immediately change the dimension to use Adobe internal database.

However i have use cases where i cannot do it in the beginning and any activity in Adobe that uses internal templates to create the query will fail as the syntax is slightly different.

First time i got this error "Unable to load document of identifier 'xtk:crdb_<mydb>.xsl' and type 'xtk:xslt'." So what i did is that i created following .xsl files for this case

- crdb_<mydb>.xsl

- updb_<mydb>.xsl

- tools_<mydb>.xsl

The crdb.xsl is a reference to load and call tools_.xsl which has some template for creating and inserting to table. My problem is that no matter what i change in these .xsl files, the query submitted by Adobe doesn't change.

Is there some other layer of how these templates are stored? What does updb do since it is not called anywhere but the name would imply that it is update database?

The connection i am trying to establish is standard ODBC but there is some syntax difference compared to what Adobe campaign does.

Hitting a bit of dead end here.

[Jonathon_wodnicki]

Hi,

Are you making your own FDA connector?

Thanks,

-Jon

[appuputti]

Hi Jon,

Yes exactly. The connection is Spark SQL through ODBC connection. I copied and renamed the hive xsl templates but there is a slight syntax difference that i would need to modify.

Thanks,

Ari

[Jonathon_wodnicki]

Hi,

That's pretty cool. You can also try just proxying the db through SQL Server or Oracle if you're using those.

Thanks,

-Jon

[appuputti]

Hi Jon,

Yeah, i could. However the difference here is that Spark SQL doesn't support column list insert syntax that most 'standard' SQL languages use, E.G: INSERT INTO <tablename> (column_list) SELECT stufff FROM <tablename> so I need custom template to construct the query here.

Thanks,

Ari

[Jonathon_wodnicki · Accepted solution]

Hi,

It should all be transparent to Campaign, see this page for SQL Server:

• Spark Connector with Azure SQL Database and SQL Server | Microsoft Docs

Thanks,

-Jon