SSIS - Web Service via Script Task

Question

SSIS - Web Service via Script Task

bkirk

SSCrazy

Points: 2824
More actions
February 25, 2010 at 3:18 pm

#137438

I need to create a SSIS package for running an overnight batch process for triggering eWay payments via their web service.
I've searched high and low on the web but cannot find a proper tutorial or script task that i can use to adapt into my own.
Some research indicates that the SSIS Web Service for BIDS/SQL 2005 cannot do authentication in the SOAP header. I believe this is true as there is no Input parameters for the specific eWay header tags of:
- eWayCustomerID
- Username
- Password
I've read that only BIDS 2008 may be able to do this, hence why i need a VB.NET script to trigger the web service instead.
The basic process i need to build in the script is as follows:
1) SQL Task (return the payment records from my SQL Table) and pass through as a variable object
2) Create FOR Each Loop per row
3) Script Task
a) Build the XML Document using the SQL data per payment row
b) HTTP Post the XML document via the web service for Processing payments
c) Receive the XML result document
d) Deconstruct the XML results
e) UPDATE payment success/fail via SQL Task
4) Close For Each loop
Is there anyone who know of this type of web services script or tutorials. I really cannot find any good references.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Prathiba SSC Enthusiast Points: 112 More actions · Answer 1

Hi,

My requirement is exactly the same. All the steps showed are same. Did you find an answer to this? If not, what other approach did you take. Please let me know.

Thanks,

Katta

bkirk SSCrazy Points: 2824 More actions · Answer 2

I changed the way i triggered the payment system for Tokens with eWay due to the SSIS 2005 limitations and the fact that i don't have SSIS 2008.

Instead of a SSIS package, i created an ASP.NET processing code-behind page using eWay Token Payments Web Services.

I did a combination of SQL datasets and statements for INSERTS/UPDATES using TRY/CATCH blocks and a number of other ASP.NET techniques.

The long story short is that i had to develop my own VB scripts to do all the processing online based on various triggered events.

I suppose i could have moved my ASP.NET code-behind script into a SSIS package Script Task and then called it by passing in all the relevant parameters, however everything became a lot easier to do it online in the web page.

If i were creating a backend batch payment processing solution then i would probably go the SSIS route. This way everything could be hidden on your server and be invisible to the web front end. This way you limit the amount of security controls you need to build into the ASP.NET webpage for authentication and credit card processing prior to submissiion.

Prathiba SSC Enthusiast Points: 112 More actions · Answer 3

Thanks for the information. It is hard for me to understand the approach you took. I am still trying to consume web service via script task in SSIS package.

Thanks,

Katta

bkirk SSCrazy Points: 2824 More actions · Answer 4

As i said above, if you are using the eWay credit card web service then you won't be able to authenticate the user credentials unless you write your own VB Script task.

SSIS 2005 Web Service tasks do not allow proper login authentication credentials to be entered.

rwlp.french Old Hand Points: 346 More actions · Answer 5

Does this article help for passing user names and passwords?

PHYData DBA SSCertifiable Points: 7541 More actions · Answer 6

bkirk (5/12/2010)
As i said above, if you are using the eWay credit card web service then you won't be able to authenticate the user credentials unless you write your own VB Script task.
SSIS 2005 Web Service tasks do not allow proper login authentication credentials to be entered.

SSIS 2008 does this, but it still does not support complex types. However you can do it as a VB script task real easy. I also created a web service to debug my vb scripts and to help make a better wsdl file.

If anyone would like to ask me question about how to do all this with a very complex custom types and classes web service send me a private message. The service I had to do this for recently even had a custom date class that was part of a custom array of items class.

pcq0125 SSCrazy Points: 2890 More actions · Answer 7

PhyData,

Do you have an example how to call the web services and pass the complex type in XML on the SSIS script component with VB? Please advise.

Thanks,