Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
curtismob
Helper IV
Helper IV

Changing Data Source from salesforce reports to SQL Server

We have a report and visuals created using salesforce reports as the source of data.  We would like to change the source to an

on-prem SQL server.  We have created the SQL Server query to mimic the columns and data from salesforce reports.  I added the SQL Server query as a new query to the pbix, I then tried copying the SQL Server query definition from the new query and replacing the salesforce reports "Source" query.   This appears to work because after clicking the green checkmark next to the source query box/window, data is returned.  We get the error when clicking "Apply" or "Close and Apply" from the Edit Query screen.

 

Error:

 

OLE DB or ODBC error: Type mismatch. (Exception from HRESULT:0x80020005 (DISP_E_TYPEMISMATCH)).

 

Anyone seen this error or tried to make a source change like this?

 

Thanks in advance,

@curtismob

5 REPLIES 5
ankitpatira
Community Champion
Community Champion

@curtismob In my opinion I don't think you will be able to do it that way since Salesforce and SQL Server are both different source connectors. I think easiest option will be to load sql query as a new query (like you have) -> Close&Apply -> then replace fields for all the visuals with fields from sql query and then delete original salesforce query. 

Thank you for the response @ankitpatira.  I will give your suggestion a try and let you know how it goes.

 

Regards,

@curtismob

@ankitpatira,

 

I tried your suggestion of replacing the fields in the visuals with the new query fields, unfortunately it did not work as well as I had hoped.  We will basicaly have to recreate the visuals.

 

Any other ideas about possibly changing the source from salesforce reports to SQL Server?

 

Thank you,

@curtismob

@ankitpatira and anyone else that is interested, I was able to successfully make the source change from salesforce reports to SQL Server.

 

- I created a SQL Server query and named each result column exactly the same as the salesforce reports source columns, as well as having the columns in the same sequence (not sure the sequence matters, but did it for good measure)
- Added my SQL Server query to the pbix
- Once the new SQL Server query was added, I clicked the "Data" icon beneath the "Report" icon on the left ribbon and verified the new SQL Server query column data types all matched the salesforce reports source column data types (I feel like this resolved the issue)

- I then went into "Edit Queries", copied the SQL Server query definition from the new query, replacing the salesforce reports "Source" query in source query box/window area

 

I clicked the green check mark, no errors, then clicked Close and Apply with no errors and the visuals stayed in tact.

One final note I wanted to share regarding changing this data source. 

 

Initially when trying to Close and Apply the source changes in the Query Editor, it would not allow it without signing in to salesforce.  At first, I didn't understand why since I had replaced the salesforce reports "= Source" settings with the SQL Server settings (query).

 

In the Query Editor (Edit Queries), I selected the SQL Server dataset/query in the left ribbon, then clicked "Advanced Editor" on the Home tab and copied the "let/in" settings.  Next I selected the salesforce report dataset/query in the left ribbon, clicked "Advanced Editor" on the Home tab and pasted the "let/in" settings from the SQL Server dataset/query, replacing the previous salesforce reports settings.

 

Best,

@curtismob

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.