Extracting Data from Salesforce using SSIS 18. January 2017 matthow Salesforce, SSIS (1) This blog looks at a couple of methods of extracting data from Salesforce and gives a demo of how to output tables and queries into flat files. Salesforce is HUGE! How do I find what I’m looking for? Thankfully SFDC make it easy for us to find the tables that custom objects as they are suffixed with “__c” (e.g. “Assessment_Questions__c”). This convention carries through to Column level as well, meaning that when a Salesforce standard table is used, such as SFDCs Event table, any custom columns are also suffixed with “__c”. Can I use SQL? Salesforce utilises a modified version of SQL called SOQL (Salesforce Object Query Language). There are some Keyword similarities such as starting a query with “SELECT” and calling a table with “FROM” but if you want to limit a query result to a fixed number of rows you need to add “LIMIT n” to the end of the query string, not the start! Additionally, SFDC provide some handy functions to make SOQL slightly easier on us developers. I have listed a few that I used below but a full documentation site is available here - https://help.salesforce.com/articleView?id=customize_functions.htm&type=0 Function Usage Example LAST_YEAR Provides a data range from Jan 1st of the previous year to Dec 31st of the previous year. SELECT AccountId FROM Customers WHERE CreatedDate > LAST_YEAR LAST_N_MONTHS: n Provides a data range from the last day of the previous month and continues for the past n months. SELECT Id FROM Account WHERE CreatedDate > LAST_N_MONTHS:12 BLANKVALUE Similar to ISNULL. Will provide a substitute value if the field is null SELECT BLANKVALUE(AddressLine2,”N/A”) FROM… How do I connect to Salesforce? In order to access the Salesforce system, you will need a username, password and security token. From my research there are two viable methods for connecting to Salesforce using SSIS which are using an API component or using a linked server. Both have their pros and cons but generally I have found the API route to be more efficient. There are a number of 3rd Party providers around and I have listed a few of these below. Provider Comments Link C – Data (API) http://www.cdata.com/drivers/salesforce/ssis/ Task Factory (API) Forces you to down load all 50 components. Can use SSIS variables and SOQL http://pragmaticworks.com/Products/Task-Factory/Free-Trial Kingsway Soft (API) Simple interface and can use SSIS variables. Auto generates SOQL. Cant edit error output from main component http://www.kingswaysoft.com/products/ssis-integration-toolkit-for-salesforce/download DB – Amp (Linked Server) Allows you to use OLE DB Connection manager http://forceamp.com/trial.htm You can use these steps below to access SFDC and start retrieving data. 1. Download an SFDC SSIS component. For this demo I have used the Kingsway Soft component available from the link above. 2. Run through the install wizard and once complete, open Visual Studio. 3. Create a new SSIS project and add a Data Flow Task. Rename this to Kingsway Soft Demo. 4. Go into the data flow and right click in the toolbox, hit “Refresh Toolbox”. Now you should see 2 new components called “Salesforce Destination” and “Salesforce Source”. 5. Drag a Salesforce Source onto the design surface and double click to edit the properties. Drop down in “Connection Manager” and select “New”. Fill out the details required using the below connection info. Username: firstname.lastname@example.org Password: S4lesforce Security Token: oKar72dhKcBAWlq0i4M0RF7ua 6. Test the connection, and click “OK” once it has connected successfully. From the component properties screen use the Source Type to select “Object Query”. Add this SOQL statement into the text editor below. Click “OK”. TIP!! – If you select “Object” from the Source Type drop down and choose your table you can then switch the source type back to “ObjectQuery” and the full SOQL statement to retrieve all columns will be generated automatically. 7. Add a flat file destination and configure the connection manager. Enable the data viewer between your SFDC component and the flat file destination and preview the data that is being extracted from SFDC. 8. From here can then repeat the process to build up a package that will bring down all the files that you might need for your solution. There are additional tools such as Salesforce Workbench that will allow developers greater access to the underlying solution but these will be covered in a different blog. If this solution does or doesn’t work for you then let me know!