ADO work items in Azure SQL DB using ADO REST API in ADF

%3CLINGO-SUB%20id%3D%22lingo-sub-2320886%22%20slang%3D%22en-US%22%3EADO%20work%20items%20in%20Azure%20SQL%20DB%20using%20ADO%20REST%20API%20in%20ADF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2320886%22%20slang%3D%22en-US%22%3E%3CP%3EAnyone%20managed%20to%20get%20the%20work%20items%20using%20ADO%20REST%20API%3F%20I%20have%20setup%20everything%20but%20each%20time%20I%20try%20to%20preview%20data%20(ADO%20wit)%20or%20run%20the%20activity%2C%20I%20am%20getting%20error%3A%3C%2FP%3E%3CBLOCKQUOTE%3E%3CP%3EError%20occurred%20when%20deserializing%20source%20JSON%20file%20''.%20Check%20if%20the%20data%20is%20in%20valid%20JSON%20object%20format.%20Unexpected%20character%20encountered%20while%20parsing%20value%3A%20%26lt%3B.%20Path%20''%2C%20line%200%2C%20position%200.%26gt%3B%3C%2FP%3E%3C%2FBLOCKQUOTE%3E%3CP%3EI%20have%20constructed%20REST%20API%20as%20below%20and%20using%20POST%20method%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fdev.azure.com%2F%257Borganization%257D%2F%257Bproject%257D%2F%257Bteam%257D%2F_apis%2Fwit%2Fwiql%3Fapi-version%3D6.0%22%20rel%3D%22nofollow%20noreferrer%20noopener%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Fdev.azure.com%2F%7Borganization%7D%2F%7Bproject%7D%2F%7Bteam%7D%2F_apis%2Fwit%2Fwiql%3Fapi-version%3D6.0%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20rest%20API%20url%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdev.azure.com%2FMyOrg%2Fmyproj%2Fmyteam%2F_apis%2Fwit%2Fwiql%3Fapi-version%3D6.0%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdev.azure.com%2FMyOrg%2Fmyproj%2Fmyteam%2F_apis%2Fwit%2Fwiql%3Fapi-version%3D6.0%3C%2FA%3E%3C%2FP%3E%3CP%3Efurther%20info%20on%20variable%3A%3C%2FP%3E%3CP%3E%40apiBody1%3D%7B%20%22query%22%3A%20%22Select%20%5BSystem.Id%5D%20From%20WorkItems%20Where%20%5BSystem.WorkItemType%5D%20In%20('%3C%2FP%3E%3CP%3E%40apiBody2%3D'%20and%20%5BSystem.AreaPath%5D%20%3D%20'mypath'%22%20%7D%20--sometime%20this%20also%20has%20'%5C'%20such%20as%20mypath%5Csubpath%3C%2FP%3E%3CP%3Efurther%20I%20am%20concatenating%20the%20variables%3A%3C%2FP%3E%3CP%3E%40%7Bconcat(variables('apiBody1')%2Citem().WorkItemType%2Cvariables('apiBody2'))%7D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2321250%22%20slang%3D%22en-US%22%3ERe%3A%20ADO%20work%20items%20in%20Azure%20SQL%20DB%20using%20ADO%20REST%20API%20in%20ADF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2321250%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1044460%22%20target%3D%22_blank%22%3E%40vishalhsingh%3C%2FA%3E%26nbsp%3B-%20I%20am%20using%20PAT%20for%20authentication.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Anyone managed to get the work items using ADO REST API? I have setup everything but each time I try to preview data (ADO wit) or run the activity, I am getting error:

Error occurred when deserializing source JSON file ''. Check if the data is in valid JSON object format. Unexpected character encountered while parsing value: <. Path '', line 0, position 0.>

I have constructed REST API as below and using POST method https://dev.azure.com/{organization}/{project}/{team}/_apis/wit/wiql?api-version=6.0

 

My rest API url: https://dev.azure.com/MyOrg/myproj/myteam/_apis/wit/wiql?api-version=6.0

further info on variable:

@apiBody1={ "query": "Select [System.Id] From WorkItems Where [System.WorkItemType] In ('

@apiBody2=' and [System.AreaPath] = 'mypath'" } --sometime this also has '\' such as mypath\subpath

further I am concatenating the variables:

@{concat(variables('apiBody1'),item().WorkItemType,variables('apiBody2'))}

1 Reply

I am using PAT for authentication.