I am trying to use a production SQL Server database to send relevant data into Salesforce to generate reports. Some of the data I would like to use however is not consolidated into a single database table and is spread across a few tables. Is it possible to populate fields of a Salesforce object with data from multiple external database tables? Please let me know if there is a better way that I should be going about using my external database to create reports in Salesforce.
You can write a stored procedure at sql server to retrieve the data from multiple tables and export that dataset into csv format, then put the csv in the network path to import the csv into Salesforce object via CLI.
You can also manually load csv through data loader. By the way, in both the scenarios you need to define proper mapping between csv columns with Salesforce fields of the object to which you are importing.
To be honest, it depends on what you feel most comfortable with.
Like @santanuboral said, a Stored procedure could work for you, but you would need to couple it with a script in bash/PowerShell to then pick up the csv and upload it to Salesforce via the Dataloader CLI.
In my case, I would be more comfortable writing a small app in C#, which would pick up the data from the SQL Server and directly pump it through the Bulk API. Also, a script can also do the job just the same, just harder to test, IMO.
The architecture of such an app would be fairly simple, following a normal ETL structure: you would have a set of classes to read from the DB in a paginated way, a small engine with all the different transformations you require which would be applied before loading the data, and lastly another set of classes to connect to Salesforce via OAuth and load the records to the Bulk API.
Once you have that app developed simply schedule it with any standard OS tools in whatever server you want. Schedule it to run every X hours/minutes and that would make sure you have the latest data in Sales Cloud.
Do take into account certain things regarding the transformations:
Date formats don't necessarily match. If you end up using the Bulk API you're going to need to use the
I recommend you to double quote everything and escape quotes, it's usually a headache otherwise.
Take into account that if you need to calculate fields for reporting, it's much easier to do it in any external processes to Salesforce (unless it's a simple sum or similar). Complicated calculations will lead you to Apex/Triggers, and that adds unnecessary complexity to the process.