Fetch & Load API data to SQL Server Specific Use Case!
Preetha R.
Microsoft Certified Azure Solutions Architect Expert | Bridging Business Goals with Cloud Excellence | Strong Problem-Solving Skills | Follow my insights at #techysvault
Hi Friends,
This week, I thought to share a specific requirement that is to "leverage ONLY SQL server capabilities for API Calls, avoid the use of third-party tools & load the API data to MS SQL server tables. ". Once loaded, this data was used to create Power Bi repots for business users.
Here, I will just provide the approach we followed to accomplish this task.?
Initially, we tried to use the web service task in SSIS, but due to security constraints we were not able to use it for our scenario. You can check out Microsoft documentation/refer this quick video 103 Web Service task in SSIS | How to use Web service task in SSIS - YouTube
We decided to go with below approach->
1.?????? Created PowerShell script, to extract API data and save it in Json format. Then load it into the SQL server table.
2.?????? Created SSIS package to execute the PowerShell script using script task.
3.?????? Apply the transformation rules and load the data into SQL server staging table and then move the data to the master table (based on our use case).
Reference Links
We used below links to come up with the PowerShell script for our scenario-
领英推荐
Conclusion:
It has been almost 4 years since we had implemented this approach for one of our clients. The reason why I posted it as an article here is, many of my friends/colleagues approached me to get recommended solution for similar use case. This week also, I got similar request.
So, friends in general there may be many possible solutions to the problem statement, that may vary based on the use case and business requirement. We should take up the sole responsibility of first analyzing the requirement thoroughly and then coming up with the opt solution.
Enjoy learning and sharing ??
Thank You All ??
?
?
?
?