Data Factory - SQL Query to Salesforce with dynamic content

Question

Data Factory - SQL Query to Salesforce with dynamic content

Bakar, Cem 281

I have a pipeline on Data Factory with two modules.

Lookup, which gets the last inserted date from SQL database.

Copy data, using the result from the lookup to execute a select query.

Query:

SELECT * FROM Event WHERE LastModifiedDate IS NOT NULL AND LastModifiedDate < @activity('LastModifiedDate-Event').output.firstRow.LastModifiedDate Limit 10

No matter how I restructure the query I get variety of errors.

Failure happened on 'Source' side. ? ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [22018] [Microsoft][Support] (40550) Invalid character value for cast specification.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [22018] [Microsoft][Support] (40550) Invalid character value for cast specification.,Source=Microsoft Salesforce ODBC Driver,'

SELECT TOP 1000 * FROM Event WHERE LastModifiedDate IS NOT NULL AND LastModifiedDate < @activity("LastModified-Event").output.firstRow.LastModifiedDate ORDER BY LastModifiedDate DESC
Failure happened on 'Source' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][Salesforce] (120) SOQL_FIRST mode prepare failure: SOQL error: [Microsoft][SQLEngine] (31480) syntax error near 'SELECT TOP 1000<<< ??? >>> * FROM Event WHERE LastModifiedDate ISÂ NOTÂ NULL AND LastModifiedDate < @activity("LastModified-Event").output.firstRow.LastModifiedDate ORDER BY LastModifiedDate DESC'. SQL error: [Microsoft][SQLEngine] (31480) syntax error near 'SELECT TOP 1000 * FROM Event WHERE LastModifiedDate ISÂ NOTÂ NULL<<< ??? >>> AND LastModifiedDate < @activity("LastModified-Event").output.firstRow.LastModifiedDate ORDER BY LastModifiedDate DESC'.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][Salesforce] (120) SOQL_FIRST mode prepare failure: SOQL error: [Microsoft][SQLEngine] (31480) syntax error near 'SELECT TOP 1000<<< ??? >>> * FROM Event WHERE LastModifiedDate ISÂ NOTÂ NULL AND LastModifiedDate < @activity("LastModified-Event").output.firstRow.LastModifiedDate ORDER BY LastModifiedDate DESC'. SQL error: [Microsoft][SQLEngine] (31480) syntax error near 'SELECT TOP 1000 * FROM Event WHERE LastModifiedDate ISÂ NOTÂ NULL<<< ??? >>> AND LastModifiedDate < @activity("LastModified-Event").output.firstRow.LastModifiedDate ORDER BY LastModifiedDate DESC'.,Source=Microsoft Salesforce ODBC Driver,'

Sorry for the giant images. Please let me know if you see any issues with the query.

Accepted answer

1 additional answer

Your answer

Answer 1

Vaibhav Chaudhari 38,916 Volunteer Moderator

Could you try below like syntax -

select * from data_source_table where LastModifytime > '@{activity('LookupOldWaterMarkActivity').output.firstRow.WatermarkValue}'

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Bakar, Cem 281 Reputation points

2021-01-14T14:03:54.257+00:00

Hello anonymous userChaudhari ,
Thank you for helping.

I believe you need to skip the inner quotations around LookupOldWaterMarkActivity but I run the way you suggested. It failed with the following error after +11minutes.

Failure happened on 'Source' side. ErrorCode=UserErrorUnclassifiedError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Odbc Operation Failed.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [22018] [Microsoft][Support] (40550) Invalid character value for cast specification.,Source=Microsoft Salesforce ODBC Driver,'
Vaibhav Chaudhari 38,916 Reputation points Volunteer Moderator

2021-01-14T14:17:07.803+00:00

I guess, the syntax of the query is correct. I have used it before while pulling data from SQL and copied this query from below article

https://learn.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-portal#create-a-pipeline

Maybe, there is need of data type conversion or there are some invalid dates..
Nasreen Akter 10,811 Reputation points Volunteer Moderator

2021-01-14T15:26:08.84+00:00

Hi @Bakar, Cem , I agree with anonymous userChaudhari. The syntax is correct what he suggested. Best way to check your sql query would be by checking the value what you get after the lookup and put the value directly in the query (you will get the lookup value after executing the lookup activity --> output icon) and run it in SQL server to see if it brings result correctly or not. Thanks!
Bakar, Cem 281 Reputation points

2021-01-14T16:57:47.563+00:00

Hi @Nasreen Akter ,
Thank you for jumping in.

I have tried this select * from data_source_table where LastModifytime > '2020-01-01T08:34:34.45' which worked. But as anonymous userChaudhari mentioned I am looking into casting the call.

There is change on date type between Salesforce and SQL. At Salesforce the data looks like this and its type is Datetime

When I pull that onto my SQL on Azure its converted into datetime2(0) - which looks like this;

When I run the lookup module to get the last entry - I try to format that.. which I have tried casting in different formats but so far nothing worked.
Bakar, Cem 281 Reputation points

2021-01-15T04:10:01.45+00:00

You were right anonymous userChaudhari - that syntax was correct. After that I focused on how the date parameters was passing around.
The mistake I was making giving the date field in the same format back to salesforce as it was pulled.

Thank you all for your help!
@Viorel
@Nasreen Akter

Answer 2

Viorel 122.5K

One of the errors is shown for TOP. Try adding parentheses: ‘SELECT TOP(1000)…’.

Another error is about “ISÂ NOTÂ NULL”. Maybe this “Â” denotes a special space. Try removing and retyping this fragment.

Also make sure that the compared values represent valid date. Maybe even try some investigations with literal dates, such as “SELECT … AND LastModifiedDate < ’2020-12-17 09:31:26’”. Or start with the simplest query that works (SELECT * FROM Event), then add other parts gradually.

Bakar, Cem 281 Reputation points

2021-01-14T13:18:15.9+00:00

Hello @Viorel ,
Thank you for your answer.

I have tested variety of things. I did not want to fill the page with error messages :)

SELECT * FROM Event LIMIT 1000 works.
However, when I add the dynamic content, it breaks everything. I need to execute the statement with WHERE clause. SELECT TOP 1000 * FROM Event WHERE LastModifiedDate < @activity('LastModifiedDate-Event').output.firstRow.LastModifiedDate

Thanks again,

Share via

Data Factory - SQL Query to Salesforce with dynamic content

1 additional answer

Your answer