I want to import files (images) with SSIS to a SQL Server table. And export those images to an other folder.
Solution
The easiest way to import files/images with SSIS, is with the Import Column Transformation. You throw in a filepath and it creates a blob column with binary data. But the Script Component can be useful too. For export you can use the Export Column Transformation.
Three examples:
A) Importing images with Import Column
B) Importing images with Script Component and Import Column
C) Importing images with Script Component
A) Importing images with Import Column
For the first example I have created a CSV file with filepaths of images named bikes.csv:
C:\Users\Joost\Pictures\MountainBike1.jpg C:\Users\Joost\Pictures\MountainBike2.jpg C:\Users\Joost\Pictures\MountainBike3.jpg |
My target table looks like this:
CREATE TABLE [dbo].[ImageStore]( [id] [int] IDENTITY(1,1) NOT NULL, [imageName] [nvarchar](50) NULL, [imageFile] [varbinary](max) NULL ) ON [PRIMARY]
Note: If you want to store images in a SQL Server table use the VARBINARY datatype because the IMAGE datatype is due to be removed from SQL Server.
A1) Flat File Source
Go to your Data Flow and use a Flat File Source to read the CSV file from above.
Import file with only one column: ImagePath |
A2) Get filename from path
I also want to store the original filename in my table. I use an expression in a Derived Column to get the filename from the filepath. Add a Derived Column and add a new column named FileName. The expression is:
RIGHT(ImagePath,FINDSTRING(REVERSE(ImagePath),"\\",1) - 1)
Extract filename from filepath |
A3) Import Column
Now the actual importing of the image files into SSIS. Add a Import Column to the Control Flow.
Import Column |
A4) Input Columns
Edit the Import Column transformation and go to the Input Columns tab. Select the column that contains the filepath of the images.
Select column |
A5) Input and Output Properties
Go to the Input and Output Properties tab and add a new output column of the type DT_IMAGE and give it the name ImageData. Remember the generated ID because you need it for the next step.
New output column: DT_IMAGE |
A6) Connect input and output
Now go to the Input Columns. Select the input column and change the FileDataColumnId to the ID of the output column (24 in my case).
FileDataColumnId |
A7) OLE DB Destination
Now you're ready to import the data in the database table. Add an OLE DB Destination and select the table ImageStore. Connect FileName to imageName and ImageData to imageFile.
OLE DB Destination |
A8) The result
Run the package and watch the table for the result.
The result |
B) Importing images with Script Component and Import Column
This example only replaces the Flat File Source Component from example A into a Script Component that reads all files in a certain folder.
Replace CSV file by Script Component |
B1) Variable
Add a string variable to the package to store the path of the folder with images.
I called mine imageLocation. Fill it with a value like: C:\Users\Joost\Pictures\
Variable to store the import folder |
B2) Script Component
Add a Script Component to the Data Flow and select Source as the Script Component Type.
Script Component Source |
B3) Select variable
Select the variable from step 1 as a ReadOnly variable.
Select ReadOnlyVariables |
B4) Inputs and Outputs
Go to the Inputs and Outputs tab and add an output column: imageName (Unicode string [DT_WSTR] 150).
Column for storing the image path |
B5) The Script
Edit the Script and replace the script with this one (we only need CreateNewOutputRows).
//C# Code using System; using System.Data; using System.IO; // Added using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { public override void CreateNewOutputRows() { // Get all files within the folder string[] allFiles = Directory.GetFiles(this.Variables.imageLocation.ToString()); // Loop through the files in the folder foreach (string currentFile in allFiles) { // Create a new record foreach file this.Output0Buffer.AddRow(); Output0Buffer.ImagePath = currentFile; } } }
Or VB.Net
' VB.Net Code Imports System Imports System.Data Imports System.IO ' Added Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _ <CLSCompliant(False)> _ Public Class ScriptMain Inherits UserComponent Public Overrides Sub CreateNewOutputRows() ' Get all files within the folder Dim allFiles() As String = Directory.GetFiles(Me.Variables.imageLocation.ToString()) ' Loop through the files in the folder Dim currentFile As String For Each currentFile In allFiles ' Create a new record foreach file Me.Output0Buffer.AddRow() Output0Buffer.imagePath = currentFile Next End Sub End Class
B6) Restore Invalid Column References
Connect the new Script Component to the Derived Column and map the old imagePath column to new imagePath Column by double clicking on the Derived Column and pressing OK to confirm the mapping.
Restore Invalid Column References editor |
B7) The result
Run the package and watch the table for the result.
The result |
C) Importing images with Script Component
This example uses a Script Component to import all files in a folder to the same database table as in the first examples. The Script Component replaces all components except the OLE DB Destination. The Script Component loads the file into a Bit array via a FileStream object. That array can be used to fill an image column in SSIS.
Note: this is just an example to show that there are other ways to import files. Where possible use the Import Column Transformation.
C1) Variable
Add a string variable to the package to store the path of the folder with images.
I called mine imageLocation. Fill it with a value like: C:\Users\Joost\Pictures\
Variable to store the import folder |
C2) Script Component
Add a Script Component to the Data Flow and select Source as the Script Component Type.
Script Component Source |
C3) Select variable
Select the variable from step 1 as a ReadOnly variable.
Select ReadOnlyVariables |
C4) Inputs and Outputs
Go to the Inputs and Outputs tab and add two output columns: imageName (Unicode string [DT_WSTR] 50) and imageFile (image [DT_IMAGE]).
Create two output columns |
C5) The Script
Edit the Script and replace the script with this one (we only need CreateNewOutputRows).
// C# Code using System; using System.Data; using System.IO; // Added using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { public override void CreateNewOutputRows() { // Get all files within the folder string[] allFiles = Directory.GetFiles(this.Variables.imageLocation.ToString()); // Variable for storing file properties FileInfo fileInfo; // Loop through the files in the folder foreach (string currentFile in allFiles) { // Fill fileInfo variable with file information fileInfo = new FileInfo(currentFile); // Create new record this.Output0Buffer.AddRow(); // Fill filename column Output0Buffer.imageName = fileInfo.Name; // Stream the file into a byte[] object which can be used as column data FileStream fs = File.OpenRead(fileInfo.FullName); byte[] b = new byte[fs.Length]; fs.Read(b, 0, b.Length); // Fill column and close filestream Output0Buffer.imageFile.AddBlobData(b); fs.Close(); } } }
or VB.Net
' VB.Net code Imports System Imports System.Data Imports System.IO ' Added Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _ <CLSCompliant(False)> _ Public Class ScriptMain Inherits UserComponent Public Overrides Sub CreateNewOutputRows() ' Get all files within the folder Dim allFiles As String() = Directory.GetFiles(Me.Variables.imageLocation.ToString()) ' Variable for storing file properties Dim fileInfo As FileInfo ' Loop through the files in the folder Dim currentFile As String For Each currentFile In allFiles ' Fill fileInfo variable with file information fileInfo = New FileInfo(currentFile) ' Create new record Me.Output0Buffer.AddRow() ' Fill filename column Output0Buffer.imageName = fileInfo.Name ' Stream the file into a byte() object which can be used as column data Dim fs As FileStream = File.OpenRead(fileInfo.FullName) Dim b(fs.Length - 1) As Byte fs.Read(b, 0, b.Length) ' Fill column and close filestream Output0Buffer.imageFile.AddBlobData([b]) fs.Close() Next End Sub End Class
Note: Don't forget to add error handling and logging.
C6) OLE DB Destination
Now you're ready to import the data in the database table. Add an OLE DB Destination and select the table ImageStore. Connect imageName to imageName and imageFile to imageFile.
Connect input and destination columns |
C7) The result
Run the package and watch the table for the result.
The result |
Note: it also works with other filetypes, not just images.
what a great post; I wonder why no one has said so before? :/
ReplyDelete