how to call web service from within Script Component ?

Appreciate if anyone can show me the code to call a web service from Script Component ?

I cannot use the Web Service Task. Because parameters to the webservice are from rows of data inside Data Flow Task.

Thanks !!

Can you not populate a staging table to be used as such:

- After the data flow completes loading a staging table, you'll have an execute SQL task in the control flow to select the data out of that table and store it in an Object variable.
- Use a foreach loop to loop through that object variable, mapping the various columns (if any) to variables
- Inside the foreach loop will be your Web service task. Each time it is called it will use the current value of the parameter variables
- After the foreach loop, you can add another execute SQL task to truncate the staging table if desired.

Does that make sense? At a high level anyway?|||

I think i got what you're saying. However, there is a lot of data. When we go through the data row by row, we take the row data and do 20 different things (update this and that in the database). There is one thing that depends on the output from the webservice.

So, if we use a staging table, memory could become an issue, maybe ? Also, looks like i'll have to to create Data Flow task "twice". The first Data flow task will read the flat file, do some lookup, etc, and put data into a staging table. Then call Web Service Task in the Control Flow. Now that i have the output from the webservice, i need to pass this big object variable back to a Data Flow task for further processing.

I'm just hoping there is a easy way to call the webservice from Script component.... is there a way ?

thanks

|||

mf915 wrote:

Appreciate if anyone can show me the code to call a web service from Script Component ?

I cannot use the Web Service Task. Because parameters to the webservice are from rows of data inside Data Flow Task.

Thanks !!

Hi there,

If you buy Donald Farmer's book (http://www.amazon.com/Rational-Guide-Extending-Script-Guides/dp/1932577254/sr=8-1/qid=1171045629/ref=pd_bbs_sr_1/102-7891523-4086513?ie=UTF8&s=books) there is a downloadable extra chapter that explains EXACTLY how to do this. Many a time I have considered blogging it but that would effectively be plagiarsing Donald's book and I don't want to do that.

-Jamie

|||thanks ! but the book is not available from rationalpress.com until March :(|||

mf915 wrote:

thanks ! but the book is not available from rationalpress.com until March :(

Don't worry. You can get the beta preview version. It contains the same stuff: http://www.amazon.com/Rational-Scripting-Integration-Services-Preview/dp/1932577211/sr=1-2/qid=1171050141/ref=sr_1_2/102-7891523-4086513?ie=UTF8&s=books

-Jamie

|||

Here's one way to use a web service from within an SSIS script component.

1. From the command line, create a wrapper class for the web service using wsdl.exe

wsdl /language:VB http://ws.strikeiron.com/relauto/iplookup?WSDL /out:IPLookup.vb

2. Create an SSIS dataflow, adding in a script component transform.

3. In the script transform, from the Project menu, use "Add Existing Item.." and pull in the wrapper class generated in step 1. You may need to delete the first first few "garbage" characters, which are the Unicode byte order mark.

4. From the Project Menu again, choose Add Reference, and select both the System.Xml and System.Web.Services assemblies.

5. Reference the class from within the transform.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Private ws As DNS = New DNS()

' Wrapper for web service

Private dnsInfo As DNSInfo

' wrapper for Response from web service

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Try

Dim server As String = Row.ServerName

dnsInfo = ws.DNSLookup(server)

ComponentMetaData.FireInformation(0, ComponentMetaData.Name, _

String.Format(" {0} maps to IPv4 {1}", server, dnsInfo.AddressList(0)), _

String.Empty, 0, True)

Catch ex As Exception

ComponentMetaData.FireError(1, ComponentMetaData.Name, _

ex.Message, String.Empty, 0, True)

End Try

End Sub

End Class

|||

Spot on as usual jaegd. This is how Donald describes doing it.

-Jamie

|||

Well, I've been able to sucessfully connect to a web service (in my case a Sharepoint List) in the script component using the method described above, and now things are starting to get messy. I'm wondering if anyone has any advice or a "best practice suggestion" for the following scenario, where I'm taking the XML response of a web service and pushing into the data flow using the "XML Source" component:

1. In the script component, query a web service and get the XML response.

1b. Remove multiple namespaces from the XML response using XSLT, since the XML Source component can't handle them.

2. Get the schema of the web service response using the DataSet class/GetXMLSchema method.

3. Push the XML response data into a variable to be read by the "XML Source" component in the dataflow.

4. Write the inferred XML schema to a file. The file name is set using the System::ExecutionInstanceGUID, which ensures a unique file name linked to the particular package execution.

5. Open up the expressions for the data flow task, and set the XMLSchemaDefinition property for the XML Soruce Component to point to the schema file just written. Because this does file not exist at validation time, the data flow task property DelayValidation must be set to True.

Of course, it would be impossible to even create a data flow without having some sort of "starting point" schema to work with. I'm just trying to construct some sort of framework so I can add or remove an item from the Sharepoint List I'm accessing using via Web Service and it doesn't cause my package to fail all over the place because the schema doesn't exactly match the data.

With this (or perhaps a simpler, easier approach?) the dynamically generated schema can change as I make small changes to my sharepoint lists. I'm wondering if I'm making this too complicated, or if it would just be easier to have a maually-maintained repository of XML schemas somewhere in the filesystem.

|||I would just maintain your own XML schemas, and pass the values in through configurations. I'm not sure if there is a simpler or easier approach to a package that has a dynamic schema. Your destinations will have problems with flucuating schemas as well....and from a brief search on the forum building a package dynamically at runtime may be your only option.