Can Compose call SqlServer stored procedures in a transform

[RonFusionHSLLC]

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

[aj96]

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

[aj96]

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. 

[TimGarrod · Accepted solution]

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.