Legacy Dev Forum Posts

Ron_Zablocki | 2022-05-10 16:02:04 UTC | #1

I am trying to automate a manual process where our Outreach team exports a Call Contact List from the Genesys List Management page. My end goal is to use SSIS to be able to import this CSV file into a SQL table.

I can use Postman to generate the contact list detail, but I'm not successful in calling Genesys APIs via a SSIS toolkit from KingswaySoft. The URL that Genesys creates is an encrypted redirect URL that Postman is able to "auto-magically" decode and process to get the call detail data, and I'm not able to replicate what Postman is doing in SSIS. Postman can also generate the URL

Is my above goal of being able to automate the exporting of call contact lists to CSV files possible to do, ideally within the SSIS environment?

tim.smith | 2022-05-10 16:07:37 UTC | #2

I'm not familiar with your 3rd party toolset, but you can prevent the redirect to the export file by using the ?download=false query string parameter when calling GET /api/v2/outbound/contactlists/{contactListId}/export. This will return the export URI as a response property instead of the API issuing a 301 redirect, which is the part it sounds like your 3rd party tooling is struggling with. As long as your tooling is capable of reading that URI from the response and issuing the download request to that location (including the authorization header), it should be able to successfully download the file without processing a redirect.

James_DiBernardo | 2022-05-10 18:22:54 UTC | #3

Hi Tim, i'm working with Ron on this -- i decided to try this in Powershell. In our Dev environment:

    #TEMP FOR TESTING
$bearer = <bearer goes here -- removing because reasons>
$contactlistid = "1fac47b5-82a9-4092-b03f-204b59195f02"

#Args from SQL
# $bearer = $arg[0]
# $contactlistid = $arg[1]

#Variables 
$POSTContactlist = 'https://api.usw2.pure.cloud/api/v2/outbound/contactlists/' + $contactlistid + '/export'
$GETContactList = 'https://api.usw2.pure.cloud/api/v2/outbound/contactlists/' + $contactlistid + '/export?download=false'

#Connect and refresh Bearer Token. (soon)

#POST - Initiate the export of a contact list
$POSTheaders = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$POSTheaders.Add("Accept", "application/json")
$POSTheaders.Add("Content-Type", "application/json")
$POSTheaders.Add("authorization", "Bearer " + $bearer)

$POSTresponse = Invoke-RestMethod $POSTContactlist -Method 'POST' -Headers $POSTheaders
$POSTresponse | ConvertTo-Json

#...give it a few to make the download
Start-sleep -seconds 5

#GET - Get the URI of a contact list export
$GETheaders = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$GETheaders.Add("Accept", "application/json")
$GETheaders.Add("Content-Type", "application/json")
$GETheaders.Add("authorization", "Bearer " + $bearer)

$GETresponse = Invoke-RestMethod $GETContactList -Method 'GET' -Headers $GETheaders
#see the output to make sure.
$GETresponse | ConvertTo-Json

##capture the joson response and send to a string

Invoke-RestMethod -URI $GETresponse.uri -outfile 'C:\temp\TEST.csv'

What's interesting is that if that flag that you mentioned is set to false, it will make the uri that this script can capture but when attempting to download the uri to an output file it will save off the HTML and not the CSV output that we see in Postman. Same for if the flag was set to true.

I think what's happening is that postman is clearly doing some level of magic to output its final form that is the CSV. Any idea how that is setup? i think thats what we're missing.

James_DiBernardo | 2022-05-10 18:23:26 UTC | #4

James_DiBernardo | 2022-05-10 18:23:59 UTC | #5

Ron_Zablocki | 2022-05-10 18:25:47 UTC | #6

tim.smith | 2022-05-10 18:42:55 UTC | #7

James_DiBernardo, post:3, topic:14673
Invoke-RestMethod -URI $GETresponse.uri -outfile 'C:\temp\TEST.csv'

tim.smith, post:2, topic:14673
and issuing the download request to that location (including the authorization header)

Don't forget the auth header. :)

James_DiBernardo | 2022-05-10 18:54:50 UTC | #8

Maybe I'm not understanding -- isn't that what this is doing? My apologies this is new territory for me.

tim.smith | 2022-05-10 19:00:31 UTC | #9

I'm not a powershell user, but the way I read that code is that you are sending the authorization header on line 34 because the call to Invoke-RestMethod specifies -Headers. When you call Invoke-RestMethod again on line 43, you are not specifying any headers for the request so the download service redirects you to the Genesys Cloud login screen because you made an unauthenticated API request. The HTML document you're getting should be the login screen's HTML.

James_DiBernardo | 2022-05-10 19:03:08 UTC | #10

the whole couple of steps is carrying that logic at run time. So once you define the variable you can call it in later steps of the script.

tim.smith | 2022-05-10 19:15:09 UTC | #11

James_DiBernardo, post:10, topic:14673
So once you define the variable you can call it in later steps of the script.

But you're not using $headers on line 43 based on what I see in the screenshot; it appears you're making the GET request without specifying any headers. The fact that you're getting the login screen's HTML code indicates that you're not making a properly authorized request.

FWIW it works in postman because it's following the 301 redirect and adding your original headers when making the request to the new location. Since it sounds like your environment doesn't send the original headers with the redirected request, you have to do it in two steps so you can explicitly add the authorization header on the GET request to download the CSV.

tim.smith | 2022-05-10 19:16:43 UTC | #12

Also, there's a JavaScript example that shows adding the authorization header on the request to download the file: https://developer.genesys.cloud/routing/outbound/exportcontactlists

James_DiBernardo | 2022-05-10 20:11:17 UTC | #13

ill tinker with that line and get back to you -- thanks Tim

(pause)

James_DiBernardo | 2022-05-10 20:16:53 UTC | #14

well ... i can do it in powershell... (good call out Tim)

#TEMP FOR TESTING
$bearer = "Bearer xxxxxxxxxxxxxxxxxxxx"
$contactlistid = "1fac47b5-82a9-4092-b03f-204b59195f02"

#Args from SQL
# $bearer = $arg[0]
# $contactlistid = $arg[1]

#Variables 
$POSTContactlist = 'https://api.usw2.pure.cloud/api/v2/outbound/contactlists/' + $contactlistid + '/export'
$GETContactList = 'https://api.usw2.pure.cloud/api/v2/outbound/contactlists/' + $contactlistid + '/export?download=false'

#Connect and refresh Bearer Token. (soon)

#POST - Initiate the export of a contact list
$POSTheaders = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$POSTheaders.Add("Accept", "application/json")
$POSTheaders.Add("Content-Type", "application/json")
$POSTheaders.Add("authorization", $bearer)

$POSTresponse = Invoke-RestMethod $POSTContactlist -Method 'POST' -Headers $POSTheaders
$POSTresponse | ConvertTo-Json

#...give it a few to make the download
Start-sleep -seconds 5

#GET - Get the URI of a contact list export
$GETheaders = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$GETheaders.Add("Accept", "application/json")
$GETheaders.Add("Content-Type", "application/json")
$GETheaders.Add("authorization", $bearer)

$GETresponse = Invoke-RestMethod $GETContactList -Method 'GET' -Headers $GETheaders
#see the output to make sure.
$GETresponse | ConvertTo-Json

##capture the json response and send to a string

Invoke-RestMethod -URI $GETresponse.uri -outfile 'C:\Temp\GENESYS\PS_TEST.csv' -Headers $GETheaders

tim.smith | 2022-05-10 20:20:50 UTC | #15

Glad you got it working!

I just found some info that's not publicly documented about the download endpoint you're making the second request to (GET /api/v2/downloads/{downloadId}). It may be useful for testing and anyone else that happens upon this post in the future.

It allows two optional query parameters: issueRedirect and redirectToAuth. If you issue the request adding on ?issueRedirect=false&redirectToAuth=false I believe it will return an error instead redirecting to auth when you don't send a valid auth token. You'll also get back the signed URL that includes a temporal token by using issueRedirect.

Ron_Zablocki | 2022-05-10 21:41:23 UTC | #16

Hi Tim - Yes, we were able to get it working in a static environment. Unfortunately the automation part is still an issue. I think we're close, but the export that's being generated contains: { "message": "HTTP 404 Not Found", "code": "not found", "status": 404, "contextId": "be8e2703-a1d0-4f66-a138-3faeaeee4a00", "details": [], "errors": [] }

If we can figure out what's causing the "HTTP 404 Not Found" error, we might be good. This is what's coming from the SSIS package that we're trying to automate this with.

tim.smith | 2022-05-10 21:56:43 UTC | #17

Thanks for sharing the correlation ID. Unfortunately, there's nothing in the API logs beyond what's in the response payload. The best advice I can give is to validate the path of the URL it's making the request to. It is hitting the correct API host because it's generating a correlation ID, but the path isn't valid in some way. I would expect more tracing if it failed to retrieve a download for some reason, but the lack of tracking leads me to believe it's more likely that the path that's being requested is incorrect in some way. Can you get the SSIS package to trace out the full details of the request it's making to see the path it's using?

Ron_Zablocki | 2022-05-10 22:14:21 UTC | #18

In the first call a POST is used to initiate the file export, and that results in the following: { "id": "1fac47b5-82a9-4092-b03f-204b59195f02", "selfUri": "/api/v2/outbound/contactlists/1fac47b5-82a9-4092-b03f-204b59195f02" }

After waiting ~5 seconds, the next call is a GET with the same parameters and that generates: { "uri": "https://api.usw2.pure.cloud/api/v2/downloads/a04b2902b3f3a309", "exportTimestamp": "2022-05-10T22:02:05.825Z" }

After those 2 calls a HTTP Requestor component from the toolkit is used to actually download the file to the specified location. I posted the contents of that generated file.

The data coming into the HTTP Requestor is:

Unfortunately we can't use trace tools because of company security.

tim.smith | 2022-05-11 13:19:36 UTC | #19

It definitely seems like it's something environmental inside the SSIS application since it works correctly from postman and your powershell script. My guess is still on the path being incorrect. I found that a request made to the API host without any path (https://api.usw2.pure.cloud/ being the full request URI) will generate a trace message like the one I saw for the correlation ID above. I don't know that the API enforces headers so strictly, but sending the Accept: application/json header to an endpoint you're expecting to download a CSV file doesn't seem right. Maybe don't send that header at all; I'm pretty sure that header is never required. Also, outgoing request shouldn't have that content-type header set; a GET request doesn't have a JSON payload.

It would be a good idea to engage support for SSIS to see if they can help figure out why it's making the request to download incorrectly; configuration of the 3rd party software is outside of what I can help with.

Ron_Zablocki | 2022-05-11 16:26:01 UTC | #20

In the GET call we're using, the URI that's being generated is: https://api.usw2.pure.cloud/api/v2/downloads/a04b2902b3f3a309

Pasting this URI manually into a web browser automatically generates a download prompt for the CSV file. But this appears to be a redirect URL and not a direct download URL. Is there a direct download URL we should be attempting to retrieve?

Is this URI path not the correct path to use? I removed the Accept and Content-Type headers for the GET call, but still get the same result.

Jerome.Saint-Marc | 2022-05-11 17:16:04 UTC | #21

Hello,

As Tim posted above, you can add issueRedirect=false to the url to get the download url (instead of an HTTP redirect). i.e. https://api.usw2.pure.cloud/api/v2/downloads/a04b2902b3f3a309?issueRedirect=false The request to the /api/v2/downloads endpoint must have the Authorization header set with the Genesys Cloud bearer/access token (e.g. Authorization: Bearer xyxyxyxyxyxyxyxyxyxyx).

This will tell the Genesys Cloud server to answer with a 200 OK - with the Amazon S3 url in the response body (instead of getting a 303 See Other redirect). You can then retrieve the file from the provided Amazon S3 url. Do NOT set/define an Authorization header for this request which is sent to Amazon S3. If your code is invoking the Amazon S3 url and you have set an Authorization header, Amazon will reject it with a 400.

Regards,

James_DiBernardo | 2022-05-12 00:09:03 UTC | #22

Just as a follow up . I think Ron and I agreed to use the PowerShell script and just pass variables into it from the SSIS package that would be a foreach in loop from a string of ContactListID's.

Once i figured out how to refresh the Bearer token (Using the logic from this OAuth Client Credentials Login Flow incorporating that first, then calling the $bearer in the later POST and GETS, to eventually save off the CSV.

The Kingsway plugin might work if we kept tinkering, but PowerShell seams to solve the need.

I am curious if there is a better method for the Wait timer of 5 sec's maybe based on a response from the API to say the link is ready? Any ideas there would be helpful...

# STEP 1 - Connect and refresh Bearer Token. -- https://developer.genesys.cloud/authorization/platform-auth/guides/oauth-client-credentials-guide#obtain-an-access-token
$environment = "usw2.pure.cloud";
$id = "XXXXXxxxxxXXXXX";
$secret = "YYYYYyyyyyYYYYY";

$auth  = [System.Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes("${id}:${secret}"))
$response = Invoke-WebRequest -Uri "https://login.${environment}/oauth/token" -Method POST -Body @{grant_type='client_credentials'} -Headers @{"Authorization"="Basic ${auth}"}
$responseData = $response.content  | ConvertFrom-Json

#Wrtie the Bearer token to a variable. 
$bearer = "Bearer " + $responseData.access_token 
# write-host $bearer

##Variables## 
#Args from SQL
# $contactlistid = $arg[0]
$contactlistid = "1fac47b5-82a9-4092-b03f-204b59195f02"

$FOLDER = 'C:\Temp\GENESYS\' #Include the last '\'

$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add("Accept", "application/json")
$headers.Add("Content-Type", "application/json")
$headers.Add("authorization", $bearer)
$POSTContactlist_EXPORT = 'https://api.'+ $environment +'/api/v2/outbound/contactlists/' + $contactlistid + '/export'
$GETContactList_EXPORT = 'https://api.'+ $environment +'/api/v2/outbound/contactlists/' + $contactlistid + '/export?download=false'
$GetContactList_NAME = 'https://api.'+ $environment +'/api/v2/outbound/contactlists/' + $contactlistid + '?includeImportStatus=false&includeSize=false'

# Get the name of the ContactList from the ID
$GETResponse_stgName = Invoke-RestMethod $GETContactList_NAME -Method 'GET' -Headers $headers
$GETResponse_NAME = $GetResponse_stgName.name
#write-host $GETResponse_NAME
$GETFILENAME = $FOLDER + $contactlistid +'_'+ $GETResponse_NAME +'.csv'
#write-host $GETFILENAME

#STEP 2 - POST - Initiate the export of a contact list
$POSTresponse = Invoke-RestMethod $POSTContactlist_EXPORT -Method 'POST' -Headers $headers

#see the output to make sure.
# $POSTresponse | ConvertTo-Json

#...give it a few to make the download link
Start-sleep -seconds 5

#STEP 3 - GET - Get the URI of a contact list export
$GETresponse = Invoke-RestMethod $GETContactList_EXPORT -Method 'GET' -Headers $headers
#see the output to make sure.
# $GETresponse | ConvertTo-Json

##Using the JSON URI Send its output to a file. 
Invoke-RestMethod -URI $GETresponse.uri -outfile $GETFILENAME -Headers $headers

tim.smith | 2022-05-12 13:17:31 UTC | #23

James_DiBernardo, post:22, topic:14673
I am curious if there is a better method for the Wait timer of 5 sec's maybe based on a response from the API to say the link is ready?

There is a notification event that's emitted when the export is ready: v2.users.{id}.outbound.contactlists.{id}.export. To use it, you would first create a channel, open the WebSocket connection, and subscribe to the export topic. Then you would initiate the export and wait for the notification to come in, then proceed with downloading the file.

However, I wouldn't recommend that in your use case since you have a run once short-lived process; setting up a WebSocket is overkill and is much more computationally expensive than making a couple API requests. Just make sure you have robust logic to poll on an interval (every 5-10 seconds is good) and abort the process after a few minutes to avoid an infinite loop. Exports generally shouldn't take more than a few seconds, but are variable and dependent on the size of the list being exported.

Ron_Zablocki | 2022-05-12 20:02:48 UTC | #24

Thanks for your assistance Tim and Jerome, I think we can now mark this thread as resolved. We’ve been able to successfully integrate PowerShell with Genesys command lines into SSIS. We’ve also been able to automate and parameterize PowerShell so that we can process multiple Contact Lists within a SSIS package.

system | 2022-06-12 20:02:58 UTC | #25

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.

This post was migrated from the old Developer Forum.

ref: 14673