How to Export the Files from a Folder to the SQL Server Table in Binary Format using SSIS Package

SSIS Package to Export the Files from a Folder to the SQL Server Table in Binary Format using Import Column Transformation
The Import Column Transformation in SSIS is used to import binary files, images, media or any kind of document to the data flow and then do some manipulations and then load the data to the destination. The data type of the output column must be DT_TEXT, DT_NTEXT or DT_IMAGE.

Scenario :

Suppose we have a different kind(.txt,.xlsx,.jpg,.mp3) of Files in a Folder as follows..

Now we want to Import these Files along with their Fully Qualified Names in to the below SQL Server Destination table..

Here, In the FileName Colum we store the File Full Path and in the FileObject Column we store the File in a Binary Format.

Now lets Design the Package to Fulfill the above Scenario..

--------------------------------------------------------------------------------------------------------------

Phase - I : Create the required Variables

--------------------------------------------------------------------------------------------------------------

Here we create the 3 variables as follows...

FolderPath : It is a Static variable in which store the Folder Path from which we read the Files by using the ForEach Loop

FileName : It is a Dynamic variable which we will use in ForEach Loop to store the File Names dynamically during run time.

Temporarily you give any name like "TempFileName" which will change in Runtime.

varSQLQuery : This is Dynamic Variable where we Store and SQL Select Statement as a String which Selects the Fully Qualified File Name and File Name using the below Expression. :

"Select '"+@[User::FolderPath]+ @[User::FileName] +"' as FullyQualfiedFileName,'"+@[User::FolderPath]+ @[User::FileName]+"' as FileName"

Expression result : 

Select 'T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Source\MyFiles\TempFileName' as FullyQualfiedFileName,'T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Source\MyFiles\TempFileName' as FileName

Here, if you observe in this Select Statement, in both the Columns FullyQualfiedFileName and FileName , I am reading the File Full Path only.

But, while loading these to Destination table, FileName will stored as it is value and the FullyQualfiedFileName  value will be Converted to Binary Format and will stored as File.

--------------------------------------------------------------------------------------------------------------

Phase - 2 : Add the For Each Loop Container to Control Flow and Set the Properties

--------------------------------------------------------------------------------------------------------------

A) Add the ForEach Loop Container to the Control Flow

B) Next in ForEach Loop Editor > Collection Tab, Map the Varaibles and Set the Properties..

Enumeration = ForEach File Enumerator

Expressions > Directory = @[User::FolderPath] , the Folder Path from which the Files will be Collected.

Retrieve File Name = Fully Qualified

C) Next in ForEach Loop Editor > Variable Tab, Map the File Name Variable

Variable = User::FileName

Index=0

Next Say OK.

--------------------------------------------------------------------------------------------------------------

Phase - 3 : Add the Data Flow inside the For Each Loop Container and Design the Flow

--------------------------------------------------------------------------------------------------------------

A) Add the DataFlow inside ForEach Loop Container

B) Add the  OLE DB Source inside DataFlow and Set the Properties...

Next go to OLEDB Source Editor > Connection Mangers ;
Set the Connection and Select the Data Access Mode as "SQL Command from Variable"
Next Select the Variable Name.

Next select the Columns from the OLEDB Source Editor > Columns Tab

Next say OK.

C) Next set the Properties for OLE DB Source Advance Editor > Input and Output Properties.

We have to Setup these Properties to avoid the Truncation Error when you are reading the Data from the Data Flow.

Here we just need to set the Length for Output Columns which should be Greater than External Columns of the same section, as shown below.

Next say OK.

--------------------------------------------------------------------------------------------------------------

Phase - 4 : Connect the Import Column Transformation to OLD DB Source and Set the Properties.
--------------------------------------------------------------------------------------------------------------

A) Connect the Import Column Transformation to OLE DB Source

B) Next from Import Column Transformation >  Advanced Editor > Input Columns , Select the required Input Column which we need to Process as File in Binary Format

Here I selected only the colulmn "FullyQualifiedFileName" which I want to Process/Convert as File.

The Other Column "FileName" is not select as I want that to load to Destination as it is with out any Processing.

C) Next from Advanced Editor > Input Output Properties , Add a New Column and under Import Column Output > Output Columns

Here I added a New Column as "FileObj" , whose LineageId is 57 and DataType is DT_Image.

Next Copy that LineageID of the Newly added Column, and assign that Value to the Import Input Column > Input Columns

"FullyQualifiedFileName" > FileDatColumID

Now this Colum will be Converted from String to the Binary File Column "FileObj"

Next Say OK.

--------------------------------------------------------------------------------------------------------------

Phase - 5 : Connect the Import Column Transformation to OLD DB Destination and Set the Properties.
--------------------------------------------------------------------------------------------------------------

A) Connect the Import Column Transformation to OLE DB Destination

B) Next in OLE DB Destination Editor > Connection Mangers, Connect to the DB and Select the destination Table

Next do the Columns Mapping..

Here I have ignored the "FullyQualifiedFileName" as it Processed then Transformed as "FileObj" Field.

Also, I have ignored the FileId, which is an Identity Column that will generate automatically in the destination Table "Tbl_FilesStorage"..

Next Say OK and Run the Package..

------------------------------------------------------------------------------------------------------

Output :

Now the File Names and Files are stored in the Destination Table as follows..

SELECT [FileID]
  ,[FileName]
  ,[FileObject]
  FROM [dbo].[Tbl_FilesStorage]GO

Note :

If You export the Binary File names from FileObject Column to a Folder, using the Export Column Transformation, then you will see the Files, as we Seen in the Source above.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------