SSIS -How to Import Files (Text, Pdf, Excel, Image etc) To Sql Server Table [ How to Use Import Column In SSIS ]

Scenario : 

We have different types of files such as Text, Pdf, Image, Excel etc and we want to load them into Sql Server Table.


Solution:

First of all, let's create a table that can store these files. FileName column will be used to store the location from which the file is imported and File itself will be stored in File column whose data type is Varbinary.
We will use For-each loop container to loop through the files and Import Column Transformation to load these files into table.

CREATE TABLE dbo.ImportFiles
  (
     ID       INT IDENTITY,
     FileName VARCHAR(1000),
     [File]   VARBINARY(MAX)
  ) 

step 1: 

Create a variable of string type in SSIS Package with name "VarSourceFolder" and value = Folder Path.

Step 2:
Bring the For-each loop container to Control Flow Pane and configure as shown below to loop through all the files. We will be using VarSourceFolder as Directory in For-each loop. 

Go to Variable Mapping and then create a new variable VarFileName to save File name with extension.

Step 3:
Create a variable VarSQLQuery. We will write expressions on this variable to build TSQL Statement. The statement will contain complete path to file that we will pass to Import Column.

Expressions : 
"Select '"+ @[User::VarSourceFolder]+"\\"+ @[User::VarFileName] +"' AS FileName"
and 
Set EvaluateAsExpression: True


Step 4:

Bring Data Flow Task inside For-each Loop Container. Double Click on Data Flow Task and then Bring OLE DB Source and in SQL Statement provide the VarSQLQuery 

Step 5: 
Drag Import Column Transformation and connection OLE DB Source to it. After connecting, Double Click on Import Column and go to Input Columns Tab and Choose input column (FileName)


Step 6:
Go to Input and Output Properties, Then Output Columns and Add a new column "File" and note down the LienageID. The LineageID for File column in our case is equal to 85.


Step 7:
Click on FileName under Input Columns and then under Customer Properties. Set FileDataColumnId=85(LineageID for File Column that we created above).
Step 8:
Bring OLE DB Destination and map input columns to destination table. FileName is going to contain File name with source path and File column will contain file data itself.


Final Output:
Execute SSIS Package and query the table to see if all information is loaded.

All the four files are successfully loaded into our destination table.

17 comments:

  1. I need step by step including the visual studio package also

    ReplyDelete
  2. Images produced by digital cameras now rival the quality of our finest photographic film stocks. But the nature of a digital image shares almost nothing in common with the analog image captured in a film emulsion.Photo Retouching Service

    ReplyDelete

  3. Awesome photography! I feel lucky today to see the beauty of photography. It was really great inspirational post

    ReplyDelete
  4. I am getting error in Import column "[Import Column [2]] Error: Opening the file "C:\Demo\" for reading failed. The file was not found.
    How did you fix it?

    ReplyDelete
  5. I’m going to read this. I’ll be sure to come back. thanks for sharing. and also This article gives the light in which we can observe the reality. this is very nice one and gives indepth information. thanks for this nice article... image typing data entry

    ReplyDelete
  6. A very awesome blog post. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. cfa level 1 mock exam pdf

    ReplyDelete
  7. This particular papers fabulous, and My spouse and i enjoy each of the perform that you have placed into this. I’m sure that you will be making a really useful place. I has been additionally pleased. Good perform! cfa practice exam

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. Nice post. I was checking constantly this blog and I am impressed! Extremely helpful information specially the last part I care for such info a lot. I was seeking this particular information for a very long time. Thank you and good luck. professionelle Telefonansage

    ReplyDelete
  10. us import export data Wow, cool post. I'd like to write like this too - taking time and real hard work to make a great article... but I put things off too much and never seem to get started. Thanks though.

    ReplyDelete
  11. Thanks for the blog loaded with so many information. Stopping by your blog helped me to get what I was looking for. how to start a payment gateway company

    ReplyDelete
  12. The Printed Gujarat 6th, 7th, 8th, 9th, 10th Class Textbook 2023 are Distributed Through Cooperative Institutions All over Gujarat. Vendors are Linked to the Distribution of Textbooks with Distributors in each District. Gujarat Board 6th, 7th, 8th, 9th, 10th Class Book 2023 are easily Accessible to All Students of This System, Gujarat 9th Class Books This will assist in improving the Quality of Teaching by Understanding the Type of Difficulties Faced by Students of Class V in Different Subjects i.e; Reading Comprehension, Mathematics and Environmental Studies. This is an effort to Provide Feedback for Planning interventions at Various Levels of School Education.

    ReplyDelete
  13. Wow, amazing block structure! How long
    Have you written a blog before? Working on a blog seems easy.
    The overview of your website is pretty good, not to mention what it does.
    In the content!Vstpirate crack
    Euro Truck Simulator Crack
    PDF Compressor Pro Crack

    ReplyDelete
  14. You can't duplicate, glue or eliminate any information or data in PDFs. https://pdf-png.com

    ReplyDelete
  15. Digital imaging technology has progressed to a point where images produced by digital cameras rival the quality of photographic film stocks. However, the nature of a digital image is almost completely different from an analog image that is captured in a film emulsion. Get Headshot retouching services

    ReplyDelete
  16. The inclusion of a glossary aids readers in understanding complex terminology. Magazine download

    ReplyDelete