you have a table with 2 fields:
FilePath -> this field contains address of local image file path
FileImage -> this field contains the image exactly, type of this field is image(sql server database)
Now you want to update FileImage field with files that are exists in exact local paths.
can you do it with SSIS? How?
Use Data Flow Task , Import Column Transformation , OLE DB Command tranformation
I will explain details on this example:
1- Create a table in sql server database with this structure:
CREATE TABLE [dbo].[Images](
[FilePath] [varchar](500) NOT NULL,
[FileImage] [image] NULL
as I said before,
FilePath contains local path of files
and Document must contain images files exactly.
2- Create a SSIS folder in c:\ , and copy 3 images there, rename them as 1.jpg , 2.jpg, 3.jpg
3- Insert 3 records in Images table with this data:
4- Create a SSIS package, Add a Data Flow Task, Add an OLE DB Source and fetch data from this table.
5- Add an Import column transformation
6- double click on import column transformation , Advanced Editor for Import Column window will open. go to Input columns tab, and tick FilePath Column.
7- go to Input and Output properties tab, under Import column output , under output column add new column and name it as myImage. Note that data type of this column must be DT_IMAGE .
8- copy ID property of myImage column, now go under Import column input, under input column, select FilePath. paste the copied id value in FileDataColumnID value here. and hit ok.
9- Add an OLE DB Command after Import Column.
10- double click on it, in first tab select connection manager same as oledb source connection manager.
11- in Component properties tab, in SqlCommand type this statement :
update Images set FileImage=? where FilePath=?
12- in column mapping tab, set these mappings:
myImage —-> Param_0
FilePath —–> Param_1
and then hit ok.
13- Run the package.
In above example, Import Column will read image files from local path and add them as new column to data flow. OLE DB Command is responsible for update FileImage columns with the images files from the column which comes from import column.