question

VikramD-5494 avatar image
0 Votes"
VikramD-5494 asked JoenathanEdwards-5763 commented

Calling a Restful API in SSIS and returning output

Hi All,

I have a requirement to call a restful API fetch the results. Request will be in xml form and so the response also.

I'm using VS 2019. Please let me know if you have any steps to implement this in c# script task or sample code.

Thanks in advance.

Vikram

sql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @VikramD-5494 - Did the answer from Mona work?

0 Votes 0 ·
Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft commented

Hi @VikramD-5494 ,

  public void Main()    
         {    
             try    
             {    
                 string serviceUrl = Dts.Variables["$Project::ServiceUrl"].Value.ToString();    
                 HttpClient client = new HttpClient();    
                 client.BaseAddress = new Uri(serviceUrl);    
                 // Add an Accept header for JSON format.    
                 client.DefaultRequestHeaders.Accept.Add(    
                     new MediaTypeWithQualityHeaderValue("application/json"));    
        
                 decimal amount = 1200.50m;    
                 long tranactionID = 1001;    
                 string APIUrl = string.Format(serviceUrl + "/ProcessManager/ProcessData?amount={0}&transactionID={1}", amount, tranactionID);    
                 var response = client.GetAsync(APIUrl).Result;    
                 if (response.IsSuccessStatusCode)    
                 {    
                     var result = response.Content.ReadAsAsync<Response>().Result;    
                     if (result.IsSucess)    
                     {  
                         //TODO update your database based on the result    
                     }    
                     Dts.TaskResult = (int)ScriptResults.Success;    
                 }    
             }    
             catch (Exception ex)    
             {    
                 Dts.TaskResult = (int)ScriptResults.Failure;    
             }    
         }   

Hope the following links will be helpful:

1.How To Consume Web API Through SSIS Package

2.Download source file from website with SSIS

Best Regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues in November--What can I do if my transaction log is full?
Hot issues in November--How to convert Profiler trace into a SQL Server table?




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @VikramD-5494 ,

May I know if you have anything to update?

Best Regards,
Mona

0 Votes 0 ·
COZYROC avatar image
0 Votes"
COZYROC Suspended answered JoenathanEdwards-5763 commented

Hi Vikram,

I would recommend you check the commercial COZYROC REST technology. You can use any REST API with it.


· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Vikram,

I am a COZYROC user.Seems the COZYROC REST can get data from specific APIs only like GoogleAnalytics/Linkedin etc but not third party APIs ?

0 Votes 0 ·

Hi Cozyroc,
How does this tool work when we deploy the solution to the vmwhere there will be a SQL job calling it ? (another vm, not the one where VS is installed )
Should this addon be installed there too ?

0 Votes 0 ·
COZYROC avatar image COZYROC PlamenaKostadinova-8538 ·

The COZYROC SSIS+ library has to be installed in the environment where the actual package execution takes place.

0 Votes 0 ·
JoenathanEdwards-5763 avatar image JoenathanEdwards-5763 PlamenaKostadinova-8538 ·

To anyone else reading this, I purchased the $1.2k Cozyroc subscription and I regret it, it is junk, full of bugs and support will treat you horribly from your very first interaction with them. Do be like me kids, don't waste your money, find another solution.

0 Votes 0 ·