{"id":454,"date":"2015-02-26T14:00:51","date_gmt":"2015-02-26T20:00:51","guid":{"rendered":"http:\/\/www.sqlfreelancer.com\/blog\/?p=454"},"modified":"2015-02-26T14:00:51","modified_gmt":"2015-02-26T20:00:51","slug":"sql-server-integration-services-connector-for-salesforce","status":"publish","type":"post","link":"https:\/\/www.sqlfreelancer.com\/blog\/sql-server-integration-services-connector-for-salesforce\/","title":{"rendered":"SQL Server Integration Services Connector for SalesForce"},"content":{"rendered":"

Problem:<\/p>\n

I have a list of contacts in my SQL Server database that I would like to import into Salesforce and keep Salesforce up to date as new contacts are entered daily into my database. I know there are a few different ways to accomplish this but I\u2019d like to use a SSIS package so I can automate.<\/p>\n

Solution:<\/p>\n

In my opinion, the best (and easiest) way to accomplish this goal is using SSIS packages and a component from CozyRoc called Salesforce Destination<\/a>. CozyRoc provides advanced SSIS libraries of custom tasks, components and scripts for handling Zip, GZip, BZip2, Tar archives, enhanced script reuse and secure Internet communications. I always go to CozyRoc when I need a custom task. In this tip I\u2019ll show you how to take a contact list from SQL Server and import it into SalesForce. I\u2019ll also show you how to schedule this on a daily basis.<\/p>\n

For this example, let\u2019s say I have a contact table in my SQL Server database like the following:<\/p>\n

\"SQL<\/a><\/p>\n

I need to import these records into my Contacts tab in SalesForce:<\/p>\n

\"SQL<\/a><\/p>\n

Create SSIS package<\/span><\/p>\n

First thing I\u2019ll need to do is download the CozyRoc SalesForce component. This download can be found here: http:\/\/www.cozyroc.com\/ssis\/salesforce-destination<\/a><\/p>\n

I\u2019ll download and install this component on the server or workstation that I plan on developing the package. Once installed, open SQL Server Data Tools and create a SSIS project:<\/p>\n

\"SQL<\/a><\/p>\n

First, let\u2019s create a connection to the SQL Server database that stores the contact information and the Salesforce destination:<\/p>\n

Right click in Connection Manager and create \u201cNew OLE DB Connection\u201d:<\/p>\n

\"SQL<\/a><\/p>\n

Type Server Name, credentials, and select database from drop down that stores the contact information. In this example, I\u2019m selecting my local server and Demo database:<\/p>\n

\"SQL<\/a><\/p>\n

Test Connection and click OK. Right click on the Connection Manager again and click \u201cNew Connection\u201d:<\/p>\n

\"SQL<\/a><\/p>\n

If the Salesforce Destination component was installed properly, you should see a connection called \u201cSFORCE\u201d:<\/p>\n

\"SQL<\/a><\/p>\n

Click Add. Type your UserName and Password + Security token. If you don\u2019t know your security token click here<\/a> to read more about resetting it.<\/p>\n

\"SQL<\/a><\/p>\n

Once you have entered your login credentials, make sure to test connection.<\/p>\n

You should now have two connections in Connection Managers:<\/p>\n

\"SQL<\/a><\/p>\n

Now to designing the package:<\/p>\n

From the Control Flow we will add our Data Flow Task and right click to configure.<\/p>\n

\"SQL<\/a><\/p>\n

Add the OLE DB source and SalesForce Destination tasks to the designer:<\/p>\n

\"SQL<\/a><\/p>\n

Configure OLE DB Source:<\/p>\n

\"SQL<\/a><\/p>\n

To configure the SalesForce Destination right click and edit. Choose the connection manager you created earlier:<\/p>\n

\"SQL<\/a><\/p>\n

Click the Component Properties tab and you\u2019ll notice a few options under Custom Properties:<\/p>\n

Action:<\/p>\n\n\n\n\n\n\n\n
Action<\/td>\nDescription<\/td>\n<\/tr>\n
Create<\/td>\nCreate a new record in the destination object<\/td>\n<\/tr>\n
Update<\/td>\nUpdate an existing record in the destination object. You must specify the appropriate object record identifier for the update to work.<\/td>\n<\/tr>\n
Delete<\/td>\nDelete an existing record from the destination object. You must specify the appropriate object record identifier for the delete to work.<\/td>\n<\/tr>\n
Upsert<\/td>\nUpdate and insert a record in the destination object. Selecting this value displays the dynamic parameter ExternalId<\/strong>.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Batch Size: Specify the number of rows to be sent as a batch. The maximum for regular mode is 200. The maximum for bulk-load is 10,000\\.<\/p>\n

Destination Object: Specify the destination Salesforce object from a dropdown where the data is to be loaded.<\/p>\n

ExternalID: Specify the external identifier field for updating the foreign key lookup with an external identifier.<\/p>\n

Mode:<\/p>\n\n\n\n\n\n\n
Value<\/td>\nDescription<\/td>\n<\/tr>\n
Regular<\/td>\nProcess the data in regular mode<\/td>\n<\/tr>\n
BulkData<\/td>\nProcess the data in bulk-load data mode<\/td>\n<\/tr>\n
BulkBinary<\/td>\nProcess the data in bulk-load binary mode<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\"SQL<\/a><\/p>\n

Since we are adding Contacts, we\u2019ll select Create (Action) and Contact from the DestinationObject option.<\/p>\n

Click the Column Mappings tab and make sure all the columns are mapped correctly (delete mapping between ID\u2019s for this example):<\/p>\n

\"SQL<\/a><\/p>\n

Run the package and if everything is setup correctly your Salesforce instance should new contacts in it:<\/p>\n

\"SQL<\/a>\"SQL<\/a><\/p>\n

Run this package as daily job<\/span><\/p>\n

To run this package as a daily job that will populate Salesforce hourly, daily, weekly, etc. we\u2019ll need to create a SQL Agent job and set a schedule.<\/p>\n

Next Steps:<\/p>\n