sp_OACreate 'ADODB.Stream', OUTPUT - An instace createdĮXEC sp_OAMethod 'Open' - Calling a methodĮXEC sp_OAMethod 'Write', NULL, - Calling a methodĮXEC sp_OAMethod 'SaveToFile', NULL, 2 - Calling a methodĮXEC sp_OAMethod 'Close' - Calling a methodĮXEC sp_OADestroy - Closed the resources Get Data into temp Table variable so that we can iterate over itĭECLARE TABLE (id int identity(1,1), varchar(100), varchar(100), varBinary(max) ) Now time to see actual T-SQL which will iterate through all documents, create folder and save Blob as a file on local disc.ĭECLARE varchar(50) = 'C:\G2\My POC\Blog\SQL Server\Extract Blob' If multiple nested folder needs to be created then we need to iterate through each folder and call CreateFolder stored procedure for each folder.
Note : We will create only one folder per document. To create folders using SQL Server, we will use stored procedure CreateFolder created in this post.
We will use Doc_Num to be created as folder and document will be saved in that folder. If we try to see content in actual table, it will look like Insert Blob into Database Export Blob From SQL Server and save it as a fileįor demo purpose, we want to save documents on local disc. (BULK 'C:\G2\My POC\Blog\SQL Server\Source\Progress.html', SINGLE_BLOB) (BULK 'C:\G2\My POC\Blog\SQL Server\Source\lightning.pdf', SINGLE_BLOB) SELECT 'pdf', 'Salesforce Lightning.pdf'.* (18, 0) IDENTITY(1,1) NOT NULL,įor demo purpose, we will save two files in our table using below queries To understand this, lets create a table in Database which will store files from local system into SQL Server as binary / Blob. Below script was used to export around 25GB of files stored in SQL Server. However, finally came up with below solution which worked very well. Script task to create a list of files to import (.I tried many ways to export files or documents saved as binary (Blob) datatypes in SQL Server.This is all well and good for the person who is looking for a specific piece of the puzzle, but what about the person who wants the whole puzzle? I put together a sample SSIS package that has all of the components (including sql scripts to build the database pieces): I have articles on how to use the import file task, articles on how to use the Enhanced Threading Framework I put together, articles on using SHA-1 to find duplicates, etc. I have, scattered throughout this blog, articles which show bits and pieces. I have gotten a number of emails over the past few days asking about how I import binary files into SSIS as well as how to improve throughput by making tasks parallel.
0 kommentar(er)
