Data Gateway Add Connection API

[Saravanan]

I am trying to connect with a database inside Archer DB server using Archer OOB Flexible SQL Data Gateway Connector.  I am able to generate the session token, but getting error with Add Connection API. 

We are using Archer 6.7.00000.1184.  The same connection string is working through a data feed.  Kindly help me to resolve the issue.

Below is my JSON request and the error i am getting

API URL: http://xxx.xx.xx/RSAArcher/platformapi/DataGateway/Connections

JSON Request:

{
"TargetId": 374,
"Connection": {
"LocationAlias": "Test_DG1",
"DatagatewayConnector": {
"ConnectorType": "ArcherTech.Datasource.Content.GenericSql.GenericSqlDataSource",
"Properties": [
{
"Name": "dbProviderType",
"Value": "System.Data.SqlClient"
},
{
"Name": "connectionString",
"Value": "Data Source=ARCHER6-6\MSSQLXX;Initial Catalog=Test_DB_DataG;User ID=User12;Password=XXX;TransparentNetworkIPResolution=False"
},
{
"Name": "tableName",
"Value": "dbo.Table_1"
},
{
"Name": "idColumnName",
"Value": "Field_Id"
},
{
"Name": "idColumnType",
"Value": "smallint"
}
]
}
}
}

Error Response:

{"Message":"An error has occurred.","ExceptionMessage":"Object reference not set to an instance of an object.
","ExceptionType":"System.NullReferenceException","StackTrace":"
at ArcherApi.Controllers.ModuleDataGatewayController.Post(DataGateway moduleDataGateway)\r\n
at lambda_method(Closure , Object , Object[] )\r\n
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass6_1.<GetExecutor>b__3(Object instance, Object[] methodParameters)\r\n
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)\r\n---
End of stack trace from previous location where exception was thrown ---\r\n
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n
at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__1.MoveNext()\r\n---
End of stack trace from previous location where exception was thrown ---\r\n
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n
at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__5.MoveNext()\r\n---
End of stack trace from previous location where exception was thrown ---\r\n
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n
at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__15.MoveNext()"}

[Saravanan]

I tried to create one more Connection today,  i have updated the SQL settings to connect via IP Address.  But getting the same error, that No Data Source is found at the location.

Below are the steps i followed

• Create Session Token
• Call Add Connection API
• Call Add Field Maps API
• IISReset through CMD iisreset comment
• Searching in the Application with Search Filter Field Id greater than 1

Source Field	Type	Archer Field	Type
Field_Id	int	23030	Numeric
Field_Type	varchar(50)	23031	Text
Description	varchar(50)	23032	Text

Hereby posting my code

$base_url = "http://10.XXX.XX.XXX/RSAArcher"
$instance_name = "Archer"
$user_domain = ""
$username = "sysadmin"
$password = "XXXXX"
$login_url = $base_url + "/platformapi/core/security/login"
$body = '{"InstanceName":"' + $instance_name + '","Username":"' + $username + '","UserDomain":"' + $user_domain + '","Password":"' + $password + '"}'
$response_login = Invoke-RestMethod -Method Post -Uri $login_url -Body $body -ContentType "application/json" -SessionVariable $sess
$session_token = $response_login.RequestedObject.SessionToken
$session_token

$headersGET = @{}
$headersGET.Add("Authorization", "Archer session-id=" + $session_token)
$headersGET.Add("X-Http-Method-Override", "GET")

$headers = @{}
$headers.Add("Authorization", "Archer session-id=" + $session_token)

$body1 = '{
"TargetId": 375,
"Connection": {
"LocationAlias": "Test_DG2",
"DatagatewayConnector": {
"ConnectorType": "ArcherTech.Datasource.Content.GenericSql.GenericSqlDataSource",
"Properties": [
{
"Name": "dbProviderType",
"Value": "System.Data.SqlClient"
},
{
"Name": "connectionString",
"Value": "Data Source=10.XXX.XX.XXX;Initial Catalog=Test_DB_DataGateway;User ID=User2;Password=XXXX;TransparentNetworkIPResolution=False"
},
{
"Name": "tableName",
"Value": "dbo.Table_1"
},
{
"Name": "idColumnName",
"Value": "Field_Id"
},
{
"Name": "idColumnType",
"Value": "int"
}
]
}
}
}'

$api_url = $base_url + "/platformapi/DataGateway/Connections"
$results = Invoke-RestMethod -Method POST -Uri $api_url -Body $body1 -Headers $headers -ContentType "application/json" -WebSession $sess
$results

$body2='{
"TargetId": 375,
"Connections": [{
"Name": "Test_DG2",
"FieldMapping": [{
"SourceFieldId": "Field_Id",
"ArcherFieldId": 23030
}, {
"SourceFieldId": "Field_Type",
"ArcherFieldId": 23031
}, {
"SourceFieldId": "Description",
"ArcherFieldId": 23032
}]
}]
}'

$api_url2 = $base_url + "/platformapi/DataGateway/FieldMaps"
$results = Invoke-RestMethod -Method POST -Uri $api_url2 -Body $body2 -Headers $headers -ContentType "application/json" -WebSession $sess
$results

[CaylorNorris]

I didn't see this until I copied the code out of RSA Link, but the property and value have  unneeded spaces that have caused folks problems before. There shouldn't be a space after the colon. 

[Saravanan]

David Freeman‌Caylor Norris‌LokeshKumar polamarasetty‌ Thanks for all your inputs.  It started working suddenly working after 6.8 upgrade.