HELP::COPY DATA::JPEG BINARY LOAD FAIL

[Element115]

CONTEXT:

Table T0 schema is:

CREATE TABLE EcoCount.dbo.MUP_Status (
	statusId int IDENTITY(0,1) NOT NULL,
	display varchar(100) COLLATE Latin1_General_CI_AS NULL,
	lastAccessed datetime2(0) NULL,
	currentLayout varchar(100) COLLATE Latin1_General_CI_AS NULL,
	MUP_screenshot varbinary(MAX) NULL
)

ISSUE:

0__config Copy data activity to extract from on-prem SQL Server and load into a NEW lakehouse table.

1__validate pipeline == success.

2__run pipeline == success.

3__open lakehouse view, navigate to table T0 and visually inspect the 1,000 rows preview --> all data in column MUP_screenshot varbinary(MAX) NULL is the same, ie all rows contain the same data when it should be different from row to row, and there is an error message that says 'Couldn't load table' even though the GUI is showing 1,000 rows, and the pipeline is reporting that the Activity ran successfully. Also, the lakehouse table preview is missing the column name for only this particular column. Also table T0 does not appear in the SQL endpoint.

DISCUSSION:

I suspect this is linked to the column data type being BINARY.  For some reason, you can have BINARY type column in SQL Server but not in a lakehouse delta table.  When I use a dataflow gen2, I convert this BINARY type to text and all is ok.  But you can't do that with a straight Copy data activity.

Any suggestions other than using a DFg2?

[Element115 · Accepted solution]

OK well... once you realize how the engine processes all this under the hood it turns out that in the Copy data --> Source --> Query you should write your T-SQL like so:

SELECT 
		statusId
	,	CAST(lastAccessed AS date)	AS [Date]
	,	lastAccessed				AS Refresh_Date
	,	display						AS Display
	,	currentLayout				AS Layout
	,	MUP_screenshot
FROM 
	MUP_Status

and NOT like this:

SELECT 
		statusId
	,	CAST(lastAccessed AS date)	AS [Date]
	,	lastAccessed				AS Refresh_Date
	,	display					AS Display
	,	currentLayout				AS Layout
	,	CAST(MUP_screenshot			AS varchar(MAX))
FROM 
	MUP_Status

In other words, let the BINARY type come in as is, no CAST needed, and the lakehouse view displays the data as VARCHAR, while the SQL endpoint displays it in hexadecimal.

Then transform later into text inside a dataflow before showing the JPEG data in a Power BI report page.

[v-cboorla-msft]

Hi @Element115 

Thanks for using Microsoft Fabric Community.

Glad that you were able to find some insights and thank you for sharing the same with the community as it can be helpful to others. Please continue using Fabric Community for further queries.

Thanks.