danieljarolim/SSIS SalesForce SOAP Integration.md

## SSIS SalesForce SOAP Integration.md

      
    Raw
  

              SSIS SalesForce SOAP Integration.md
            
          
    SSIS SalesForce Integration using SOAP

Integration with SalesForce without using a custom component requires use of a Script Component as the data source but is fairly straightforward. If you are not limited with an old version of SSIS and have access to .NET newer than 3.5 then you should try using the Salesforce REST API documented here: https://gist.github.com/danieljarolim/1b6e2c2575f17d8a477f3135d36f99c9
Let the wsdl utility (part of the .NET 3.5 SDK) do the hard work of generating all the required classes with correct field names and data types from the SalesForce WSDL.
Use SalesForce documented login and data extract query patterns to get the data.
Note: using the web service component of SSIS will not work. Two calls are required just to log in.
Step 0 Install TLS 1.1 fix for .NET 3.5 framework


For Windows Server 2008 R2 SP1 .NET 3.5 SP1 this is located in https://support.microsoft.com/en-us/kb/3154518 but versions for 2012 exist.
Update the registry as indicated in the above KB to make .NET use windows TLS settings rather than .NET builtin.
Update the registry to enable TLS 1.1 and 1.2 in windows (they are disabled by default) (below reg fix is from: https://technet.microsoft.com/en-us/library/dn786418(v=ws.11).aspx)
Create "TLS 1.1" and "TLS 1.2" Keys under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols
Create a "Client" key under TLS 1.1 and under TLS 1.2
In each "Client" key create DWORD "DisabledByDefault" and leave it as 0

Step 1 Install v7.0 .NET SDK


Download and install .NET 3.5 SDK (v7.0 SDK) to get access to the wsdl utility.

Step 2 Download Enterprise WSDL Schema from SalesForce


In SalesForce go to Setup -> Develop -> API -> Generate Enterprise WSDL and save as enterprise.wsdl

Step 3 Generate cs file from WSDL


Launch microsoft Windows SDK v7.0 -> CMD Shell
run wsdl against enterprise.wsdl: wsdl /language:CS /out:SalesForceWebService.cs enterprise.wsdl
This create a SalesForceWebService.cs file in the same directory.

Step 4 Create the Script component and extract data


Add the script component as source into a data flow task. Open script editor.
copy SalesForceWebService.cs from windows explorer and paste straight into Project Explorer / Solution (right hand side of window)
Double click on SalesForceWebService.cs in Project Exporer to check for missing references in project.
Add any missing references to References in Project Explorer. (They are shown as underlined errors in editor). There will be some missing and cause errors if not added.

Update the source script using the standard template below to read from SalesForce.

SalesForce connection pattern in PreExecute
SalesForce Query with QueryMore pattern in CreateNewOutputRows

/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using System.Net;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    SforceService SFService1;
    DataSet TestDataSet1;

    public override void PreExecute()
    {
        base.PreExecute();
        /*
          Add your code here for preprocessing or remove if not needed
        */
        SFService1 = new SforceService();
        // Proxy settings if needed, by default uses system proxy settings (ie autodetect)
        //SFService1.Proxy = WebRequest.GetSystemWebProxy(); // or new WebProxy("proxy",3128");
        //SFService1.Proxy.Credentials = new NetworkCredential("UserName", "Password","OptionalDomain");

        // Salesforce login workflow
        SFService1.SessionHeaderValue = new SessionHeader();
        LoginResult SFLR = SFService1.login("name@company.com", "PasswordTOKEN");
        SFService1.Url = SFLR.serverUrl;
        SFService1.SessionHeaderValue.sessionId = SFLR.sessionId;

    }

    public override void PostExecute()
    {
        base.PostExecute();
        /*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */
    }

    public override void CreateNewOutputRows()
    {
        /*
          Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
          For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
        */
        
        // eg Get Current User Info - classes are defined in the generated cs file.
        GetUserInfoResult UIR;
        UIR = SFService1.getUserInfo();

        // eg Get Contacts
        // Results restricted to 1000 per batch. Use this pattern to get all data until complete.
        QueryResult ContactQR;
        // Get First Batch of 1000
        ContactQR = SFService1.query("select c.Description, c.LastName, c.LastModifiedDate from Contact c");
        Boolean QueryDone = false;
        while (!QueryDone)
        {
            // Loop over 1000 records and create row for each record
            foreach (Contact contact in ContactQR.records)
            {
                // Add New Row
                Output0Buffer.AddRow();
                // The definition of each attribute of a contact is in the generated cs file.
                // Make sure String1, String2 etc are correctly created in the output columns of the script component.
                Output0Buffer.String1 = Convert.ToString(contact.Description);
                Output0Buffer.String2 = Convert.ToString(contact.LastName);
                Output0Buffer.DateTime = Convert.ToDateTime(contact.LastModifiedDate);
            }

            // Check if last query indicated there is still data and queryMore
            if (!ContactQR.done)
                ContactQR = SFService1.queryMore(ContactQR.queryLocator);
            else
                QueryDone = true;
        }

    }

}