Reading URL and storing file as Blob in SQL2000 using DTS.

Question

Reading URL and storing file as Blob in SQL2000 using DTS.

Daniel Jones

SSC-Addicted

Points: 471
More actions
March 23, 2005 at 12:10 pm

#163080

I have an Access DB with a table that contains file paths to PDF files. I am moving everything over to SQL2000 use DTS. What I would like to do is read the file path, and load up the PDF file into SQL as a blob.
Is this possible?
Could you provide a code sample of what I would need to do to accomplish this.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Calvin Lawson SSChampion Points: 11030 More actions · Answer 1

You cannot do this in SQL...you need to have middleware that converts the PDF into a binary object, which can then be stored in SQL. Usually you'll use a filestream to do this. What technology other than SQL do you have available? I'd recommend java or C#, although VB seems the most common way to do this in an MS world.

Here's some code examples:

http://www.vbdotnetheaven.com/Code/Sept2003/2175.asp

http://searchvb.techtarget.com/vsnetTip/1,293823,sid8_gci918585_tax293037,00.html

Signature is NULL

mike gercevich SSCrazy Points: 2961 More actions · Answer 2

You can read in the Binary data from the File URL with the following function, then set your insert parameter of your stored procedure to the return value of the function.

Function ReadBinaryFile(sFileName)
  Const adTypeBinary = 1
  'Create Stream object
  Dim oBinaryStream
  Dim oData
  Set oBinaryStream = CreateObject("ADODB.Stream")
  'Specify stream type
  oBinaryStream.Type = adTypeBinary
  'Open the binary file
  oBinaryStream.Open
  'Load the file data into stream object
  oBinaryStream.LoadFromFile sFileName
  'Open the stream and get binary data from the object
  oData = oBinaryStream.Read
  'object cleanup
  oBinaryStream.Close
  Set oBinaryStream = Nothing
  'Set function return value
  ReadBinaryFile = oData
End Function

Mike Gercevich

Daniel Jones SSC-Addicted Points: 471 More actions · Answer 3

So I make the "Function ReadBinaryFile(sFileName)" a User Defined Function in SQL?

mike gercevich SSCrazy Points: 2961 More actions · Answer 4

Well, since this is a DTS forum and you are reading the rows from Access; this would be a DTS solution... this would not be a good candidate for a UDF or a stored proc using oa_ method calls because it would not perform very well and would be extreamly difficult to manipulate the objects via straight SQL.

This solution is a Function inside of an Active-X Task to pull the Binary Data from a file and post it via ADO to your SQL server.

Mike Gercevich

Daniel Jones SSC-Addicted Points: 471 More actions · Answer 5

Hey I am new to the DTS thing, so I am just trying to figure out what your suggestion is.

I am with you now.

Thanks

Daniel Jones SSC-Addicted Points: 471 More actions · Answer 6

This is what I have setup in the Transformation Data Task. Is this what you are suggesting?

'**********************************************************************

' Visual Basic Transformation Script

'************************************************************************

function Main()

{

DTSDestination("Cutsheet").Value = ReadBinaryFile(DTSGlobalVariables("sFileName").Value);

return(DTSTransformStat_OK);

}

Function ReadBinaryFile(sFileName)

Const adTypeBinary = 1

'Create Stream object

Dim oBinaryStream

Dim oData

Set oBinaryStream = CreateObject("ADODB.Stream")

'Specify stream type

oBinaryStream.Type = adTypeBinary

'Open the binary file

oBinaryStream.Open

'Load the file data into stream object

oBinaryStream.LoadFromFile sFileName

'Open the stream and get binary data from the object

oData = oBinaryStream.Read

'object cleanup

oBinaryStream.Close

Set oBinaryStream = Nothing

'Set function return value

ReadBinaryFile = oData

End Function

Daniel Jones SSC-Addicted Points: 471 More actions · Answer 7

In the ActiveX Transformation Script I have the Main Function calling the ReadBinaryFile Function.

Am I doing this correct?

'**********************************************************************

' Visual Basic Transformation Script

'************************************************************************

function Main()

{

DTSDestination("Cutsheet").Value = ReadBinaryFile(DTSGlobalVariables("sFileName").Value);

return(DTSTransformStat_OK);

}

function ReadBinaryFile(sFileName)

{

Const adTypeBinary = 1

'Create Stream object

Dim oBinaryStream

Dim oData

Set oBinaryStream = CreateObject("ADODB.Stream")

'Specify stream type

oBinaryStream.Type = adTypeBinary

'Open the binary file

oBinaryStream.Open

'Load the file data into stream object

oBinaryStream.LoadFromFile sFileName

'Open the stream and get binary data from the object

oData = oBinaryStream.Read

'object cleanup

oBinaryStream.Close

Set oBinaryStream = Nothing

'Set function return value

ReadBinaryFile = oData

}

Daniel Jones SSC-Addicted Points: 471 More actions · Answer 8

When I try to run the ActiveX script I get the following exception.

"ActiveX Scripting Transformation 'DTSTransformation__2':Error parsing script - Error Code:0

Error Source=Microsoft VBScript compilation error

Error Description: Expected statement

Error on Line 0

."

Daniel Jones SSC-Addicted Points: 471 More actions · Answer 9

Fixed the problem, the following is the working code.

'**********************************************************************

' Visual Basic Transformation Script

'************************************************************************

' Copy each source column to the destination column

Function Main()

Const adTypeBinary = 1

Dim oBinaryStream

Dim oData

Set oBinaryStream = CreateObject("ADODB.Stream")

oBinaryStream.Type = adTypeBinary

oBinaryStream.Open

oBinaryStream.LoadFromFile = DTSSource("CutsheetURL")

oData = oBinaryStream.Read

oBinaryStream.Close

Set oBinaryStream = Nothing

DTSDestination("Cutsheet").Value = oData

Main = DTSTransformStat_OK

End Function