I am getting weird errors from both the ODBC drivers and the Devart SSIS Component for Salesforce. Being that we are using the SSIS in our project, I only included the ODBC issue to illustrate the problem occurring in both situations. (We purchased both)
SSIS Dta flow component V1.7.672
This is the issue for ODBC:
THIS WORKS: (DSF=Linked server)
SELECT *
FROM [dsf]...[Account] AS A
WHERE A.Name='abc'
THIS DOES NOT:
SELECT
U.LastName,
U.FirstName,
A.Name AS Account_Name,
O.Vapor_Thermal__c,
O.Project_ID__c
FROM [dsf]...[Opportunity] AS O
JOIN [dsf]...[Account] AS A
ON A.Id=O.AccountId
JOIN [dsf]...[User] AS U
ON U.Id=O.OwnerId
WHERE A.Name='abc'
OLE DB provider "MSDASQL" for linked server "dsf" returned message "[Devart][ODBC][Salesforce]Unknown column 'Col1050'".
Msg 7320, Level 16, State 2, Line 46
Cannot execute the query "SELECT "Tbl1003"."Id" "Col1050","Tbl1003"."Name" "Col1051" FROM "Account" "Tbl1003" ORDER BY "Col1050" ASC" against OLE DB provider "MSDASQL" for linked server "dsf".
FOR SSIS ( This gives me a preview correctly)
SELECT
F.Pursuit_Line__C,
F.Year__C,
F.Price_Effective_Date_Ramp_Down__C,
F.Sell_Price_Per_Pc__c,
F.Take_Rate__C,
F.Ramp_Down__C,
F.of_Price_Ramp_down_Applies__c
FROM "Pursuit_Line_Forecast__c" AS F
JOIN "Pursuit_Line__c" AS P
ON P.id=F.Pursuit_Line__c
JOIN "Opportunity" AS O
ON O.id=P.Pursuit__c
WHERE F.LastModifiedDate >='<@User::YesterdayDate>'
and F.LastModifiedById ='<@User::ExcludeUser>'
AND O.Channel__C='OE'
WHEN I run the SSIS TASK :
I Get :
[Devart Salesforce Source] Error:
= '005W000000392MyIAI')) AND (Pursuit__r.Channel__C = 'OE')) AND
^
ERROR at Row:1:Column:282
Didn't understand relationship 'Pursuit__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.
[Devart Salesforce Source [37]] Error: Devart.Data.Salesforce.SalesforceException (0x80004005):
= '005W000000392MyIAI')) AND (Pursuit__r.Channel__C = 'OE')) AND
^
ERROR at Row:1:Column:282
Didn't understand relationship 'Pursuit__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.
at Devart.Data.r.bk7(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
at Devart.Common.r.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3, Boolean A_4)
at Devart.Common.r.a(CommandBehavior A_0, Boolean A_1)
at Devart.Common.r.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at Devart.SSIS.SqlShim.SourceComponent.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper100 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer100[] buffers, IntPtr ppBufferWirePacket)
if I copy the code above and use it in ODBC it works WITHOUT the WHERE clause. If i keep the where clause (Channel__c) then I receive the same error as the ODBC error on top.
This is the error (same as copypasted above)
This works in SSIS : which is weird because other WHERE clauses aren't working
THIS DOES NOT WORK IN ODBC
