SSIS - update table column

1
votes

In my SSIS Package I load data into a staging table from multiple sources. Once all data is loaded I would like to submit all records to a web service and then store returned IDs in the same staging table. Let's say the staging table looks like this:

ServiceID | ColA | ColB
NULL      | xxx  | yyy
NULL      | vvv  | zzz

I was thinking about creating a Data Flow Task that reads from the table, then executes a Script Component, which submits data to my web service and then updates the table with returned IDs. Is this possible or do I need to create an identical table, that would receive the updated data?

1
sql-serverssis
Why is the ServiceID NULL..Am I to assume that these are all UNIQUE IDs? Your prompt is a bit generic. – J.S. Orris
This is how the staging table looks like before calling the web service, so the ServiceID is null – filip
Yes it's possible performance wise there is no point creating another table. Just update your existing staging table. – Nick.McDermaid
The question is how you update it within SSIS DataFlow component with results from the Web Service? – filip

1 Answers

1
votes

See here....

http://www.bimonkey.com/2009/09/the-script-transformation-part-3-as-a-destination/

  1. GO into your data flow
  2. Drop a Script Destination on the page (from the transformation area)
  3. When prompted set it as a destination
  4. Join your source to the script component
  5. Build your code in here to lookup the web service and use standard ADO.NET code to run your UPDATE

I have never done this before so I'm just parroting what I've found online. If you were to ask for more detail I would just be posting links to sample code sorry.

It appears that you could alternatively use the Script Component as a transformation (looking up the web service) and feed it through to a data flow destination (insert to your new staging table). This would assist with auditing and data checking. The final Update time would be negligible in comparison to the web service calls.

I suspect you have already got this far though.