Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS - Web Service via Script Task Expand / Collapse
Author
Message
Posted Thursday, February 25, 2010 3:18 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 3, 2011 5:17 AM
Points: 92, 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.
Post #873049
Posted Wednesday, April 28, 2010 7:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 12, 2010 2:04 PM
Points: 2, 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
Post #911922
Posted Wednesday, April 28, 2010 7:00 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 3, 2011 5:17 AM
Points: 92, 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.
Post #912469
Posted Wednesday, May 12, 2010 2:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 12, 2010 2:04 PM
Points: 2, 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
Post #920844
Posted Wednesday, May 12, 2010 6:16 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 3, 2011 5:17 AM
Points: 92, 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.
Post #920929
Posted Monday, December 13, 2010 2:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 2:18 AM
Points: 25, Visits: 581
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]
Post #1033643
Posted Wednesday, September 4, 2013 1:20 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 2:15 PM
Points: 322, Visits: 287
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.
Post #1491495
Posted Tuesday, July 8, 2014 7:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 7:33 AM
Points: 104, Visits: 331
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,
Post #1590357
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse