locked
Executing SSIS Packages RRS feed

  • Question

  • Hi,

    i have a task to update the CRM database based on matching records found on a spreadsheet extracted from a different source every morning.

    My approach to solve this was to use an SSIS package and update matching records and write back to the CRM database .. After reading around, some sources say its not advisable to update the CRM database and plugins should be used to update it ..

    Is anyone able to shed the light on this please as i do not know much about plugins.  Whats the best approach to updating a CRM database? .. Links and examples would be helpful please

    Thanks for your response


    Every day i learn something new.

    Tuesday, October 7, 2014 7:36 PM

Answers

  • Hi,

    I think SSIS should be able to do the end to end process you are after. SSIS is a good approach for this kind of tasks (ETL).

    Using SSIS, you can read the source (spreadsheets), do some sort of transform logic then write the data to your Target (CRM).

    Obviously, its not supported to write directly to CRM db but you can use the Script component in SSIS to do the Microsoft approved way. The script component allows you to use the CRM SDK and its Web Services to Create, Update or Delete data in CRM.

    Here is a good step by step article in using the Script Component. 

    http://microsoft-ssis.blogspot.com.au/2014/01/insert-update-and-delete-records-in-crm.html

    Good Luck.


    Eric UNG [Senior Analyst Programmer :: Sydney, Australia]


    • Edited by Eric Ung Wednesday, October 8, 2014 9:31 AM
    • Marked as answer by CRM_Beginner Wednesday, October 8, 2014 10:22 AM
    Wednesday, October 8, 2014 9:29 AM
  • Hello,

    the best practice would be to write a c#windows service that reads the

    excel and write the info into CRM using organization service. Or you could also call the same organization service from ssis.

    no code approach would be to use the ssis integration package from a third party called kingswaysoft. The package can take data from multiple sources and can upload data in batch. It is free to use the software from visual studio. And written by a Microsoft MVP.

    regards

    Jithesh

    • Marked as answer by CRM_Beginner Wednesday, October 8, 2014 10:22 AM
    Tuesday, October 7, 2014 7:48 PM
  • You never want to update the CRM database directly.  You will break things.  However, Dynamics CRM has a robust web service that gives you just as much functionality.

    From an SSIS perspective, I will link you to a concept called Custom Actions: http://msdynamicscrmblog.wordpress.com/2013/11/07/actions-in-dynamics-crm-2013/

    That should be relatively familiar in concept.


    The postings on this site are solely my own and do not represent or constitute Hitachi Solutions' positions, views, strategies or opinions.

    • Marked as answer by CRM_Beginner Wednesday, October 8, 2014 10:22 AM
    Wednesday, October 8, 2014 12:46 AM

All replies

  • Hello,

    the best practice would be to write a c#windows service that reads the

    excel and write the info into CRM using organization service. Or you could also call the same organization service from ssis.

    no code approach would be to use the ssis integration package from a third party called kingswaysoft. The package can take data from multiple sources and can upload data in batch. It is free to use the software from visual studio. And written by a Microsoft MVP.

    regards

    Jithesh

    • Marked as answer by CRM_Beginner Wednesday, October 8, 2014 10:22 AM
    Tuesday, October 7, 2014 7:48 PM
  • You never want to update the CRM database directly.  You will break things.  However, Dynamics CRM has a robust web service that gives you just as much functionality.

    From an SSIS perspective, I will link you to a concept called Custom Actions: http://msdynamicscrmblog.wordpress.com/2013/11/07/actions-in-dynamics-crm-2013/

    That should be relatively familiar in concept.


    The postings on this site are solely my own and do not represent or constitute Hitachi Solutions' positions, views, strategies or opinions.

    • Marked as answer by CRM_Beginner Wednesday, October 8, 2014 10:22 AM
    Wednesday, October 8, 2014 12:46 AM
  • Thanks a lot for your response ! Excuse me if i am asking a dumm question, what is the difference of using a Web Service or an SSIS package? Wont both update the database either way?

    Any link or examples would be appreciated !

    Thanks


    Every day i learn something new.

    Wednesday, October 8, 2014 8:57 AM
  • Hi,

    I think SSIS should be able to do the end to end process you are after. SSIS is a good approach for this kind of tasks (ETL).

    Using SSIS, you can read the source (spreadsheets), do some sort of transform logic then write the data to your Target (CRM).

    Obviously, its not supported to write directly to CRM db but you can use the Script component in SSIS to do the Microsoft approved way. The script component allows you to use the CRM SDK and its Web Services to Create, Update or Delete data in CRM.

    Here is a good step by step article in using the Script Component. 

    http://microsoft-ssis.blogspot.com.au/2014/01/insert-update-and-delete-records-in-crm.html

    Good Luck.


    Eric UNG [Senior Analyst Programmer :: Sydney, Australia]


    • Edited by Eric Ung Wednesday, October 8, 2014 9:31 AM
    • Marked as answer by CRM_Beginner Wednesday, October 8, 2014 10:22 AM
    Wednesday, October 8, 2014 9:29 AM
  • Thank you all. So much appreciated !

    Every day i learn something new.

    Wednesday, October 8, 2014 10:23 AM
  • Hi,

    You may also want to check COZYROC SSIS+ library. It includes Dynamics CRM adapters which are free for testing and development from Visual Studio. You can even do one-time migration completely free. No programming skills are required.


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Friday, May 8, 2015 9:31 PM