An interesting problem has been posed on the SSIS forum at SQL Server Central. The user wants to download a file containing stock information from Yahoo finance and import the data into a database table.
That sounded like an interesting problem so I took up the challenge. The basis of my solution uses the code that Ashvini Sharma has posted here that shows how to download files over HTTP.
Here's everything that happens in the package in order to make this happen and display the results:
- Build a URL dynamically using an expression on the @[User::URL] variable using various parameter values within the package. The parameter values are hardcoded here but they could easily be changed at runtime using any one of a number of methods.
- Using a slightly modifed version of Ashvini's code the file is downloaded to a location defined by a Connection Manager within the package.
- That same file, pointed at using the Connection Manager, is loaded into the SSIS pipeline. Here we simply view the data using a data viewer but of course once it is in the pipeline you can do whatever you want with it.
That's about it really. Here's a screenshot of the package in progress:
Hope that's useful to someone. The power to download data over HTTP and process it within SSIS is really powerful. Good luck in using it!
The demo package is attached to this blog post. See the link below.
Comments are welcome!!!
-Jamie