none
SSIS Package or CLR Function RRS feed

  • Question

  • We have a table which contains the basic data of our customers (e.g., First Name, Last Name, Identity Code and so fourth). The table has approximately 100,000 rows.

    Now, we need to call several web services to get some data for each customer and then insert the received data from web services into some other tables in the database.

    I'm considering two approaches to accomplish this task:

    1. Declaring several table-valued CLR functions and call web services through some .Net managed code, and then use these CLR functions in several stored procedures to insert the received data into several tables.

    2. The second approach which I'm considering is using an SSIS Package. For example, I can create some Script Tasks inside the SSIS Package and call web services, and then insert the received data into some tables through ADO.Net or some other providers.

    Which approach is the best from your opinion? and why?

    Saturday, February 9, 2019 7:30 AM

All replies

  • You will find it easier to follow the SSIS approach. The CLR functions have a problem: by default you will not be able to call the web services. It's not a mere problem of granting the "external access" permission, you also have to bring-in the WCF libraries which by default it cannot access. I remember doing a web search in an attempt to find the trick to make it work, and from what I recall it was more difficult than it should be.

    On the other hand, using SSIS should be straightforward; all the required tasks are available, or you may add a Script task if you need to do something that the standard tasks cannot accomplish. And it can do things such as for instance restarting from the point where it was interrupted in case of an error (if you enable the checkpoints) or creating detailed logs, which would take you some effort to do with the CLR approach.

    Saturday, February 9, 2019 12:58 PM
    Moderator
  • Hi

    Is your problem solved? If so, please post "Mark as answer" to the appropriate answer, so that it will help other members to find the solution quickly if they face a similar issue.
    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Tuesday, February 19, 2019 8:11 AM
    Moderator