SSIS Dynamics CRM Connection Manager can be used with Dynamics CRM Source, Dynamics CRM Destination, to read/write Microsoft Dynamics CRM data.

Download Read more

Content

Video Tutorial

Coming soon...

Step-By-Step

In this tutorial we will learn how to create Dynamic CRM Connection. You can create multiple connection(Used with Dynamics CRM Source, Dynamics CRM Destination).
  1. In order to connect to DynamicsCRM Storage from SSIS you will need Credentials. You can create account click here. and get credentials.
  2. Now, You need to Download and Install SSIS ZappySys PowerPack.
  3. Once you finished above steps, Open Visual Studio and Create New SSIS Package Project.
  4. Right click in the Connection Manager panel and click on New Connection.
    SSIS Create New Connection
  5. Select ZS-DYNAMICS-CRM Connection Manager from the Connection Managers list and Click on Add Button.
    SSIS DynamicsCRM Connection Manager - ADD
  6. In the connection manager UI, set Authentication type to CRM Online (Office 365), enter user name and password. Select organization.
    SSIS Dynamics CRM Connection Manager UI (Used with Dynamics CRM Source, Dynamics CRM Destination)
  7. Click on Test Connection to check configure correct or not.
  8. Click on OK button to save DynamicsCRM Connection Manager configure setting UI.
  9. Once you finished, you will see DynamicsCRM connection in Connection Manager panel.
    SSIS DynamicsCRM Connection Manager - Success

Properties

Property Name Description
AuthType Authentication Type

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
OnlineFederation [0] Online Federation (for CRM Online 365) *** DEPRECATED (Use OAuth instead) ***
Federation [1] Internet-facing deployment - IFD (On-Premises or Hosted)
ActiveDirectory [2] Active Directory (On-Premises)
OAuth [3] OAuth 2.0 Client Credentials (Application Permissions)
OAuthDelegated [4] OAuth 2.0 Delegated Permissions (Interactive User Sign-In)
OrganizationId Prefix for Service Url
ServiceUrl Organization Service Url
DiscoveryUrl
Timeout Request timeout in seconds
UserName
Password
TokenUrl Custom token URL override (e.g. US GCC: https://login.microsoftonline.us/<tenant>/oauth2/v2.0/token). Applies to OAuth auth types.
ClientId Azure AD App Id (Client Id). Required for OAuth Client Credentials and OAuth Delegated auth types. See https://zappysys.com/links/?id=10116
ClientSecret Client Secret from Azure AD app registration. Required for OAuth Client Credentials and OAuth Delegated auth types. See https://zappysys.com/links/?id=10116
AuthUrl Custom authorize URL override for OAuth Delegated flow (optional).
RedirectUrl Redirect URI for OAuth Delegated flow. Must match Azure app registration exactly. Recommended: https://zappysys.com/oauth
Scopes OAuth scopes for delegated access. Default is {org-url}/.default offline_access openid profile. App must have Delegated Dynamics CRM user_impersonation permission.
RefreshToken Refresh token from Generate Token on connection UI (OAuth Delegated only). Runtime token refresh uses this value when RefreshTokenFilePath is empty.
RefreshTokenFilePath File path for rolling refresh token storage (OAuth Delegated). Microsoft/Dataverse may issue a new refresh token on each access-token renewal; runtime updates this file so scheduled jobs keep working. Set via Generate Token or Advanced tab.
AccessToken
EnablePkceCodeVerifier Enables PKCE feature (RFC 7636) which passes code_challenge / code_verifier for authorization and token call (e.g. get refresh_token call)
ExtraAttributesForAuthRequest Extra query parameters for /authorize request (e.g. login_hint=user@company.com). Overrides Prompt dropdown if prompt= is included here.
ExtraAttributesForTokenRequest Extra parameters for token request (usually empty for delegated OAuth).
ConnectionString
RetryMode Error retry mode. Default is do not retry (None).

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
None [0] Do not retry
RetryAny [1] Retry for any web, io, timeout errors
RetryWhenStatusCodeMatch [2] Retry for throttling or matching HTTP status code(s)  (e.g. 429|501)
RetryWhenMessageMatch [3] Retry if error message contains specified string(s) (e.g. nessage1|message2)
RetryCountMax Maximum number of retry attempts to try before throwing error. This option is ignored if RetryMode is set to None (Do not retry).
RetryMessageList This option retries on a  static retry_wait_time you entered using this formula. new_retry_wait_time = entered retry_wait_time * current_retry_attempt_counter (after each failed attempt attempt_counter is increased). So for example if your retry wait time is set to 3000ms (3sec) then with this option turned on you will see wait as follow: On 1st attempt = 3000*1 wait time= 3000ms, On 2nd attempt 3000*2=6000ms wait time, On 3rd attempt 3000*3=9000ms wait time... so on. This option is ignored if RetryMode is set to None (Do not retry).
RetryMultiplyWaitTime This option increases wait time on each retry attempt using this formula. new_retry_wait_time = specified retry_wait_time * retry_attempt_counter (after each failed attempt retry_attempt_counter is increased by one). So for example if your retry wait time is set to 3000ms (3sec) then with this option turned on you will see wait as follow: On 1st attempt = 3000*1= 3000ms wait time, On 2nd attempt 3000*2=6000ms wait time, On 3rd attempt 3000*3=9000ms wait time... so on until max attempts reached. This option is ignored if RetryMode is set to None (Do not retry).
RetryStatusCodeList Specifies a list of http status codes on which you like to retry. This option can be used if you want to narrow down retry attempts on a very specific list of http status codes separated by pipe rather than any random web exception. Use pipe to separate values e.g. 429|500 so on to retry for only these 3 status codes rather than any web exceptions. Refer to your API documentations to findout which errors you can retry on. This option is ignored if RetryMode is set to None (Do not retry).
RetryWaitTimeMs This value specifies how much time to wait (in milliseconds) before trying again on error(s). This option is ignored if RetryMode is set to None (Do not retry).
StreamReadWriteTimeoutMs HTTP stream read/write timeout in milliseconds. This controls how long to wait while reading or writing data on the network stream before timing out. Set to 0 to use the .NET default read/write timeout (typically 5-10 minutes).
UseProxy Enable custom proxy settings (If this is not set then system default proxy will be used. To disable proxy totally uncheck this option and check DoNotUseDefaultProxy option if available)
ProxyUrl Web URL of Proxy server (including port  if necessary). [e.g. http://myproxyserver:8080/]
DoNotUseDefaultProxy Do not use system default proxy (This setting is ignored if UseProxy=true)
ProxyUseCreds Enable passing userid and password to proxy server
ProxyPassword Proxy password
ProxyUserName Proxy username

Setting UI

SSIS DynamicsCRM Connection - Setting UI

See Also

References