get usd to gbp history exchange rates conversation information for 1 year back to still date using ssis package

• asrinu13

  Say Hey Kid

  Points: 709

  More actions

  January 19, 2017 at 12:23 am

  #401422

  Hi ,I have doubt in ssis,

  how to get usd to gbp  history exchange rates conversation information for 1 year back to still date using ssis package.

  here I Done current live values for ssis package ran day information values using webservice task.

  how to get exchange rates history values for 2016 year(january 1 to till date)  get currency exchange rates for 4 countries for 2016 year  currency exchange from USD to other 4 countries.  to run ssis ETL package daily,pull the data from webservice  on the web and insert into the stage table.step 1 : open new project of integration services and rename the default package name as webservicestep 2 : drag and drop the webservic task in package and create 4 variables (GBP_value ,AUD_value,cad_value,BRL_value\)step3 : create new HTTP connection by right clink in connnection managerand select http connection and enter the server url as http://www.webservicex.net/CurrencyConvertor.asmx?WSDL and testthe test connection.step 4: open the new notepad and save it as currencyconvertor.wsdlfor required location .step 5 : Double click on the web service task and confiure like belowin general tab: Httpconnection .....>http connection manager which we create earlywsdl file .....> browse and select the currencyconvertor.wsdl fileoverwritewsdlfile.....> set as truedownload the wsl file and overwrite to empty wsdl fileinput tab:in input tab we have to configurservice .....>currecconvertormethod.....>convesionrateand also I need to select the from currency value and tocurrencyvalueFromCurrency as usd(us dollar) and Tocurrency(GBP uk)output:in output tab i have to configur the output type : variable : GBP_valuestep 6 : from the datafow to insert the stagein oledb source select data access mode : sql command

  select getdate()as daterun ,convert(nvarchar(4000),NULL)as viewxml ,convert(nvarchar(3),'GBP')as currencytype

  then fields list like daterun,viewxml,currencytypederievd column to expression : @[user::GBP_value]insert to stage dimcurrencystage.after that take another dft and drag and drop oledb source and dataaccess is sql command and write query like

  SELECT [Currencytype] = [CurrencyAbbr] [DateSK] = convert(int,convert(varchar(8),Convert(date,[DateRun]),112)) ,[ExchangeRate] = CONVERT(Decimal(18,2),CAST(CAST(ViewXML AS XML).query('data(/double)') AS varchar(4000))) FROM
  [dbo].[DimCurrency_Stage]

  then this data load in to final table.its working finesimilary to I developed remains countries.its working fine when i ran package that day related exchange rates only I am getting.I need 2016 year to till history exchanges rates information (usd to others)please tell me how to achive this task in ssis.