Tuesday, April 17, 2018

Here we go!

Today is a really special day! 


Docco Labs has been offering a collection of unique SQL-related tools to make everybody's tasks easier when it comes to handling with data (Import, Export, Explore and more)

We've started with SQL Stripes, and over the years created more apps and products, including SQL ClipTable, Database Space Explorer , SQL Query Zipper, Log4Sql and SuperBulkCopy

As of today, all of these products are offered FOR FREE!


To get the free versions, simply download and install, or upgrade through your already-installed products.

Hope you enjoy using our products.

Feel free to share these exciting news!

Best,
Adi


Friday, April 6, 2018

Big News coming soon

There are new versions in the pipeline, along with some very exciting news

Stay tuned!



Thursday, April 6, 2017

SuperBulkCopy (Major) Refresh



SuperBulkCopy (and the command-line sbcp) is one of our newest (free) which comes to replace the traditional usage of both 'BULK INSERT' and 'bcp' with something much more powerful, yet simple.

If you've ever worked with either, trying to turn text-based files into a SQL Table, you are probably quite aware of the various limitations. SuperBulkCopy was written to overcome those limitations and provide a quick working solution.

OK, so that was the quick refresh. Now, there's a new version which just came out, and in addition to the existing abilities, it introduces the following:

- Gzip support! You can now bulk import directly from a .gz file 

This is a big one -> often the input files are compressed and traditional import tools usually require a preliminary step of uncompromising everything to a different folder.
SuperBulkCopy will detect those compressed files and will extract the stream in-memory, quickly importing the uncompressed data.

- Wildcard support! Now supporting wildcard* input files

Have multiple files in a folder which all needs to be imported? No problem -> the wildcard feature will take care of achieving it all in a single run. Whether to the same table or different tables.
If you wish to use different table names, see the item below!

- Output table pattern support! 

This feature helps you customize the output table name. Based on input file names, dates, unique id's and more.
The supported patterns are:
%ifn -> Input Filename
%timestamp -> Timestamp (yyyy-mm-dd-hh-mm-ss)
%date -> yyyy-mm-dd
For example,  if  Output Table Name is "my_table_%ifn", and input file name is "my_file.csv" then the table will be named "my_table_my_file_csv"


- Added a new custom field generation -> adding input filename as an additional field to the output table/file

This new feature allows you to add custom fields into the destination table/s.
The current supported fields are:
- Input Filename
- Input Directory


More cool things to come -- in the meantime, enjoy this new version!



Wednesday, December 28, 2016

Database Space Explorer

As part of our app renaming/re-branding - Database File Explorer is now known as Database Space Explorer. The main reason is that this product, which started being very focused on the file/filegroup usage is expanding to many other database-space related stats.

With that being said, we encourage you to try out the latest version of the app - there are many new features, fixes and enhancements. For example - A "Table Popularity" view which lists table by their connection and references to/by other objects. This provides another easy way to explore large databases.

Here's a screenshot:



Wednesday, July 13, 2016

SqlTableZip is now known as - "SQL Server Query Zipper"

I think names are important. And product names specifically should indicate something about the actual product.

With that in mind, "SQL Server Query Zipper" (or SQL Query Zipper in short) does a better job on explaining what the tool does - even before reading the description.

There's a new version - which in addition to other enhancements, also goes by the new name.

TA-DA!



The console version was also renamed and the executable is now called: SSQZConsole

Tuesday, May 24, 2016

SuperBulkCopy (SBCP) Walk-through

Recently, We've introduced a free new tool called SuperBulkCopy (short name: sbcp)



What it does is simple to explain, and yet pretty challenging to implement - it does everything we wanted BULK INSERT, BCP, or any existing flat file importer to do: Get the job done, as automatic as possible.

In this post I'll provide a walk-through, with many images. Video will also become available in the future but hopefully this segment can get you up to speed as to: "Why do I REALLY need this!"

Again, as mentioned above, this tool is absolutely free. So just get it and start playing!

Let's begin.

Part 1: The struggle


Here's the setup - I got a fairly big file from a client, and need to turn it into a SQL Table for further analysis.

I look at the file:


It's about 1.7gb big. Thinking - already let's have a look with Notepad:


Nah! too big. How about Notepad++?


Nope. The same thing.

OK, next step -- I do know (and we'll see this in a moment) that this file has mixed data types, and everything is wrapped with "quotes". Also - it has headers, which eliminates the ability for either BULK INSERT or BCP to handle it correctly.
So we'll do the next natural thing and jump right into the "Text Import Wizard", or use SSIS directly. (Same engine anyway)



Loaded the file, with the correct delimiters, as well as specified the Text Qualifier - A quick preview indicates we're almost good to go:



Mapping the data into a new table, after also using the "Suggest Data Types" feature




But - OH NO! The process has failed.
This can be caused by so many reasons, from data truncation, into type mismatch, headers not 100% matching the field structure and more.


Looking at the friendly logs:


Fun Times!

Now, I'm not saying that everything fails, and even the ones that do are in some cases solvable after a few iterations of trial & error.

But the question is - WHY BOTHER? Where's there is a free tool today that will do all the hard work for you?

Part 2: The Easy Way Out


Let's try to do that same thing with SuperBulkCopy.
It's very fast and easy to use, so I'll take the time showing a few different methods of getting the job done.

Let's go! (again)

Okay, after installing the tool, let's run it. I'm using the DoccoLabs launcher but you might as well simply run it directly from the start menu.


Okay - now it's loaded.
On Input Settings, I choose that same file. 



Now, even before going further - there's a quick "Preview" option, regardless of file size - it will show you a sample


Yep, Looks great!
Now, there's not a lot to do: Simply defining the Destination SQL Server connection. I'll even ignore the fine-tuning options and simply let it run.
(Note: for the demo I've only imported part of the file, which is another feature, but one can easily import the entire content)


So - completed. Let's go check the table in SSMS:



It's right there. Looking at the table definitions even shows that the best data-types were used:


Basically, we're done.
But I want to spend another minute of showing you how SuperBulkCopy can run just as easily as a console-app from command line.

After setting all the relevant input-output configuration for this demo, we can save the configuration for later use.

In our case, the configuration looks like this:

<?xml version="1.0" encoding="UTF-8" ?>
<config>
    <input>
    <Filename>c:\temp\text_samples\big_file_with_quoted_identifiers_20160301.tsv</Filename>
    <AutoDetectHeaders>True</AutoDetectHeaders>
    <AutoGuessDelimiters>True</AutoGuessDelimiters>
    </input>
    <output>
        <TableName>test1</TableName>
        <OptimizeDataTypes>True</OptimizeDataTypes>
        <ConnectionString>Data Source=127.0.0.1;Initial Catalog=tempdb;Integrated Security=True;</ConnectionString>
        <DropTableIfExists>True</DropTableIfExists>
    </output>
    <AllowAsyncDataWrite>True</AllowAsyncDataWrite>
</config>


And I can save it using the main menu.




Now - I can start using sbcp.exe
Of course, instead of generating the XML file, I could write it myself. Typing sbcp.exe /? provides all the essential information of how to build a configuration file.
Alternatively, everything *can* be done with simple command-line arguments but I find XML option to be more clear and easy to understand.

So, now let's open a command window and direct sbcp.exe to the configuration file:


And here are the results:


Basically - the exact same result as the UI version produces. Only here - the process can be fully automated. (for example, if such file arrives every day and needs to be turned into a table)

Furthermore - this can run directly from SSMS by using the SQLCMD mode. Here's how

Switch into SQLCMD Mode on the SSMS main menu:



Now, run the same as you just did from the command line. don't forget to add the !! indicator.


Part 3: Conclusions

In short, SuperBulkCopy can easily get into flat-file input into your SQL Server. It does a lot such as detecting headers, delimiters, data-types and more.
It's a free tool that can potentially turn this annoying task into a rather easy one.

You can get it from this line: http://www.doccolabs.com/products/superbulkcopy

---

Have anything to add? Let us know! (comments, twitter mentions, emails or whatever channel you like best)





Thursday, May 19, 2016

SQL Stripes is now FREE (Going EOL)



It feels a bit awkward to say it, but SQL Stripes - the first product that ever came out of DoccoLabs, is reaching its End-Of-Life.

As SQL Stripes is getting old, and after a lot of considerations I've decided to not write a new "similar" product mostly due to the ROI given a growing number of other monitoring tools out there.

SQL Stripes, like all of the other products was written with love and passion to making the SQL Server users' life easier.
It was written out of need, and served many customers. (And still does to this day)

For existing users who get what they need from the product - feel free to working with this but keep in mind that there will be no more future releases, fixes or any sort of support around it.

In addition, We're making SQL Stripes free! If you would like to keep working with it after the trial is over (or even before it expires), send me an email and I'll send you an activation key.

SQL Stripes Screenshot