SSIS package with Webservice as destination
I have a table is SQL server database A that is my source. I have another database B which is accessed via webservice call.(its a CRM server basically). My intention is to transfer data from A to B while B is accessible only via web service. I need to update existing one and create the missing one. Currently I am using script component, and on every insertion of a row, i call the webservice to check if the record exist or not. If it exist I update it else create it using webservice call itself. All this happen in Input0_ProcessInputRow(Input0Buffer Row) function. Now this method is making 2n webserive call which is making the performance very slow. I want to optimize the approach. Is there a way where I can retrieve whole set of rows in source table in preexecute(), filter it and store it in a List. This way, i just need to check the list a perform update ro create accordingly preventing my webservice call. Any suggestion to optimize this or even some better approach? Its actually a CRM server and I am trying to update and create contacts in CRM sync with a database.
October 25th, 2010 5:49pm
Hi prateek.sri, Based on your description, you want to retrieve the result set firstly, and then filter it and store it. After that, update the destination using a script task based on the list. If I have misunderstood, please don't hesitate to let me know. In order to return the whole result set, we can use 'Execute SQL Task' in the control flow instead of using source task in a Data Flow Task(DFT). We can save the result set in a Object variable. Then, we can use a script task to get the expected list from the variable. Finally, please call the web service to update the destination. Add a variable to the package. The type is object. Add a 'Execute SQL Task' to the control flow. Edit the task, set the 'ResultSet' to be 'Full result set'. In the editor, go to 'Result Set' tab, click 'Add' to map the resetset to a varaible. The variable is the one we defined in step1. Click 'OK' to apply. Now, in the followed Script Task, read a data set from the variable. Once we get the list, update or insert the data to the destination. For your reference, here is a article about using Object variable in a Script Task: http://sqlblog.com/blogs/andy_leonard/archive/2007/10/14/ssis-design-pattern-read-a-dataset-from-variable-in-a-script-task.aspx If you have any more questions, please feel free to ask. Thanks, Jin ChenJin Chen - MSFT
October 27th, 2010 2:53am