Azure Data Factory HubSpot Connector v2 fails when HubSpot returns empty string ("") for DateTime property notes_next_activity_date

Question

Azure Data Factory HubSpot Connector v2 fails when HubSpot returns empty string ("") for DateTime property notes_next_activity_date

Neel Prajapati 0

Hello Microsoft Team,

I believe I have found a reproducible issue with the Azure Data Factory native HubSpot Connector (v2).

I have spent several days investigating this issue and created a minimal reproducible example. Before opening a paid support request, I would like to confirm whether this is a known connector limitation, a bug, or if there is any supported workaround.

Environment

Azure Data Factory

Native HubSpot Connector (v2)

Azure SQL Database

Copy Activity

AutoResolve Integration Runtime

Problem

The Copy Activity fails when the HubSpot contact property notes_next_activity_date contains an empty string ("").

The pipeline reads all HubSpot records successfully but fails before writing any rows into Azure SQL Database.

Error

Failure happened on 'Sink' side.

Column 'notes_next_activity_date' contains an invalid value ''.

String was not recognized as a valid DateTime.

Couldn't store <> in notes_next_activity_date Column.

Expected type is DateTime.

Copy Activity Statistics

Rows Read: 1315
Rows Copied: 0

This indicates the connector successfully retrieves all HubSpot contacts but fails during internal type conversion.

HubSpot API Response

For multiple contacts, HubSpot returns:

"notes_next_activity_date": ""

The HubSpot property metadata reports:

type = datetime

fieldType = date

hubspotDefined = true

Investigation Performed

I created a completely new Azure Data Factory pipeline and reproduced the issue with the smallest possible configuration.

I tested:

New pipeline

New HubSpot dataset

New Azure SQL dataset

New SQL table (Auto Create)

No ForEach activity

No dynamic parameters

No metadata-driven pipeline

No imported mapping

Auto Mapping

Default Type Conversion

Custom DateTime Format (yyyy-MM-ddTHH:mm:ss.fffZ)

Every test produced exactly the same result.

Observation

It appears the native HubSpot connector attempts to convert:

"" -> DateTime

instead of treating the empty string as NULL.

Because the exception occurs before any rows are written to SQL Server, it appears the failure happens during the connector's internal type conversion rather than during SQL insertion.

My Questions

Is this a known issue with the Azure Data Factory HubSpot Connector v2?

Is this expected behavior?

Is there any supported way to treat empty strings ("") returned by HubSpot as NULL for DateTime fields?

Is there any connector configuration, mapping option, or Copy Activity setting that can resolve this without switching to the REST connector?

I would appreciate confirmation from the Azure Data Factory engineering team or anyone who has encountered the same behavior.

Thank you.Hello Microsoft Team,

I believe I have found a reproducible issue with the Azure Data Factory native HubSpot Connector (v2).

I have spent several days investigating this issue and created a minimal reproducible example. Before opening a paid support request, I would like to confirm whether this is a known connector limitation, a bug, or if there is any supported workaround.

Environment

Azure Data Factory

Native HubSpot Connector (v2)

Azure SQL Database

Copy Activity

AutoResolve Integration Runtime

Problem

The Copy Activity fails when the HubSpot contact property notes_next_activity_date contains an empty string ("").

The pipeline reads all HubSpot records successfully but fails before writing any rows into Azure SQL Database.

Error

Failure happened on 'Sink' side.

Column 'notes_next_activity_date' contains an invalid value ''.

String was not recognized as a valid DateTime.

Couldn't store <> in notes_next_activity_date Column.

Expected type is DateTime.

Copy Activity Statistics

Rows Read: 1315
Rows Copied: 0

This indicates the connector successfully retrieves all HubSpot contacts but fails during internal type conversion.

HubSpot API Response

For multiple contacts, HubSpot returns:

"notes_next_activity_date": ""

The HubSpot property metadata reports:

type = datetime

fieldType = date

hubspotDefined = true

Investigation Performed

I created a completely new Azure Data Factory pipeline and reproduced the issue with the smallest possible configuration.

I tested:

New pipeline

New HubSpot dataset

New Azure SQL dataset

New SQL table (Auto Create)

No ForEach activity

No dynamic parameters

No metadata-driven pipeline

No imported mapping

Auto Mapping

Default Type Conversion

Custom DateTime Format (yyyy-MM-ddTHH:mm:ss.fffZ)

Every test produced exactly the same result.

Observation

It appears the native HubSpot connector attempts to convert:

"" -> DateTime

instead of treating the empty string as NULL.

Because the exception occurs before any rows are written to SQL Server, it appears the failure happens during the connector's internal type conversion rather than during SQL insertion.

My Questions

Is this a known issue with the Azure Data Factory HubSpot Connector v2?

Is this expected behavior?

Is there any supported way to treat empty strings ("") returned by HubSpot as NULL for DateTime fields?

Is there any connector configuration, mapping option, or Copy Activity setting that can resolve this without switching to the REST connector?

I would appreciate confirmation from the Azure Data Factory engineering team or anyone who has encountered the same behavior.

Thank you.

Peram Venkateswara Reddy 250 Reputation points Microsoft External Staff Moderator

2026-08-03T10:12:47.44+00:00

Hello @Neel Prajapati ,Thank you for the detailed investigation and for providing a clear minimal reproducible example.

Based on the behavior you've described, the issue appears to occur during the connector's internal data type conversion process. The HubSpot Connector v2 maps HubSpot datetime fields to the ADF DateTime type, and the error indicates that the connector is attempting to parse an empty string ("") as a DateTime value, which results in the conversion failure. The fact that all source rows are read successfully but zero rows are written further suggests that the failure occurs before data reaches the Azure SQL sink.

At this time, the public documentation for the HubSpot Connector v2 does not describe any connector setting, mapping option, or Copy Activity configuration that converts empty strings returned from HubSpot into NULL values for DateTime fields. The documented data type mapping indicates that HubSpot datetime values are handled as DateTime, but there is no documented null-handling behavior for empty string values.

Additionally, the HubSpot connector is currently supported for Copy Activity and Lookup Activity scenarios, and there is no documented capability within the native connector to override source-side type inference or apply per-column transformations before the conversion occurs.

From the information available, this behavior does not appear to be documented as an expected feature. However, I could not find any publicly available documentation, release notes, or published bug records confirming whether this is a known product issue or limitation of the HubSpot Connector v2.

Your observation is technically sound: if HubSpot legitimately returns "" for an optional datetime property such as notes_next_activity_date, treating that value as NULL rather than attempting a DateTime conversion would be a more resilient behavior.

Thank you again for providing the detailed reproduction steps and analysis. The information you've shared should be valuable for further review and for others who may encounter the same scenario.

Best regards,
Microsoft Support Team.
Neel Prajapati 0 Reputation points

2026-08-03T11:08:07.1766667+00:00
@Peram Venkateswara Reddy Thank you for the detailed explanation and for reviewing the investigation.

Based on your response, my understanding is:

The failure occurs during the HubSpot connector's internal DateTime conversion before the Copy Activity reaches the sink.

There is currently no documented Copy Activity, mapping, or connector configuration to treat empty strings ("") returned by HubSpot as NULL for DateTime properties.

Source-side transformations cannot be applied before this conversion occurs.

Could you please confirm one final point?

Should this behavior be considered a product limitation of the native HubSpot Connector v2, or is it an issue that should be reported to the product engineering team?

If it is a known limitation, I will implement an alternative approach (such as using the REST connector or preprocessing the data). If it is a product issue, could you please advise whether it can be escalated to the engineering team or whether there is an existing bug tracking this behavior?

Thank you for your assistance.

4 answers

Your answer

Neel Prajapati 0 Reputation points

2026-08-03T11:08:07.1766667+00:00

@Peram Venkateswara Reddy Thank you for the detailed explanation and for reviewing the investigation.

Based on your response, my understanding is:

The failure occurs during the HubSpot connector's internal DateTime conversion before the Copy Activity reaches the sink.

There is currently no documented Copy Activity, mapping, or connector configuration to treat empty strings ("") returned by HubSpot as NULL for DateTime properties.

Source-side transformations cannot be applied before this conversion occurs.

Could you please confirm one final point?

Should this behavior be considered a product limitation of the native HubSpot Connector v2, or is it an issue that should be reported to the product engineering team?

If it is a known limitation, I will implement an alternative approach (such as using the REST connector or preprocessing the data). If it is a product issue, could you please advise whether it can be escalated to the engineering team or whether there is an existing bug tracking this behavior?

Thank you for your assistance.

Answer 1

@Sina Salam

I understand that using Mapping Data Flow to replace empty strings with NULL before converting to DateTime would be a valid approach.

However, according to the Azure Data Factory documentation, the native HubSpot connector supports Copy Activity and Lookup Activity, but it is not available as a source for Mapping Data Flow.

Could you please clarify how Mapping Data Flow can be used with the native HubSpot connector in this scenario? If there is a supported approach, could you share the configuration or documentation?

My issue occurs when the connector reads notes_next_activity_date as an empty string ("") and fails during type conversion in Copy Activity.

Answer 2

Senthil kumar 1,900

Hi @Neel Prajapati

for your copy activity alter your database and add column constraints.

ALTER TABLE YourTable
ADD CONSTRAINT DF_YourDate DEFAULT (GETDATE()) FOR YourDateColumn;

or in your derived columns in before sink.

check is null then pass current date time or set default date time.

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-date-time-functions

iif(isNull(yourDate) || trim(yourDate) == "", toTimestamp('1900-01-01 00:00:00'), yourDate) ' default date time.

iif(isNull(yourDate) || trim(yourDate) == "", currentTimestamp(), yourDate) ' Current date time.

Thanks.

Neel Prajapati 0 Reputation points

2026-08-03T05:44:13.35+00:00

Thank you @Senthil kumar for your suggestion.

In this case, the failure occurs before the data reaches Azure SQL Database. The Azure Data Factory HubSpot connector throws a source-side type conversion error when HubSpot returns an empty string ("") for the notes_next_activity_date property. Because the Copy Activity fails before the sink operation begins, SQL default constraints are unfortunately never applied.

I'm trying to determine whether this behavior is a known limitation or a product issue with the native HubSpot connector. If you've encountered this specific source-side conversion problem before, I'd appreciate any additional insights.
Senthil kumar 1,900 Reputation points

2026-08-03T07:25:41.58+00:00

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-date-time-functions

Add a Derived Column before the Sink:

iif(isNull(yourDate) || trim(yourDate) == "", toTimestamp('1900-01-01 00:00:00'), yourDate) ' default date time.

iif(isNull(yourDate) || trim(yourDate) == "", currentTimestamp(), yourDate) ' Current date time.

this will check if its null set default or current datetime.
Neel Prajapati 0 Reputation points

2026-08-03T11:06:17.24+00:00

@Senthil kumar The failure occurs before the data reaches Azure SQL Database. The Azure Data Factory HubSpot connector throws a source-side type conversion error when HubSpot returns an empty string ("") for the notes_next_activity_date property
Senthil kumar 1,900 Reputation points

2026-08-03T12:17:48.6666667+00:00

this should be for hubspot connect not database.

Answer 3

Thank you @Sina Salam for your response.

I understand that using the REST connector is a viable workaround. However, my objective is to understand whether this behavior in the native Azure Data Factory HubSpot Connector v2 is considered a product defect.

The connector fails during source deserialization when HubSpot returns an empty string ("") for the notes_next_activity_date property, before any mapping or sink transformation can be applied.

Could you please clarify:

Has the Azure Data Factory team been able to reproduce this issue with the native HubSpot Connector v2?
Is this behavior a known limitation or has it been logged as a product bug?
If it has been logged, is there a bug ID or tracking number that can be shared?
Is a fix planned for a future release of the connector?

Since the HubSpot API can legitimately return empty values for optional properties, it would be helpful if the native connector treated "" as NULL for nullable DateTime fields instead of failing the entire copy activity.

Thank you for your assistance.

Answer 4

Sina Salam 31,296 Volunteer Moderator

Hello Neel Prajapati

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that your Azure Data Factory HubSpot Connector v2 fails when HubSpot returns empty string ("") for DateTime property notes_next_activity_date

The best method without overhead is to:

Use the Azure Data Factory REST connector for the affected HubSpot contacts load.
Retrieve the contacts from the HubSpot CRM API.
Land the unmodified API response as raw JSON in Azure Data Lake Storage Gen2.
Process all HubSpot pages using paging.next.link.
Convert notes_next_activity_date from "" to NULL before applying the DateTime data type.
Load valid timestamps into an Azure SQL datetime2 column.
Open an Azure Support request for Product Group investigation and permanent correction of the native HubSpot v2 connector.

The native HubSpot connector supports Copy Activity and Lookup Activity, but its documentation does not provide a setting for converting an empty DateTime string to NULL. The ADF REST connector supports JSON REST sources and pagination, making it the reliable lossless workaround for this scenario. - https://learn.microsoft.com/en-us/answers/questions/5596905/data-factory-copy-activity-is-failing-for-hubspot, https://learn.microsoft.com/en-us/answers/questions/1397849/how-to-connect-to-hubspot-in-adf-using-rest-connec

After extracting the contacts as raw JSON and normalizing the property before DateTime conversion, contacts with a blank notes_next_activity_date will be stored as SQL NULL, while valid timestamps will remain correctly typed. This restores the production load without waiting for Azure Support.

Azure Support is not required to implement this workaround. Support is required only to confirm the connector’s internal root cause, engage the Azure Data Factory Product Group, provide a defect tracking ID, and determine when the native connector will be corrected.

Use the following resources for implementation:

I hope this is helpful. Please! Do not hesitate to let me know if you have any other questions, steps or clarifications.

Please do not close the thread by upvoting and accepting the answer if any part of it is helpful.

Neel Prajapati 0 Reputation points

2026-08-03T05:27:46.64+00:00
Thank you @Sina Salam for your response.

I understand that using the REST connector is a viable workaround. However, my objective is to understand whether this behavior in the native Azure Data Factory HubSpot Connector v2 is considered a product defect.

The connector fails during source deserialization when HubSpot returns an empty string ("") for the notes_next_activity_date property, before any mapping or sink transformation can be applied.

Could you please clarify:

Has the Azure Data Factory team been able to reproduce this issue with the native HubSpot Connector v2?

Is this behavior a known limitation or has it been logged as a product bug?

If it has been logged, is there a bug ID or tracking number that can be shared?

Is a fix planned for a future release of the connector?

Since the HubSpot API can legitimately return empty values for optional properties, it would be helpful if the native connector treated "" as NULL for nullable DateTime fields instead of failing the entire copy activity.

Thank you for your assistance.
Neel Prajapati 0 Reputation points

2026-08-03T06:05:11.7166667+00:00

@Sina Salam

I understand that using Mapping Data Flow to replace empty strings with NULL before converting to DateTime would be a valid approach.

However, according to the Azure Data Factory documentation, the native HubSpot connector supports Copy Activity and Lookup Activity, but it is not available as a source for Mapping Data Flow.

Could you please clarify how Mapping Data Flow can be used with the native HubSpot connector in this scenario? If there is a supported approach, could you share the configuration or documentation?

My issue occurs when the connector reads notes_next_activity_date as an empty string ("") and fails during type conversion in Copy Activity.
Sina Salam 31,296 Reputation points Volunteer Moderator

2026-08-03T11:23:48.5133333+00:00
@Neel Prajapati

Thank you for your feedback and asking for clarification.

In addition to my response, the behavior you described is consistent with an internal type conversion occurring inside the native Azure Data Factory HubSpot Connector v2 before the sink or mapping stage is reached. Based on your testing, the connector attempts to deserialize an empty string ("") returned by HubSpot for a DateTime property into a .NET DateTime, resulting in the conversion failure before any rows are written to Azure SQL Database.

At present, Microsoft documentation does not describe any Copy Activity setting, schema mapping option, fault-tolerance configuration, or type conversion setting that converts empty strings to NULL for nullable DateTime fields in the native HubSpot connector. Likewise, the HubSpot connector is supported only for Copy Activity and Lookup Activity, so it cannot be used directly as a source in Mapping Data Flow to perform the conversion before deserialization.

Based on available documentation, there is no supported configuration that resolves this behavior while continuing to use the native HubSpot Connector v2. The practical workaround is to ingest the HubSpot data through the REST connector, stage the JSON response, replace empty strings with NULL, and then perform the DateTime conversion before loading the data into Azure SQL Database.

Regarding your specific questions:

Microsoft documentation does not currently identify this as a documented limitation or a known product bug.

No public bug ID or engineering tracking number is available.

There is no published information indicating that a fix has been scheduled.

Only Microsoft Azure Support internal can determine whether the Product Group has reproduced the issue, confirm whether it is a product defect or intended behavior, provide any available internal tracking ID, and communicate future plans for a fix.

Given that you have already produced a minimal reproducible example and eliminated common configuration issues, opening an Azure Support via your portal to request is the appropriate next step to allow the Azure Data Factory Product Group to investigate the connector's internal deserialization behavior.

Cheers.