SSIS Component for Salesforce and ODBC Drivers issues

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Cloud Applications
Post Reply
abayd
Posts: 3
Joined: Mon 16 Apr 2018 13:33

SSIS Component for Salesforce and ODBC Drivers issues

Post by abayd » Tue 29 May 2018 16:17

Hello,

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.

Image


This is the error (same as copypasted above)



Image




This works in SSIS : which is weird because other WHERE clauses aren't working
Image


THIS DOES NOT WORK IN ODBC


Image

Shalex
Site Admin
Posts: 8132
Joined: Thu 14 Aug 2008 12:44

Re: SSIS Component for Salesforce and ODBC Drivers issues

Post by Shalex » Fri 01 Jun 2018 18:48

1. "Devart SSIS Data Flow Components for Salesforce" and "ODBC Driver for Salesforce" are two separate products. Could you please report the issue with "ODBC Driver for Salesforce" at viewforum.php?f=55 ? Also specify the link to this thread in the new report. Thank you.

2. With "Devart SSIS Data Flow Components for Salesforce" v1.7.672, I have tested the following query in a simple scenario (Devart Salesforce Source -> Flat File Destination):

Code: Select all

SELECT
	D.Name,
	D.Dname__c,
	E.Name
FROM "Dept__c" AS D 
	LEFT JOIN "Emp__c" AS E
	ON D.Deptno__c=E.Deptno__c
WHERE D.CreatedDate <='<@User::TODAY>'
	AND D.Dname__c='test name'
It works in both cases "Preview Data" and runtime within VS2015.
Please give us an additional information:
a) what does running "SSIS TASK" mean? How are you running your SSIS package to reproduce the error (in VS2015 OR in SQL Server Agent from SQL Server Management Studio OR in dtexec.exe, etc)?
b) try modifying your current query to localize the exact change in the query that leads to the error
c) does the issue persist with other custom objects?

Post Reply