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.

24 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. This comment has been removed by the author.

    ReplyDelete
  11. { Otherwise, {you can|you are able to|it is possible to|you'll be able to} directly ask Pay - Pal {or other|or any other|or another|and other} {methods to|techniques to|solutions to|ways to} {help you|assist you to|enable you to|allow you to} on phone or through remote desktop software| Some things {to look|to appear|to check|to take a look} out for {are the|would be the|will be the|include the} {availability of|accessibility to|option of|use of} direct {phone numbers|telephone numbers|cell phone numbers|numbers}, instant messaging support, and direct emails| You will never {know exactly|know precisely|understand specifically|specifically} that {what type of|which kind of|what sort of|which} client {you are|you're|you might be|you happen to be} handling {and you have|and you've got|plus you've got|along with} to be prepared {and have|and also have|and possess|and still have} some {bunch of|couple of|few|lot of} {ideas to|suggestions to|tips to|tricks to} {make out|write out|find out|figure out} the solutions pdf data entry services

    ReplyDelete
  12. 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
  13. 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
  14. 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
  15. 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
  16. 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
  17. You can't duplicate, glue or eliminate any information or data in PDFs. https://pdf-png.com

    ReplyDelete
  18. 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