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.

16 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. Inconsistency in output quality: If the provider {you have chosen|you've chosen|you've selected|you have selected} is inexperienced and lacks consistency, {then it|it|this|that} {might lead to|could trigger|might trigger|may cause} problems {such as|for example|including|like} delayed submission of completed projects, processed files without accuracy and quality, inappropriate assignment of responsibilities, {lack of communication|no communication|poor communication} {and so|and thus|therefore|so} on| While the job profile {might seem|may appear|may seem|might appear} simple {it does|it will|it can|it lets you do} {in fact|actually|in reality|the truth is} {require a|need a|demand a|have to have a} certain {degree of|amount of|level of|a higher level} exactness {and an|as well as an|plus an|with an} eye for detail| My writing {is focused|is concentrated|is targeted|concentrates} {more on|more about|read more about|on} {the industry|the|a|that is a} {and quality of|and excellence of|superiority} work, not the worker| By continues monitoring the hurdles and solving it, {one can|it's possible to|you can|one can possibly} easily {increase the|boost the|raise the|improve the} productivity of business| Decline {in the|within the|inside the|inside} quality of service and delay {in the|within the|inside the|inside} execution and delivery of processes are some {of the|from the|with the|in the} risks involved, {besides the|aside from the|in addition to the|apart from the} risk {to the|towards the|for the|on the} security {of the|from the|with the|in the} data and privacy and cost-related risks| The {service provider|company|supplier|vendor} {should also|also needs to|must also|also need to} volunteer {a variety of|a number of|many different|various} profits concerning formulas {of data|of information|of knowledge|of internet data} transmission, turnaround etc}. { A lot of companies are fine with admitting this, but {others are|other medication is|other people are} {not so|not too|not|less than} sure, primarily {because this|as this|since this|simply because this} may put people {off the|from the|off of the|over} service| Such measures would {keep your|keep the|maintain your|maintain} sensitive documents from falling {into the|in to the|to the|in the} hands of unauthorized personnel| When you outsource {to an|for an|to a|with an} experienced BPO company, {they would|they'd|they might|they will} manage these risks professionally {as well as|in addition to|along with|and also} plan and implement appropriate {strategies to|ways of|ways to|methods to} avoid them in future| Outsourcing data entry is most helpful term {for all|for those|for many|for all those} these organizations| With the help of such information, {you can|you are able to|it is possible to|you'll be able to} {improve on|enhance|make improvements to} customer targeting| If you think {you are|you're|you might be|you happen to be} proficient enough in installing the payment processor {on your|in your|on your own|on the} website {on your|in your|on your own|on the} own, {you should not|you shouldn't|you ought not|it's not necassary to} hesitate doing it}.pdf data entry services

    ReplyDelete
  6. 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
  7. 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
  8. 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
  9. Daniel Gustav is a 32-year-old tea maker who enjoys walking, listening to music and running. He is kind and careful, but can also be very unfriendly and a bit untidy.He is Argentinian who defines himself as straight. He started studying medicine at college but never finished the course. He is obsessed with Frozen.He is average-height with fair skin, grey hair and brown eyes. He has a tattoo of Olaf on his left shoulder.He has recently installed Decorative resins stained glass and is working as the Manager at Creative Resins. To know more woocommerce data entry

    ReplyDelete
  10. I admire this article for the well-researched content and excellent wording. I got so involved in this material that I couldn’t stop reading. I am impressed with your work and skill. Thank you so much. cfa level 1 material

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

    ReplyDelete