I'm very new to Dell Boomi and we are pulling data from our ERP system, which uses SQL Server as the database, to load into a cloud application. Our different environments have different schemas for the tables. For example our development database uses one schema, our test environment uses another schema, and production has a unique schema as well. I was instructed to keep the SQL code inside Dell Boomi and not make sure of stored procedures. I can make use of some utility functions we have written in SQL Server but they wanted the cord logic to be one place. To keep the SQL statements from having to change depending on the environment, I used the following technique:
- Created a SQL Server login for each environment using SQL Server authentication
- Created a database user for the each environments database tied to the login
- Set the default schema for the database user to be the appropriate schema for the environment. For example DVDATA for development, TSDATA for test, etc).
This works great for the tables but unfortunately when calling a scalar function in SQL Server you have to supply at least two parts; schema and function name. In this case the function in question just converts the date from a specialized version of a Julian date to a standard formatted date. I decided to just SELECT the field as is in the specialized Julian format and create a Groovy script function in my Dell Boomi map which duplicates the logic in SQL Server scalar function to convert the date to a standard date format.
If anyone has other suggestions or recommendations, they are welcome and appreciated.