Data Transfer From SQL Server to CRM 2011 using CozyRoc SSIS+

1)Open Sql Server Data Tools
2)Create New Integration Services Project
3)Right Click Connection Manager -> Select New Connection Manager
    Select OLEDB Type
    Select Exiting Data Connection or Create new connection
    Rename 'DNS_DB.conmgr' (DNS sql database connection)

   

4)Right Click Connection Manager -> Select New Connection Manager
    Select OLEDB Type
    Select Exiting Data Connection or Create new connection
    Rename 'MSCRM.conmgr' (CRM sql database connection)

5)Right Click Connection Manager -> Select New Connection Manager
    Select DYNAMICS-CRM Type
    Select Edition -> CRM 2011 and Deployment -> Premise
    Enter Server, Crednetials, Select Organization:
    Press 'Test Connection' and 'Ok' button
    Rename 'CRM_Organization.conmgr'

  
6)Add Data Flow Task in Control Flow
7)Add OLE DB Source in Data Flow
      Dobule Click, Select 'DNS_DB_DEV' OLD DB, Select 'SQL Command'
      Enter 'SQL Command text:', Press 'Parse Query' and 'Ok' button

   

8)Add Lookup Plus
    The purpose of this tool is connecting reference table/entity value between sql server and crm (picklist or option set or reference entity).
    http://www.cozyroc.com/ssis/lookup
  
    For example, crm contains State Entity, it includes DNS_DB state id (let's assume dns_state_id) , Contact Entity referenced State Entity id. When you insert employee record from DNS_DB to CRM_DB Contact entity, you need to find out correct state id entity, here Lookup Plus will help you to match the both entity.
  
    Lookup Plus will also help you to match PickList and OptionSet also.
  
    Dobule Click, Select Input Column (From DNS_DB) -> StateID
    Dobule Click Reference, Select Connection (MS CRM Database), Check SQL Query, enter sql query
        select dns_statename, dns_stateid from dns_state, Press 'Tick' green button
        Select Key Column -> dns_statename, Value Column -> dns_stateid
    Result Column -> dns_stateid
    On NULL = > 00000000-0000-0000-0000-000000000000  (if entity other wise 0)
    No Match  => 11111111-1111-1111-1111-111111111111 (if entity other wise -1)

 

  

9-1) only Insert Step (Create)
    Add Dynamics CRM Destination
        Double Click,
        Select 'Connection Managers' tab -> Select 'Connection Manager' -> CRM_Organization
        Select 'Component Properties' tab -> Select 'Action' -> 'Create', Select 'Destination Entity' -> contact
        Select 'Coumn Mappings' tab , match Input Column (DNS_DB) to Destination Column (CRM DB)
        Lookup Plus Input Column 'dns_stateid' match to 'dns_stateid' CRM Desitnation Column
        Press OK.  

9-2) Insert & Update Step (Upsert)
    Go to CRM Settings -> System -> Data Management -> Click 'Duplicate Detection Rules'
        Press 'New' button, Enter Name, Select Base Record Type and Matching Record Type (contact)
        Select Field (dns_employeeid), Criteria 'Extact Match' and save
    Add Dynamics CRM Destination
        Double Click,
        Select 'Connection Managers' tab -> Select 'Connection Manager' -> CRM_Organization
        Select 'Component Properties' tab -> Select 'Action' -> 'Upsert', Select 'Destination Entity' -> contact
        Select 'Coumn Mappings' tab , match Input Column (DNS_DB) to Destination Column (CRM DB)
        Press OK.  

9-3) Insert & Update Step (Create & Update)
    Add Lookup Plus
        Dobule Click, Select Input Column (From DNS_DB) -> EmployeeID
        Dobule Click Reference, Select Connection (MS CRM Database), Check SQL Query, enter sql query
            select count(contactid) CRMEmployee, dns_employeeid from contact group by dns_employeeid , Press 'Tick' green button
            Select Key Column -> dns_employeeid, Value Column -> CRMEmployee
        Result Column -> CRMEmployee
        On NULL = > 0 and No Match  => -1
      
        Select Input Column (From DNS_DB) -> EmployeeID
        Dobule Click Reference, Select Connection (MS CRM Database), Check SQL Query, enter sql query
            select  contactid, dns_employeeid  from contact, Press 'Tick' green button
            Select Key Column -> dns_employeeid, Value Column -> contactid
        Result Column -> contactid
        On NULL = > 00000000-0000-0000-0000-000000000000  (if entity other wise 0)
        No Match  => 11111111-1111-1111-1111-111111111111 (if entity other wise -1)
   

    Add Conditional Split
        Double Click, Order, Output Name, Condition
                1    Create        CRMEmployee <=0
                2    Update        CRMEmployee >0

   

    Add Dynamics CRM Destination  (Create)
        Double Click,
        Select 'Connection Managers' tab -> Select 'Connection Manager' -> CRM_Organization
        Select 'Component Properties' tab -> Select 'Action' -> 'Create', Select 'Destination Entity' -> contact
        Select 'Coumn Mappings' tab , match Input Column (DNS_DB) to Destination Column (CRM DB)
        Press OK.
      
    Add Dynamics CRM Destination  (Update)
        Double Click,
        Select 'Connection Managers' tab -> Select 'Connection Manager' -> CRM_Organization
        Select 'Component Properties' tab -> Select 'Action' -> 'Update', Select 'Destination Entity' -> contact
        Select 'Coumn Mappings' tab , match Input Column (DNS_DB) to Destination Column (CRM DB), match
            contactid <-> contactid
        Press OK.      

  

10) Execute Package