by Wayne Walter Berry via SQL Azure Team Blog on 6/22/2010 6:03:00 PM
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:
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:
Do you have questions, concerns, comments? Post them below and we will try to address them.
Original Post: Copying Files to SQL Azure BLOBS
The content of the postings is owned by the respective author. AzureFeeds is not responsible for the contents of the postings. This site is automatically generated and cannot be reviewed for abusive content. If you find abusive content on AzureFeeds, please contact us. Designated trademarks and brands are the property of their respective owners. All rights reserved.
