I’ve been working more with Docker on a Windows PC lately. With the more recent versions of Docker, the application runs much better and there is task bar control panel for managing the processes. If you are interested in trying out Docker and don’t have a Linux machine to work with, go download the Windows Stable Community edition which was recently updated to 17.09.0.
As with many things I post here, this article was the result of a problem I encountered, and how to resolve or work around it. I have been working with Docker on Windows and was attempting to run a container provided by Microsoft that included SQL Server. The process is fairly easy, just pull the image and run it with the appropriate command options. Unfortunately, that didn’t work. When I used the supplied command to run the container, I would get this error message:
Error response from daemon: failed to create endpoint <container name> on network nat: HNS failed with error. Unspecified error.
I spent several hours working this out over the past few days, doing web searches and trying various ideas. Lots of people experiencing the problem, but no solutions. Eventually I figured out a solution. Hopefully this helps someone else. The error above I found out indicates a problem with the default ‘nat’ network that Docker creates when you install it. By using the default NAT network, you basically have a private network on your docker host.
You can attempt to delete that network by opening PowerShell as Administrator. (Microsoft has a good write-up on networking for Windows containers here.) Enter the following command to see a list of the virtual networks defined on your PC:
Stop Docker using the whale icon on your notification area, by right clicking on the whale and choosing Quit Docker from the menu. Then run this command:
Enter “Y” at the prompt to remove all networks. Now restart Docker. If this doesn’t work for you, read on.
As I have stated previously when creating ETL workflows, its useful to store the information in a database repository, rather than as individual files on your workstation. This allows multiple users to have access to the information (why recreate the wheel?), it allows you to pull it into your jobs quickly and easily, and you can back it up quickly and restore it if necessary. With the community version 7.0 of PENTAHO® DATA INTEGRATION (PDI), I am happy to report that you can finally create a repository for your ETL code on Microsoft SQL Server. Previously, you could setup a repository on MySQL or PostgreSQL with the community edition but there were compatibility problems with the code that Kettle used that didn’t work with SQL Server. After downloading the latest version I was attempting to make a connection to SQL Server, and decided to test setting up a repository again. I am happy to say it works so the remainder of this article will walk through the process of setting up a Pentaho repository on SQL Server 2016 from a Windows 10 machine.
- Download the jTDS open source SQL Server JDBC driver. Extract the ZIP file, and copy the jtds-1.3.1.jar file from your download and save it into the data-integration\lib folder of your Pentaho application. Although Microsoft provides a JDBC driver, it did not work for me.
- Create an empty database on your Microsoft SQL Server. I created one called “PentahoRepository”
- Setup a SQL Server user account (not an Active Directory account) on your database server and give the account DBO (owner) permissions on the database. Using a DDLADMIN level does not work. I created my account and called it “repository”. I also set the default database for this account to the new database.
Now that we have our prerequisites setup, we can start the PDI client.
Recently I switched PCs to a newer Windows 10 based laptop for some of my work, and I wanted to get Pentaho Data Integration up and running on it. I downloaded the pdi-ce-126.96.36.199.25.zip file from the Community website, and extracted the contents to a folder in my Program Files directory. I tried running the SPOON.BAT to start it up but a window flashed on screen quickly and disappeared, but nothing else happened. I opened a command prompt and executed the SPOON.BAT file, but got a message that the JAVAW.EXE file could not be found. So I needed to perform a few other things to get it working.
A quick search engine query showed me that many people had the same issue, but there didn’t seem to be a consensus on how to resolve it. Below is how I managed to get it running.