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.
http://www.google.com/ig/api?stock=RIL
The above URL will show the stock price of India most valuable company “Reliance Industries”. The stock prices are shown in XML format.
- SSIS: Read public data of Twitter Users?
- SSIS: How to pull Tweets from Twitter?
- SSIS: How to pull Currency Rates from European Central Bank
- SSIS: How to pull Stock Quotes from Google Finance
- SSIS: How to load data into SQL table from Google Spreadsheet?
- SSIS: How to Download images from Picasa album?
- SSIS: How to Load images into Picasa web album?
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.
http://www.google.com/ig/api?stock=RIL
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.
If you want to explore more with the Source code of this post; please visit Download Zone.
Related Article:
- SSIS: Read public data of Twitter Users?
- SSIS: How to pull Currency Rates from European Central Bank
- SSIS: How to pull Tweets from Twitter?
- SSIS: How to read Excel Meta Data?
- SSIS: How to resolve Excel Import 255 character Truncation issue?
- SSIS: Read and Export Excel data from nth Row
- SSIS: How to pull Stock Quotes from Google Finance
- SSIS: How to load data into SQL table from Google Spreadsheet?
- SSIS: How to Download images from Picasa album?
- SSIS: How to Load images into Picasa web album?
- SSIS: How to Load XML search result from Search engines like Bing, Google etc?
- SSIS: How to generate HyperLink in Excel output?
- SSIS: How to Compress/Zip your file using 7-Zip?
- SSIS: What is CheckPoints?
- SSIS: Checkpoints implementation
- SSIS: Event Handlers
- SSIS: Data Flow v/s Control Flow
