topic Re: Can Compose call SqlServer stored procedures in a transform in Qlik Compose

Can Compose call SqlServer stored procedures in a transform

RonFusionHSLLC — Fri, 14 Mar 2025 20:49:07 GMT

We have 1000's of sp's that are used in our current ETL processing.

These sp's range from simple get a column from a row, to huge calculations not easily/possible to be replicated in Compose's expression builder.

Some of the sp's can be converted to a 'pre-run' sp that could populate lookup tables, which seem easy to integrate.

Can Compose call a sp in a SqlServer database, not necessarily the same database of the ODS or DW...ie. if the dw is in xxxx_DW.dbo and the sp is in xxxx_SP.dbo; both databases on the same server; if so how?

Ron

Re: Can Compose call SqlServer stored procedures in a transform

aj96 — Sun, 02 Jan 2022 20:09:53 GMT

I think you can use EXEC in post or pre etl. That should work.

Re: Can Compose call SqlServer stored procedures in a transform

aj96 — Sun, 02 Jan 2022 20:15:07 GMT

Also the single table is good option if you have logic that would not be converted in views or expression. Also a good advantage of that is in cdc you will only be operating onthe delta values but as I'm not sure what the requirement is, i can't say much about the approach.

Re: Can Compose call SqlServer stored procedures in a transform

TimGarrod — Tue, 04 Jan 2022 21:35:46 GMT

You cannot call an SP in a mapping - but can as you and AJ suggest call it in

a Pre-Load step.
- Recommended approach would be to have the proc populate a table - which is subsequently used in a mapping
a multi-table / single-table ETL step
- Note that if no records are loaded to the TSTG table then additional steps are often skipped by Compose.
A Post-Load step
- It is not recommended to INSERT data into HUBS / SATELLITES in custom steps due to all the RFI work that is automated by Compose, however updating Type 1 values in HUB's is fine - just remember to update the RUNNO_UPDATE column or changes may not be reflected in data mart processing.

Another option would be to convert the SP into a Table-Valued function which generates an output. You should then be able to use the TVF in a mapping as a query based source.