none
Quickest way to import blob data from multiple files RRS feed

  • Question

  • Hello Developers,

    I am doing a piece of work where I have to import millions of files of all sorts of extensions (msg, xlsx, pdf, etc) into SQL Server database. I need to import those files as blob data. The go to SQL OPENROWSET command is taking painfully long time. I don’t even know how long it will take to get all files into the database. Is there any quicker way that I can use than the “OPENROWSET(BULK N'Myfolderpath\FileName.pdf', SINGLE_BLOB) method. I have trawled the internet to no avail. If I can pull that data as multiple files than on a file by file basis, it will be great.

    Thanks and kind regards,

     


    Mpumelelo


    • Edited by Mpumelelo S Thursday, January 9, 2020 3:34 PM
    Thursday, January 9, 2020 3:33 PM

Answers

  • I guess you could write a multi-threaded C# program that runs SqlBulkCopy. Well, I guess that it could still use OPENROWSET (BULK), but something which runs multiple threads can speed things up a little. Of course, this assumes that you know how to write a multi-threaded program in C#. (Which I cannot really claim myself.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Mpumelelo S Monday, January 13, 2020 9:10 AM
    Thursday, January 9, 2020 10:28 PM
  • Thank you for all the responses. From I what I gather from the responses, it appears like one won't be able to escape the OPENROWSET , row by row 'curse'.

    With a program that reads there is actually an alternative to the row-by-row processing, as you can send multiple rows with SqlBulkCopy, and you can also use a table-valued parameter and send in multiple images at a time. But obviously, this requires skills in C#. And the C# program should fit into the overall solution.

    This article on my web site gives an introduction of using table-valued parameters in .NET: http://www.sommarskog.se/arrays-in-sql-2008.html
    There are no examples with blobs though.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Mpumelelo S Monday, January 13, 2020 9:09 AM
    Friday, January 10, 2020 10:27 PM
  • This is an update on the latest following my question. C# proved challenging and I couldn’t get any responses from the C# portal after posting my question for help with code for multi-threaded process. Anyway, I have discovered a relatively quicker method, which, if I have to be honest, the solution has been all along staring at me. Instead of using OPENROWSET, I am now using the “Import Colum” component in SSIS Data Flow. While my entire solution is built on SSIS, I have been pulling BLOB data using OPENROWSET run from SSMS using a stored procedure that I call from SSIS. As said, I am now using SSIS “Import Column” component, and this is proving to be relatively faster and I’m happy with the progress.


    Mpumelelo

    • Marked as answer by Mpumelelo S Tuesday, January 14, 2020 12:31 PM
    Tuesday, January 14, 2020 12:31 PM

All replies

  • BULK operations are already the fastest way and what do you expect, when you try to import MB/GB of BLOB data into SQL Server; that takes some time.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, January 9, 2020 4:00 PM
  • There are a few more options you can try:

    1. SSIS package with foreach loop container;

    2. bcp import to a stage table;

    3. powershell (maybe dbatools) scripts

    Thursday, January 9, 2020 5:15 PM
    Moderator
  • I guess you could write a multi-threaded C# program that runs SqlBulkCopy. Well, I guess that it could still use OPENROWSET (BULK), but something which runs multiple threads can speed things up a little. Of course, this assumes that you know how to write a multi-threaded program in C#. (Which I cannot really claim myself.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Mpumelelo S Monday, January 13, 2020 9:10 AM
    Thursday, January 9, 2020 10:28 PM
  • Hi , 

    Here are some ways .

    1.The quickest way to get your Excel file into SQL is by using the import wizard: Import data from Excel to SQL Server or Azure SQL Database

    2. Use BULK INSERT or OPENROWSET(BULK...) to import data to SQL Server

    3.Using SSIS : Start the SQL Server Import and Export Wizard Also, it is not corresponding forum for SSIS , if you would like to use SSIS , please post your issue in SQL Server Integration Services Forum .

    4.Import and export bulk data using bcp (SQL Server)

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, January 10, 2020 6:42 AM
  • Thank you for all the responses. From I what I gather from the responses, it appears like one won't be able to escape the OPENROWSET , row by row 'curse'. The closest to getting a better solution to my problem is what Erland has suggested, using a multi-threaded C# program, which I too cannot claim any knowledge in that. It looks like I will may not have any choice but go with the row by row, OPENROWSET method.

    Kind regards,


    Mpumelelo

    Friday, January 10, 2020 9:22 AM
  • Maybe you can ask for help in C# forum. Please check . C# forum

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 10, 2020 9:31 AM
  • Thanks Rachel.

    Kind regards,

    Melelo


    Mpumelelo

    Friday, January 10, 2020 10:22 AM
  • Thank you for all the responses. From I what I gather from the responses, it appears like one won't be able to escape the OPENROWSET , row by row 'curse'.

    With a program that reads there is actually an alternative to the row-by-row processing, as you can send multiple rows with SqlBulkCopy, and you can also use a table-valued parameter and send in multiple images at a time. But obviously, this requires skills in C#. And the C# program should fit into the overall solution.

    This article on my web site gives an introduction of using table-valued parameters in .NET: http://www.sommarskog.se/arrays-in-sql-2008.html
    There are no examples with blobs though.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Mpumelelo S Monday, January 13, 2020 9:09 AM
    Friday, January 10, 2020 10:27 PM
  • Hi Mpumelelo S, 

    You are welcome .

    Please kindly mark the helpful replies as answers or mark yourself. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 13, 2020 8:30 AM
  • This is an update on the latest following my question. C# proved challenging and I couldn’t get any responses from the C# portal after posting my question for help with code for multi-threaded process. Anyway, I have discovered a relatively quicker method, which, if I have to be honest, the solution has been all along staring at me. Instead of using OPENROWSET, I am now using the “Import Colum” component in SSIS Data Flow. While my entire solution is built on SSIS, I have been pulling BLOB data using OPENROWSET run from SSMS using a stored procedure that I call from SSIS. As said, I am now using SSIS “Import Column” component, and this is proving to be relatively faster and I’m happy with the progress.


    Mpumelelo

    • Marked as answer by Mpumelelo S Tuesday, January 14, 2020 12:31 PM
    Tuesday, January 14, 2020 12:31 PM