To run a Microsoft SSIS ETL Package daily, pull the data from Web Service on the web and insert into the Data Warehouse.
So using this URL as example: https://www.mssqltips.com/sqlservertip/3272/example-using-web-services-with-sql-server-integration-services/
Pulling Exchange Rates from this URL: http://www.webservicex.net/CurrencyConvertor.asmx
So another developer had already started the package. I decided to change it from writing to and reading from a Text file to store in SSIS Variables, save a few steps.
First we truncate the Staging table.
Then we call a Sequential Container, which has the 5 Exchange Rate's we'd like to download, snippet shown here:
Looking into one of the Web Service calls, we configure settings:
And created SSIS Variables, one for each Currency and a Exchange Date:
Here we set the output to a variable:
From the Data Flow, to insert into Stage:
SQL Command source:
Derived Column settings:
Insert to Stage:
Here's the data flow to pull from Stage, see if it already exists in the Data Warehouse, if not, do an Insert, if it already exists, basically do nothing (Row Count equivalent to a dead end):
Raw SQL to convert XML:
[CurrencyAK] = [CurrencyAbbr] + '|' + convert(varchar(8),Convert(date,[DateRun]),112)
,[CurrencyName] = [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)))
,[ExchangeRate30DayAvg] = convert(decimal(18,2),0)
,[RowInsertedDateTime] = GetDate()
,[RowModfiedDateTime] = NULL
And the Field List:
Here's the lookup to DW, conditional logic, if found, don't do anything, if NOT found, we'll perform an insert further down:
We need to specify "Redirect Row", which means further down we need to connect to the Data Warehouse table with the Red line, indicating no record found. If we fail component, nothing after runs, if we Ignore Error, it will perform an insert even if the record already exist, so we Redirect Row and handle it with Lookup Error Output (do the insert).
Into the Data Warehouse table named DimCurrency:
Here you can see the raw data from Stage, actually inserted the Raw XML data from the Web Service. We do a truncate to stage each time to clear out any previous runs first.
And here's the DimCurrency in the Data Warehouse with actual data we just pulled from the Web Service. Running again, it recognizes the data already exists, and doesn't attempt another insert, so no duplicates.
So that's how we got around having to write to text files on the server and then reading from them. Instead we used SSIS Variables, wrote the raw XML to the Stage database, then transformed the XML using SQL and eventually inserted into Production Data Warehouse if it doesn't exist.
You may have seen in the snippet that the 30day average all show zero. That's because I still have to code it.
To get back data, this site from IMF seems to have good interface and exports to Excel or XML:
And there you have it~!
ps. I went back to change the Web Service calls to run sequential rather than parallel, as it threw errors. Works fine now.