• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1336
  • Last Modified:

Import PDF files into image datatype using SQL Server Integration Services?

I am working with SQL Server 2005 using an Access application as front-end.  The application stores .PDF files (as well as other types, such as .JPG), one at a time, in the SQL Server database as image or varbinary(max) using AppendChunk.  (Yes, the files themselves do need to be stored in the database, not just links to external locations, since I use Merge Replication to synchronize the contents with remote databases.)

The AppendChunk approach works well for individual files, but I have a large number of folders containing a large number of .PDF files that need to imported.  I would prefer to automate this with SQL Server Integration Services instead of coding it in Access using VBA, but I do not know how.  

Do you know of a tutorial that discusses all the steps of how to accomplish loading binary files from a single given location (folder) into a varbinary(max) column, retaining the file contents in the same way that AppendChunk does, and also storing each file's name in a text column at the same time?  Thanks!
0
AutomateMyOffice
Asked:
AutomateMyOffice
  • 3
  • 3
1 Solution
 
dbaSQLCommented:
I'm not too versed with AppendChunk, but I know that you can use the file watcher task within SSIS to monitor X number of folders containing X number of pdf's for import.

http://sqlis.com/post/File-Watcher-Task.aspx
0
 
AutomateMyOfficeAuthor Commented:
Thanks!  I will take a look.  I only need this for a one-shot mass import, not to "watch" on an ongoing basis.  Should it work OK for that purpose?  (About 25 folders, each holding hundreds of PDFs.)
0
 
dbaSQLCommented:
yep.   that should work perfectly.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
AutomateMyOfficeAuthor Commented:
Well, I have successfully downloaded and installed the File Watcher Task into the Business Intelligence Development Studio... and I now find myself pretty much completely lost.  

As I said in my original posting, I am in need of "a tutorial that discusses all the steps of how to accomplish loading binary files from a single given location (folder) into a varbinary(max) column."  I can only assume that the File Watcher is a valuable component in constructing such a process, but I guess I am not yet familiar enough with SSIS to actually use it.  While I am not completely unfamiliar with the tools (I have built some packages with the old DTS), I will probably need a bit more hand-holding to get me going with this project in SSIS.

I am guessing I will need to make use of the Import Column Transformation along with the File Watcher, but I don't know what steps to take.  Am I at least headed in the right direction?
0
 
dbaSQLCommented:
SSIS <> DTS.... the new world is so very, very different from the old.  far better, in my opinion, but there's quite a learning curve involved.  i'm still doing it myself

see this:  http://www.konesans.com/filewatcher.aspx
also, this is a pretty good discussion: http://www.windows-tech.info/15/588d15818c99653b.php

ultimately, it suggests against the file watcher task, but I, personally, think that would be the way to go.  I also believe you need to just use a foreach loop.   see the 'got the link '  section in the windoes-tech link, it will give you a better tutorial on the how-to  of the file watcher task
0
 
AutomateMyOfficeAuthor Commented:
Sorry.  I was very negligent in not properly closing this thread.  I found the answer to my question, in that I was able personally to locate a tutorial showing, step-by-step, how to load binary files into SQL Server using SSIS.  However, I was unable to apply it since the subsequent stored object is apparently incompatible with other operations (writing the object back out as a PDF file) that I must accomplish using GetChunk.  The tutorial is located here:
http://jumpstarttv.com/loading-binary-files-into-sql-server-using-ssis_62.aspx
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now