How to convert the pdf file to blob type ?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » How to convert the pdf file to blob type ?

How to convert the pdf file to blob type ?

Rate Topic

Display Mode

Topic Options

Author

Message

Charmer

Posted Tuesday, December 18, 2012 3:47 AM

Say Hey Kid

Group: General Forum Members
Last Login: Monday, February 18, 2013 2:08 AM
Points: 672, Visits: 725

Hi Friends,

I have a folder which contains thousands of pdf files where i have to convert into a blob file into a table. I have another table called Document which contains three columns called DocName and Doctype and fileSource.

The files in the directory is always named as like as the same name in the Document table.
For exapmle a pdf file holding the name called 15003786.pdf ...Exactly the same way we can see in the document table under DocName column. So my need is to look up for the names in the document table and process the matched files from the directory and convert it to blob data and insert into another table.

FYI I don't want to open the pdf document. I just want to convert it into a blob data.

Any suggestion woud be really appreciated. Help me friends..

Thanks,
Charmer

Post #1397664

Koen Verbeeck

Posted Tuesday, December 18, 2012 5:00 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:11 AM
Points: 9,378, Visits: 6,473

You can use the Import Column task to import the PDF files into the database.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1397702

Charmer

Posted Tuesday, December 18, 2012 6:20 AM

Say Hey Kid

Group: General Forum Members
Last Login: Monday, February 18, 2013 2:08 AM
Points: 672, Visits: 725

Koen Verbeeck (12/18/2012)

You can use the Import Column task to import the PDF files into the database.

Yes Koen, I am using the import column component....but the task gets failed when a file is unavailble in the directory if it is specified in the table. How to avoid such cases?

A file name can be specified in a table where it may or may not be availble in the directory...So i need to skip if the file is not available and move to the next file....

Thanks,
Charmer

Post #1397746

Charmer

Posted Tuesday, December 18, 2012 6:25 AM

Say Hey Kid

Group: General Forum Members
Last Login: Monday, February 18, 2013 2:08 AM
Points: 672, Visits: 725

Koen, FYI

"INSERT INTO [dbo].[Doc_Stage](DocName,DocPath,DocType) VALUES ('" +REPLACE(RIGHT(@[User::FilePath],FINDSTRING(Reverse(@[User::FilePath] ) ,"\\", 1)-1),RIGHT(@[User::FilePath],4),"")+ "','" + @[User::FilePath] + "','" + RIGHT( @[User::FilePath] ,FINDSTRING( REVERSE(@[User::FilePath] ),".",1)-1)+ "');"

I googled and found the above method of inserting the document name. I use this statement to insert the file name dynamically using a for loop containser and execute SQL task.....but this statement is not validated by the expression of execute sql task....could you help me please...?

Thanks,
Charmer

Post #1397751

John Mitchell-245523

Posted Tuesday, December 18, 2012 6:30 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 4,434, Visits: 7,218

Charmer (12/18/2012)

the task gets failed when a file is unavailble in the directory if it is specified in the table. How to avoid such cases?

Two options:
(1) Do a file existence check for each file, using xp_fileexist, a DOS command, a VB script, Powershell or however else you like to do it.

(2) Import the file list into a staging table and join that to your table of files so that you get a list that only includes files that exist.

John

Post #1397755

« Prev Topic | Next Topic »

Permissions