Copying Files to SQL Azure BLOBS
[This article was contributed by the SQL Azure team.]
I have written a simple console application (run from the command line) to upload multiple BLOBs from files into SQL Azure. This article discusses how it works, provides a complete Visual Studio solution of the console application and the source code in case you want to modify the code to meet your needs. It is an extension of this former blog post that introduces the SqlStream class.
In some scenarios you might wish to deploy images from your desktop directly to SQL Azure into a varbinary(max) column. You can do this with tools like Microsoft SSIS and the BCP utility. However, if you have a directory of images, this tool will allow you to upload them all directly to SQL Azure in a streaming fashion, breaking down the image into blocks that are written individually to SQL Azure.
It doesn’t have to be images. BlobCopy will allow you to upload any data you can store in a file directly to a SQL Azure column.
I made many assumptions about how this tool was to be used; this gave me the advantage of keeping the sample and the code simple. Because the assumptions might not match how you would like to use the tool, I have included the code so that you can tweak it to work as you want. If you can’t figure out how to do what you want, post a comment below and I will try to help. I also emulated many of the BCP parameters to keep the learning curve low.
Here are the assumptions I made:
- You have a directory of files; each file is a row in the database.
- Only the rows you want to update have files in the directory.
- All the files in the directory are to be uploaded when BlobCopy is run.
- The name of each file without the extension represents the primary key in the database where the BLOB is written. For example, with the AdventureWorksLTAZ2008R2 database and the SalesLT.Product table the file 884.jpg would go to: ProductId = 884.
- 8192 bytes of data are uploaded at a time. That is the block size per transaction.
One thing to note is that mid stream failures will leave the BLOB in an unknown state in the database. Each write is a transaction, however the BLOB is broken down into multiple blocks and multiple writes. If you succeed on several writes and then encounter a failure that terminates the process, the BLOB might only be partially written to the database.
This sample code uses the AdventureWorksLTAZ2008R2 database and writes all files in C:\My Projects\Sql Azure Blog\BlobCopy\Test Files to SalesLT.Product table in the ThumbNailPhoto Column.
BlobCopy.exe AdventureWorksLTAZ2008R2.SalesLT.Product -S tcp:yourServer.database.windows.net -U yourlogin -P yourPassword -K ProductId -B ThumbNailPhoto -F "C:\My Projects\Sql Azure Blog\BlobCopy\Test Files"
BlobCopy database_name.schema.table_name -S server_name–U login_id –P password -K primary_key –B blob_column –F folder_of_files
Just like the BCP utility, the first argument is the database, schema, and table of the destination to write the blog.
-S server_name : Specifies the SQL Azure server to which to connect.
-U login_id: Specifies the login ID used to connect to SQL Azure.
-P password: Specifies the password for the login ID. If this option is not used BlobCopy uses the default password (NULL).
-K primary_key: Specifies the primary key column in the table so that BlobCopy can identify what row to write the blob in. This is the column name, the value of the primary key comes from the file name.
-B blob_column: Specifies the column in which to write the blob.
-F folder_of_files: A folder location of the local machine where the blobs are stored as files. One file for each row to be uploaded. The file name is the primary key value that identifies the row.
These additions to the code, left to the reader, might be useful:
- Move the files after successful upload to another directory.
- Allow the amount of bytes copied in each transaction to be determined by a command argument.
- Handle transient retry as discussed in this blog post.
Do you have questions, concerns, comments? Post them below and we will try to address them.