SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Import documents into SQL using SSIS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alanmac
Starting Member

United Kingdom
26 Posts

Posted - 06/03/2014 :  08:26:54  Show Profile  Reply with Quote
Hi,

In my database table I have 2 columns. One holds the path to a file (FileName), and the other is for the file itself. What I need to do is read each FileName from the database, then go to the location and read that file into the database as an image by updating the current row.

Does anyone know of a resource that explains how to do this? I feel I'm getting close but the updated rows each have null values for the image.

Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 06/03/2014 :  08:32:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE	@SQL NVARCHAR(MAX);

SELECT	@SQL = N'UPDATE		mt
		SET		MyColumn = w.Content
		FROM		dbo.MyTable AS mt
		CROSS JOIN	OPENROWSET(BULK ' + QUOTENAME(MyPathColumn, N'''') + ', SINGLE_BLOB) AS w(Content)
		WHERE		mt.ID = ' + CAST(MyRowID AS NVARCHAR(12)) + ';'
FROM	dbo.MyTable
WHERE	MyRowID = 2;

EXEC	(@SQL);



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

alanmac
Starting Member

United Kingdom
26 Posts

Posted - 06/03/2014 :  09:09:57  Show Profile  Reply with Quote
Thanks for that, but it didn't work. The record I specified still has a null value for the image column.

Would there be a way to do this through an SSIS package? This is for a colleague who has been asked to design one to perform this task.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000