I've been tasked to extract images saved in SQL Server (as varbinary) to flat files via t-sql. I haven't done ETL work for over a decade and I can't recall any other method of doing this via t-sql other than using sp_OACreate, sp_OAMethod etc.
Is there some new method offered that can handle this? Something more "reliable" and doesn't require turning on OLE automation procedures and doing all this crazy stuff?
This would be an on-going process. Not a one time run.
If you really must do it from T-SQL, and your SQL Server instance version is 2012 or more recent, You could use a FileTable. It relies on the FileStream feature, so you'll have to enable this first.
To keep it simple, a FileTable works mostly like a regular table, but the file content is stored in a FILESTREAM file group, and the files can be accessed directly with a UNC path. In fact, files can be read, created, modified or deleted through the UNC path. Permissions on UNC path are the same as the File Table. Ex: if DOMAIN\bob has SELECT permission, he will have read permission on the UNC path.
After the filetable is created, you could just load it with an INSERT like this:
INSERT INTO dbo.myFileTable(name, file_stream)
SELECT file_name,file_data
FROM dbo.OldTableWithFileContent;
Once this is done, you can get the files under \\<servername>\<MSSQLSERVER or instance name>. Exact path will depend on your FileStream configuration, but never try to access the local path defined in FileStream config, only through the UNC path managed by SQL Server.
Finally, if it's just for a one time export, you can drop the FileTable after files have been copied to their final destination.
