Export Column: no path appeared to export images

Question

User991566988 posted

Hi

I've dragged and dropped OLE DB Source and Export Column in an integration service project in visual studio 2019, and make the connection successfully to export Images from Image datatype column to some folder in Windows.

So I put this SQL command text in OLE DB Source:

DECLARE @path VARCHAR(100) = 'C:\Images\' 

SELECT [image]
,@path+CAST([Id] as varchar(100)) AS Path
FROM [MyDB].[dbo].[SampleTable]

But when I double click on Export Column, The 'Extract Column' has 'image' value but 'File Path Column' has no value!

Why? and How to solve please?

Answer 1

User475983607 posted

The code shown looks fine.  If Id is null then the concatenation will return null.  I suspect you have other issues with the code.  Try basic troubleshooting and debugging.  Execute the query in SSMS to see what happens.

IF OBJECT_ID('tempdb..#SampleTable') IS NOT NULL
	DROP TABLE #SampleTable

CREATE TABLE #SampleTable (Id INT, [image] VARCHAR(10))

INSERT INTO #SampleTable (Id, [Image])
VALUES(1, 'test 1'), (2, 'test 2'), (null, 'test 3')

DECLARE @path VARCHAR(100) = 'C:\Images\' 

SELECT [image]
,@path + CAST([Id] as varchar(10)) AS Path
FROM #SampleTable