This post shows how you can use Google Finance to pull the stock quotes in SSIS without using any web services.
Knowing the stock prices of your portfolio is one import thing we do every day. To know the stock prices there are many websites available, which provide free services to create your portfolio and track their prices.
When it comes to pull the stock prices in SSIS there are many task and components which are helpful. One of the favourite components is Web Services Task, Script Task or Script Component. In order to use Web Services Task we need to call a web service which is providing the Stock Quotes free. We can call such web services in Script Task and Script Component as well.
The internet giant Google Finance is also available to achieve the same. In case you want to know the stock price of a company in XML format you can try the following URL from Google Finance.
The above URL will show the stock price of India most valuable company “Reliance Industries”. The stock prices are shown in XML format.
You can use the above Google Finance service in SSIS to pull the stock quotes.
For the demonstration purpose I have create a table which has following values and columns.
The table has four columns. StockName, StockSymbol, LastPrice and LastUpdateTime.
The StockSymbol field contains the value which we need to pass as querystring value to Google Finance.
To pull the stock quotes of above mention shares in table I created one OLE DB connection in my SSIS project which points to the Database which has the above table. I created following task under the Data Flow tab.
The Read Stocks
simply read all the values from table.
The Read From Google Finance
is a script component which connect with the Google Finance services and fetch the stock price values.
The Update Stock Prices
is a OLE DB Command task which updates the stock prices values back in the table.
I created a package level variable to store the Google Finance URL.
The script component Read From Google Finance uses the System.XML namespace to read the XML values return by the Google Finance. I have setup the ReadOnlyVariable
values to User::googleURL
. I have following codes in the script component.
As you can see in the code we are pulling the stock price Last values and updating it to LastPrices column. You can read values such as high, low, volume, market capitalization etc. from the XML values return by the Google finance.
The OLE DB Command Update Stock Prices
has following SQL Update statement.
After setting up the Data Flow Task components I connected with the internet and run the package. It runs successfully and pulls the stock prices values and updated them in the table.
This post shows one of the ways we can use to pull the stock prices of our favourite shares. There are many ways to accomplish the same in SSIS.
I hope you enjoy reading the post. Thanks for the reading.
Republished from Blog by Vikash Kumar Singh [46 clicks].
Read the original version here [5 clicks].