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

Hi,

U can use Activex Script. But it is better to use Script task.

|||

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 filenames you 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

|||

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

|||

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

|||

Thanx Donald

I am gonna try a couple of things and i will get back to you.

|||

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

|||

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.