August 23, 2011 at 12:44 am
Hi,
I have JPG files in a directory and the filename equals the natural key of the item in the picture. How can I load the data into a table
with either TSQL or SSIS?
D:\Directory1\APU201105.jpg
D:\Directory1\APU201106.jpg
D:\Directory1\APU201107.jpg
...
should load to the table
Create table test
(
Item varchar(20),
Picture image
)
Item = 'APU201105'
image = image data
.....
Ville
August 23, 2011 at 1:51 am
This was removed by the editor as SPAM
August 23, 2011 at 2:15 am
OK, thanks, I'll use varbinary(max) instead
I was asked to store the data in DB to avoid possible access violations with SSRS usage...
I have to admid I'm new to this kind of varbinary data in db
The idea is to show the covers of the magazines above the chart bars in SSRS reports. I really don't know how this should be done:
Only store the URL in DB
FILESTREAM
OPENROWSET commands
and should I use TSQL or SSIS (import column) when/if trying to create the data in db table
Ville
August 23, 2011 at 2:05 pm
How large are the image files? Varbinary(MAX) has a size limit of 2GB.
The easiest way to load is via ADO.NET. There are several examples on how to do that in msdn.
August 24, 2011 at 1:02 am
Only standard Varbinary(MAX) has limit of 2GB. FileStream does not have. Please consider using Filestream Varbinary(MAX) if you have relatively larger size of image file(1 MB) else it can be standard one.
There are certain things to be noted on cluster environment. Please revisit the msdn.
August 24, 2011 at 3:06 am
I did this with SSIS and loaded the example pictures to a varbinary column with 'Import Column' transformation. First there is a CMD that reads all JPG filenames into a txt file and that again is a source for the Data Flow.
The JPS's are going to be small - about <100KB. So I figured, based on what I have read, that no FILESTREAM techniqueas are needed here regarding the select efficiency from the DB.
Just in case, can I add FILESTREAM filegroup to an existing database?
V
August 24, 2011 at 4:17 am
This was removed by the editor as SPAM
August 24, 2011 at 4:17 am
If you are not using FILESTREAM, then you do not need to add file gorup.
It is always better not to use FILESTREAM for smaller size of image for streaming performance.
August 25, 2011 at 11:44 am
Hi,
Its always better to store only the path i.e. file location or URL of the image rather than the image itself by using FILESTREAM.
So go by VARCHAR() to store the image path.
Regards,
Durga
August 25, 2011 at 1:00 pm
Hi,
like I said earlier, I'm a newbie with varbinary-data in sql server, but what I've read recently, I wouldn't be so tight with this. When the images are small (< 100k) like in my case, it seems to be good practice to store them in the DB (No FileStream technique is needed).
I implemented this with SSIS and works perfectly OK and the test reports as well
V
September 2, 2011 at 1:37 pm
Don't know if this helps, but here is the insert statement for T-sql for inserting a LOB (large object).
--Note: file path is in relation to the database server, not where you run the script from (say, your workstation)
INSERT INTO MiscValueLOB
(LOBName, LOBDesc, MiscLOB)
SELECT 'DUMMY' as LOBName,'This is a test' AS LOBDesc, * FROM OPENROWSET(BULK N'C:\raptor_image.jpg',SINGLE_BLOB) as MiscLOB
September 4, 2011 at 10:54 pm
Thanks, good to know the T-Sql way as well even though I already implemented this with SSIS
v
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply