I want to update a Salesforce Object from a Stored Procedure inside Microsoft SQL Server. Is this possible?

I have use case that will get data from 'X' database .From 'X' db SQL server will get data into raw table and it will apply some business rules and stores the data into localtable. Now by using these local user table i need to compare with data in the Salesforce user object. if there is any change of data in user object then i need to update in salesforce. this should be done only to active users. for this purpose i need to construct a staging table in sql and by using this staging table in sql i need to update in salesforce. my approach is i need to create a view and should get all columns from local user table and then by using this view i need to create a stored proc and then staging table . from staging table i need to use update stored proc . so it will updated in cloud. please let me know whether it is correct or not or any suggestions plz let me know any links where i can find some pseudo code

  • I'm having difficulty following the question as written. As far as I can tell you want to compare data in a SQL Server table against a Salesforce object, and update the Salesforce object if different? Is there any reason not to just use the Salesforce Data Loader in 'Update' mode? If there are changes then the tool will manage. – user12646 Jan 27 '18 at 20:52
  • yes i want to compare and update in salesforce. i am not doing manually to upload data from CSV. or may be there are automation tools to upload. but here they are using this SQL server only. no logic is done in salesforce. everything it should happen in SQL server only – sarah Jan 27 '18 at 20:59
  • Do you have any other ETL tools do you have for this function (Informatica, etc)? Salesforce Data Loader can be used with SQL Server, but requires extra configuration. You can also export the SQL Server data to CSV with SQL Server Integration Services (SSIS). – user12646 Jan 27 '18 at 21:07
  • I think i need to put up a control' M 'job to make automate – sarah Jan 27 '18 at 21:15
  • ControlM will schedule a process to run, but as far as I know, will not transfer any data between SQL Server and Salesforce. I suggest using ControlM to schedule a SSIS job to export your data as CSV, and then use ControlM to schedule Salesforce Data Loader to update that data into Salesforce. – user12646 Jan 27 '18 at 21:19

It is technically possible to update Salesforce directly from a SQL Server stored procedure, however I'd highly discourage you from trying! It is not the way Stored Procedures should be used.

If you really want to do this, then you check this question about called a web service from SQL Server: https://stackoverflow.com/questions/33449/can-you-call-a-webservice-from-tsql-code . In theory you can call a Salesforce 'Update' REST method from the Stored Procedure.

To achieve your scenario I would rather use Salesforce Data Loader to run updates every night directly from your SQL Server table.

Not the answer you're looking for? Browse other questions tagged or ask your own question.