I’ve been tasked at work to create a large number of text files from SQL Server. Get this bro, 100,000+ text files, and as fast as possible! These files are for a program that consumes them for some sort of actuarial study…actually I haven’t gotten into the details but should understand the process better. Why? Well, maybe some of the files could be reused and not generated each run? It’s much faster to create 50K files as opposed to 100K I’m thinking. Maybe there’s a way to import data directly rather than a physical file out on the NT file system? But again, I’ll dig into the process and learn it later, and for now create the files for the application to use. We’re attempting to rewrite a DTS package that has been in use for a long time, and because most of the DTS servers will go bye-bye soon at the company, we have to go a different route to update our process. It’s a very big challenge, and gives me the opportunity to see what tool that we have at our disposal to crank out, not a large file that many of us have done in the past, but many small files and as fast as possible. These files, by the way, have about 45 records for each file, and one numeric “column” of data.
I have several options here, and I’ve tried a few of them up to this point. Here’s what I have brainstormed:
- bcp
- bcp from TSQL selecting “pre-calculated” rows from a table
- SSIS
- bcp from powershell
- .bat file with individual bcp commands on “pre-calculated” table
- sqlcmd
- c# custom app
- Combination of the above/others
Over the next week or two I will add to this series of blogs so that I might share what I learned given my hardware, network, and software setup. Hopefully you can learn something from my notes as well. If you have done an exercise like this, sharing your experience would be great, so pm me on what you found.
Thanks for reading,
Lee Everest
------------------------
Looks like I picked the wrong week to quit sniffing glue
27328aa4-3a08-400f-bec3-73d4d563fcfd|0|.0