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 ««12

Capturing Real-Time Currency Conversions in SSIS Expand / Collapse
Author
Message
Posted Thursday, April 29, 2010 1:41 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:03 PM
Points: 17,941, Visits: 15,930
Thanks for the article.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #913228
Posted Thursday, April 29, 2010 2:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 11:53 AM
Points: 27, Visits: 146
richard.maw (4/29/2010)
There may be other motives for the method shown that I'm not getting but it seems to be an insanely inefficient and complicated way to do it: read service, write to file, read file, parse text, convert text, wrap it in dataset, iterate over dataset and then run the update. You can do it in two steps (1) read service into text variable (2) pass the text variable to the update statement. The parsing is done in SQL like this:

Update CurrencyConversion
Set USD_Amount = Amount * convert(xml,?).value('(//double)[1]','float'),
ConvertedDate = getdate()
Where USD_Amount IS NULL



Good point and very true. As with any exercise I wanted to show off some of the other capabilities that SSIS has... some for my benefit and hopefully for the benefit of a few others.
Post #913264
Posted Thursday, April 29, 2010 6:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 19, 2013 12:54 PM
Points: 2, Visits: 28
Fair enough, I learned something myself. But if someone is just looking for the functionality provided the two-step method is the way to go.
Post #913335
Posted Sunday, May 2, 2010 6:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 3, 2012 1:52 AM
Points: 1, Visits: 7
Hi,

Good articles, but it is not working for me

I got below error message in RecordSet Destination

Error at Data Flow Task [RecordSet Destination ..]: The type of the runtime variable "User:ExecutedConversion" is incorrect. The runtime variable type must be Object

please assists.

Bahru
Post #914349
Posted Monday, May 3, 2010 3:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:02 AM
Points: 1,194, Visits: 789
For this Error please go the variable section and change the DataType to Object.
Post #914546
Posted Monday, May 3, 2010 3:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:02 AM
Points: 1,194, Visits: 789
Hi All,

I have similar kind of requirement where i need to change the amount into the USD,but in my case the amount will be in different currency format say for some columns it is IND and for some column it is CAD.so how to go in this scenario.
Post #914548
Posted Monday, May 3, 2010 8:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 11:53 AM
Points: 27, Visits: 146
You might have to do multiple calls into the web service based on the number of currency codes you are converting.
Post #914710
Posted Wednesday, May 12, 2010 2:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 2, 2011 1:22 AM
Points: 1, Visits: 12
Hi,

This is a nice article. I am replacing the currect logic in my application to this one. It is throwing an error "Proxy authentication required" (refer the error below) do i need to add the proxy credentials? can anybody help please...

[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The Web Service threw an error during method execution. The error is: The request failed with HTTP status 407: Proxy Authentication Required.. at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".
Post #920234
Posted Thursday, August 12, 2010 8:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 5:02 AM
Points: 2, Visits: 88
Thanks for the Article Its just what I am looking for .
I cant get it to work though,I think its something to do with the variables .
And explicitly setting them from currency to string,I just dont have a clue how to do that though .


[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Object reference not set to an instance of an object..
at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection)
at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser)
at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".
Post #968210
Posted Tuesday, May 21, 2013 4:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 12:22 AM
Points: 2, Visits: 61
I followed exactly the same to create it. But I am getting the following error :
[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The Web Service threw an error during method execution. The error is: The request failed with HTTP status 417: Expectation failed..
at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection)
at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser)
at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".



When I tried to call the same web service using Script Task, I am getting the following error:
Could not find default endpoint element that references contract 'CurrencyConverter.CurrencyConvertorSoap' in the ServiceModel client configuration section. This might be because no configuration file was found for your application, or because no endpoint element matching this contract could be found in the client element.


Please advice how to proceed
Post #1454905
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse