Importing CMS Open Payment APIs

user_8828432 0
Viewed 94

CMS Publishes Open Payments Data on their website (see: https://openpaymentsdata.cms.gov/developers)

I would like to import the API data using SSIS if possible. Otherwise, I'll have to download the excel spreadsheet and import it that way.

Is it possible to do this? And if so, which method is most practical for yearly data loads?

This will be my first time importing the data. The excel spreadsheets seem cumbersome and I would like to import the data from the website directly.

No code has been developed as of yet.

ssis dataset rest jquery c#
1 Answers

If I am not mistaken, the service in question has an HTTP endpoint that serves the data as JSON (string). The authentication methods seem to allow passing the token via an URL parameter. That makes things easier.

  1. My recommendation would be to use an SSIS component that allows you retrieving and importing the data (unfortunately, I can recommend only commercial components from well-known companies if you go that route).
  2. You can use a Script task (or an external script/app that downloads the data and stores it as file, e.g. using PowerShell as shown on your linked page) and get the data on your own via WebRequest or web service call and consume it using SQL Server 2016+'s JSON features, e.g. OPENJSON, or BULK import - if you have a lot of data. There are other ways for older SS versions as well, e.g. parsing JSON via C# script task.
user_82919490
Related