We are moving data from OLTP server into Data Warehouse server, utilizing SSIS to conduct this.
When writing queries from the OLTP Data Source, it it better to utilize?
1) Inline Embedded queries
3) Or Stored Procedures
In researching and testing - Embedded queries are great, however we want code placed in SSDT Database projects to build/compile. So if column name changes/deleted, we would know in Devops portion. If writing in SSIS Datasource 'Test abcd etc', SSIS will still build/compile. If writing in SQL Database project, 'select testabcd from dbo.exampletable', SSDT DB project will fail to build/compile.
I hear Functions are great, since it will display the metadata. However, Multi Statement table value functions are Slow, which team will require sometime.
Stored procedures I am reading have issues with creating metadata. Is this true ? Is there a workaround, heard its possible to create metadata in stored procedures.
Can someone let me know good option between three, or any other options?
4) * Did not want to incorporate views, since team wants to parameterize queries by source Create/Update Dates, if ever intending to reprocess data.