Load images into Sql Server 2005
Hi There I have not had much luck finding info in BOL for tsql or SSIS that tells one how to load images on the file system into sql server 2005. All i have really been able to find is that IMAGE data type will not be used in future and that one should use a varbinary(max) data type. I am thinking of using a for each file loop in SSIS, but then how do i load the images (.tif) into a sql server database table ? Perhaps i need to use a sql task with the filepath , or an active x script. Anyway if anyone knows how i can load images from the file system into sql server 2005, please let me know. Thanx
June 5th, 2006 2:25pm

Hi, U can use Activex Script. But it is better to use Script task.
Free Windows Admin Tool Kit Click here and download it now
June 5th, 2006 5:02pm

You should look at the Import Column component. http://msdn2.microsoft.com/en-us/library/ms141262.aspx All you need is a source file or table, with a column containing the filenamesyou wish to insert. SSIS will then load each named file into the data flow, from whence you can insert it to the database as normal. Choice of the best data type for storing images would be better discussed on the SQL Server engine forum. Donald
June 5th, 2006 5:38pm

Hi Donald Would a for each file loop not work better, as a source file or table is not realistic considering there are over 9 million flat files. I am not sure i am following you, import column requires a data source, if i point a flat file source to a image file it gets very confused with randon columns and junk data. I am not sure how to feed the flat file image to the import column task? I have figured out another way to do it. But i am also interested in your suggestion. Thanx
Free Windows Admin Tool Kit Click here and download it now
June 6th, 2006 12:27pm

That is indeed a lot of files. Actually it is so many that you may want to contact me offline to discuss options. DonaldDotFarmerAtMicrosoftDotCom will get me. One way to generate a suitable file as a source, may be to pipe a dir command to file and then parse the result using SSIS - either using a flat file source, or a script component. From there it would be relatively simple to implement your scenario. Donald
June 8th, 2006 8:22am

Thanx Donald I am gonna try a couple of things and i will get back to you.
Free Windows Admin Tool Kit Click here and download it now
June 13th, 2006 1:09pm

The TextCopy utility is a great way to load binary data into BLOB fields. I am calling the util from a .bat file. Its another option anyway.... Check out this article http://www.mssqlcity.com/Articles/KnowHow/Textcopy.htm
August 25th, 2006 6:28pm

Dietz wrote: Hi There I have not had much luck finding info in BOL for tsql or SSIS that tells one how to load images on the file system into sql server 2005. All i have really been able to find is that IMAGE data type will not be used in future and that one should use a varbinary(max) data type. I am thinking of using a for each file loop in SSIS, but then how do i load the images (.tif) into a sql server database table ? Perhaps i need to use a sql task with the filepath , or an active x script. Anyway if anyone knows how i can load images from the file system into sql server 2005, please let me know. Thanx the t-sql OPENROWSET BULK statement is designed to import files into sql server: http://msdn2.microsoft.com/en-us/library/ms190312.aspx this statement can then be used in an execute sql task. i suggest that you consult with the t-sql forum if you need assistance with this statement.
Free Windows Admin Tool Kit Click here and download it now
August 26th, 2006 9:22am

Here is an example of importing images with SSIS 2008 (Import Column and Script Component): http://microsoft-ssis.blogspot.com/2011/08/importing-images-with-ssis.html Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
August 1st, 2011 3:19am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics