SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS - Web Service via Script Task


SSIS - Web Service via Script Task

Author
Message
bkirk
bkirk
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 346
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.
Prathiba
Prathiba
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 7
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
bkirk
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 346
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
Prathiba
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 7
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
bkirk
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 346
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
rwlp.french
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 800
Does this article help for passing user names and passwords?

[url=http://sqlblogcasts.com/blogs/greenmtnsun/archive/2007/09/06/http-authenticatoin-download-of-file-in-ssis-to-csv-file.aspx][/url]
PHYData DBA
PHYData DBA
SSC Eights!
SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)

Group: General Forum Members
Points: 935 Visits: 537
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
pcq0125
SSC Veteran
SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)

Group: General Forum Members
Points: 244 Visits: 371
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,
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search