Consume web service data daily

[G12Consult]

Can someone point me in the right direction for the best practice approach (security, efficient etc) of consuming data (automatically through scheduled jobs) from a web service daily and storing information into SQL server.

a vendor offers a web service where I can pull through my client information, I want to know how I can get this info stored in my db.

I have a finance model which I am developing and need to pull in new clients and their information which changes daily.

Thanks
Andrew

 

[johnherman]

First, bring the unedited data into a loading table in SQL Server. This is your copy of the original data and will help with any troubleshooting. Determine how long you want to keep this data around. At the very least, keep it until you are ready to get your next update. I would keep it longer, maybe more than a month. Maybe forever depending on whether the data could be subject to audit (you mentioned FINANCE !)

Then, move the data into a staging table where you can check the quality of the data and do any transformations and conversions that you need to make it "joinable" to any other tables. This data should also have a retention period similar to the one above, or perhaps even longer.

Finally, move the data into a table designed to handle the queries and reports that need the data. Again, you'll need to consider your data retention period and whether you want to overwrite this table with the latest data each time you make an update. However, remember that there will be reports out there against previous versions of the table. To me, this means you need to have copies or versions of this table, or inactive records within this table.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[G12Consult]

Thanks for your reply.

I was looking for suggestions on best ways of getting the data into SQL Server. Should I consume the web service using asp.net (app language) or is there a way to do this from a stored procedure and an scheduled job

 

[gmmastros]

I've done this numerous times. I usually create a c# winforms app to do it. I make it a winforms app so that I can configure it with the server, database, user, webservice location, etc...

I then use command line arguments to just run it (assuming it is configured properly).

Once everything runs the way I want it to, I then use windows task scheduler to run it on a schedule.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom