How to Export binary data in SqlServer to file using DTS

Question

I have an image column in a sql server 2000 table that is used to store the binary of a pdf file.

I need to export the contents of each row in the column to an actual physical file using SqlServer 2000 DTS.

I found the following method for vb at http://www.freevbcode.com/ShowCode.asp?ID=1654&NoBox=True

Set rs = conn.execute("select BinaryData from dbo.theTable")
FileHandle = FreeFile 
Open ("AFile") For Binary As #FileHandle 
ByteLength = LenB(rs("BinaryData")) 
ByteContent = rs("BinaryData").GetChunk(ByteLength)
Put #FileHandle, ,ByteContent 
Close #FileHandle

Unfortunately, the DTS script task is VBSCript, not VB, and it throws up on the AS keyword in the third line.

Any other ideas?

Answer 1

I would go with SQL Server Integration Services (SSIS) instead of DTS, if at all possible, and use a Script Task which would allow you to use VB.NET.

You can connect to your SQL Server 2000 data source and point the exported output to a file.

Answer 2

Writing binary files is a notoriously difficult task in VBScript. The only direct file operations exposed by VBScript live in the FileSystemObject object, which only supports writing text files. The only viable option is to use ADO Stream objects, which still is cumbersome because VBScript does not support passing script-created Byte arrays to COM objects, which is required to be able to write arbitrary binary data.

Here's a technique using ADO streams that probably won't work but might put you on the track to the right solution.

adTypeBinary = 1

Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT binary_data FROM dbo.data_table", "connection string..."
Set strm = CreateObject("ADODB.Stream")
strm.Type = adTypeBinary
strm.Open
strm.Write rs.Fields("binary_data").GetChunk( _
    LenB(rs.Fields("binary_data").Value))
strm.SaveToFile "binarydata.bin"
strm.Close
rs.Close

I experimented with this code using Access, and unfortunately it didn't seem to work. I didn't get the same binary data that was in the table I created, but I haven't bothered going into a hex editor to see what was different.

If you get errors from an ADO operation, you can get the actual messages by adding an "On Error Resume Next" to the top of the script and using this code.

For Each err In rs.ActiveConnection.Errors
    WScript.Echo err.Number & ": " & err.Description
Next