Copy data from SQL and post it into Hubspot API (for company) using ADF

Hi everyone,

I am new to ADF and seeking help in a scenario where I need to copy data from SQL database and feed it into Hubspot for company.

Below is the query I am using to convert it into Json and the dump into Blob.

SELECT top 10

name AS [properties.name],

new_businesswebsite AS [properties.domain],

new_city AS [properties.city],

new_emergencycontactmobile AS [properties.phone]

FROM

[zdev].CRM_Accounts

FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

This is the preview of data

Preview data

Linked service:DATA_WAREHOUSE

Object:JSON_F52E2B61-18A1-11d1-B105-00805F49916B

{“properties”:{“name”:”Al Duaa Tours Limited (Accept No Bookings)”,”domain”:”www.alatours.com“,”city”:”Renfrew”,”phone”:”07921005″}},{“properties”:{“name”:”TEST”,”city”:”Jersy”,”phone”:”01239″}},{“properties”:{“name”:”American Express”}},{“properties”:{“name”:”Reizen De Cauwer”,”city”:”Sint Niklaas”}},{“properties”:{“name”:”TEST”,”city”:”Jersy”}},{“properties”:{“name”:”SPORTSCORP”,”city”:”BOLTON”}},{“properties”:{“name”:”HIS CHARIOT TRAVELS “,”city”:”AJA”}},{“properties”:{“name”:”TEST”,”city”:”Jersy”,”phone”:”0156789″}},{“properties”:{“name”:”Cruise Village (Accept No Bookings)”,”domain”:”https://www.thecruisevillage.com“,”city”:”Blackpool”,”phone”:”07976926998″}},{“properties”:{“name”:”Riotravel Turismo 5661 “,”city”:”Rio de Janeiro”}}

In the next step I am trying to use foreach loop activity to parse data into hubspot with the relevant columns.

Settings

Items

@activity(‘Customer Data Copy’).output

Activities: Web

For Web activity below are settings.

URL: is not a problem

Method: Post

Body:

{

“properties”: {

“name”: “@{item().properties.name}”,

“domain”: “@{item().properties.domain}”,

“city”: “@{item().properties.city}”,

“phone”: “@{item().properties.phone}”

}

Error:

Failure type: User configuration issue

Details: The function ‘length’ expects its parameter to be an array or a string. The provided value is of type ‘Object’.

Please suggest how to resolve this issue, or provide alternative optimized way to achieve this goal. There are around 50k rows that should be imported and api can’t be feed for much records at once.

Hi everyone, I am new to ADF and seeking help in a scenario where I need to copy data from SQL database and feed it into Hubspot for company. Below is the query I am using to convert it into Json and the dump into Blob. SELECT top 10 name AS [properties.name], new_businesswebsite AS [properties.domain], new_city AS [properties.city], new_emergencycontactmobile AS [properties.phone]FROM [zdev].CRM_AccountsFOR JSON PATH, WITHOUT_ARRAY_WRAPPER; This is the preview of data Preview dataLinked service:DATA_WAREHOUSEObject:JSON_F52E2B61-18A1-11d1-B105-00805F49916B {“properties”:{“name”:”Al Duaa Tours Limited (Accept No Bookings)”,”domain”:”www.alatours.com”,”city”:”Renfrew”,”phone”:”07921005″}},{“properties”:{“name”:”TEST”,”city”:”Jersy”,”phone”:”01239″}},{“properties”:{“name”:”American Express”}},{“properties”:{“name”:”Reizen De Cauwer”,”city”:”Sint Niklaas”}},{“properties”:{“name”:”TEST”,”city”:”Jersy”}},{“properties”:{“name”:”SPORTSCORP”,”city”:”BOLTON”}},{“properties”:{“name”:”HIS CHARIOT TRAVELS “,”city”:”AJA”}},{“properties”:{“name”:”TEST”,”city”:”Jersy”,”phone”:”0156789″}},{“properties”:{“name”:”Cruise Village (Accept No Bookings)”,”domain”:”https://www.thecruisevillage.com”,”city”:”Blackpool”,”phone”:”07976926998″}},{“properties”:{“name”:”Riotravel Turismo 5661 “,”city”:”Rio de Janeiro”}} In the next step I am trying to use foreach loop activity to parse data into hubspot with the relevant columns. Settings Items @activity(‘Customer Data Copy’).outputActivities: Web For Web activity below are settings. URL: is not a problemMethod: PostBody:{ “properties”: { “name”: “@{item().properties.name}”, “domain”: “@{item().properties.domain}”, “city”: “@{item().properties.city}”, “phone”: “@{item().properties.phone}” }} Error:Failure type: User configuration issueDetails: The function ‘length’ expects its parameter to be an array or a string. The provided value is of type ‘Object’. Please suggest how to resolve this issue, or provide alternative optimized way to achieve this goal. There are around 50k rows that should be imported and api can’t be feed for much records at once. Read More

Cart

Cart