Python is one of the most popular programming languages. It is a general purpose high level language. It was created by Guido van Rossum, publicly released in 1991. SQL Server 2016 started to support R, and SQL Server 2017 adds support for Python. Now you can select your preferred language for the data science and even other tasks. R has even more statistical, data mining and machine learning libraries, because it is more widely used in the data science community; however, Python has broader purpose than just data science, and is more readable and might thus be simpler to learn. This is the first of the four articles that introduce Python to SQL Server developers and business intelligence (BI) specialists. This means that the articles are more focused on Python basics and data science, and less on general programming with Python.
When talking about technologies, speed and capacity in the transfer of data plays a fundamental role. That is why two of the most important technology companies in the world, such as Microsoft and Facebook, along with Telxius, a telephone infrastructure subsidiary, have inaugurated the most advanced transatlantic cable so far. The result of this ambitious project has been named Marea (Spanish for “Tide”) and its main aim is to satisfy the growing demand for Internet services and cloud computing in recent years. Marea is the most advanced subsea cable that crosses the Atlantic Ocean. The cable connects the United States (Virginia Beach) with Spain (Bilbao).
I have uploaded a repository containing a helper to analyse the Data Migration Assistant tool results to GitHub. I have compiled and improved Microsoft’s version released last March in order to process aggregated results from multiple servers using Microsoft’s static code analysis tool. (more…)
SQL Server 2016 really represents a drastic change for the better in regards to general performance. And the proof is that, thanks to the engine changes, this is the first SQL Server version where Microsoft actively provides a widespread increase in performance levels by simply upgrading. But what impact can it have in your daily loads if you use columnstore indexes in SQL server 2014 datawarehouse? There was already an amazing increase in performance levels with SQL Server 2014 and the columnstore indexes. Is it worth upgrading to SQL 2016? (more…)
Below is a comparison table between ETL tools: SQL Server Integration Services aka SSIS vs PowerCenter. What tool should I choose? What are the differences? What are the advantages and disadvantages? Do they meet my requirements and particular needs? These are some of the questions that you may have asked yourself at the start of any new project, and we will aim to answer them in this post by providing you with some key facts regarding two very potent tools: SQL Server Integration Services a.k.a. SSIS versus Informatica PowerCenter.
Lately, we are noticing a trend whereby traditional SANs are increasingly losing ground. This can be due to various reasons, such as forced migrations to the cloud, limited budget environments or even customers who are getting annoyed with their storage providers’ draconian terms and conditions. A common example is to find very costly maintenance agreements, higher-than-market disk size upgrade costs, as well as difficulties to expand/ improve hardware without having to completely replace it. There is also a strong trend to continue going virtual and trading servers like they were commodities. All the above is therefore contributing to an increase in this trend towards SDS, while the VSANs continue to attract new supporters.
When you develop SSIS packages with custom functionality, either self signed assemblies or script components/script transformations that implement assemblies you got from codeplex, it sometimes become a nuisance to get them into GAC on production machines. What often happens is that when you try to run the package in earnest via the SQL Agent, it fails to find the assembly.
In my case I’ll be adding ionic.zip to my SSIS package dataflow in order to stream data into encrypted zip files.
If you would like to try out a cool way to deal with zip files in dotnet, I recommend it: DotNetZip – Zip and Unzip in C#, VB, any .NET language
So back to trying to run custom components and assemblies in SSIS packages. I bet you have seen something like this at some point in your career:
Error: System.IO.FileNotFoundException: Could not load file or assembly ‘Ionic.Zip, Version=126.96.36.199, Culture=neutral, PublicKeyToken=edbe51ad942a3f5c’ or one of its dependencies. The system cannot find the file specified.
File name: ‘Ionic.Zip, Version=188.8.131.52, Culture=neutral, PublicKeyToken=edbe51ad942a3f5c’
This tells us that our dll can’t be found when the SISS execution reaches the dataflow that uses it.
I wanted to find a solution that helps me deal with this in a good manner that is pretty future proof.
The first thing that came to mind is doing what I always recommend: use powershell. The problem is that sometimes you don’t have powershell, or an older version or aren’t allowed to run what scripts you want in production.
The next thing that was suggested to me was to copy gacutil.exe over to the production environment and do my deployment that way. That is not recommended though as it violates the license of Visual Studio SDK.
I tried out a couple of different plug ins for Visual Studio to be able to create an installer and the one that worked with the least hassle for me was WiX.
These are the steps I took to get my first working installer that added dll-files to gac:
- Make sure that your assembly is signed. If you are unsure about whether your own assembly is signed, then it probably isn’t. Since the example is of someone else’s assembly then it probably is signed.
- Make sure that your installation of Visual Studio is compatible with WiX (I use Visual Studio 2015 Developer)
- Download the latest stable release of the WiX toolset that works with your version of VS.
- Put the assembly (dll file) in any folder on your computer and have the corresponding path at hand. It doesn’t matter where the file is as long as you can access it when building the project.
- Create a new VS project for WiX as shown in this picture:
- Put an XML fragment into Project.wxs that points to your file:
- Set the project to release mode, build it, and go to the bin file to take a look.
- The three files are all needed for your installer, so zip them together and you are basically done:
- Move your zip file with the three files to the machine you need the DLL on and run the installer. If you want a prettier installer with more options and so on, put more effort into the WiX project!
Here is the excerpt of the xml that worked for me, mileage might vary so look up the documentation and the comment sections of other articles you find.
<Fragment> <ComponentGroup Id="ProductComponents" Directory="INSTALLFOLDER"> <Component Id="Ionic.Zip"> <File Name="Ionic.Zip.dll" KeyPath="yes" Source="C:\Import\Ionic.Zip.dll" Assembly=".net" AssemblyManifest="Ionic.Zip.dll"/> </Component> </ComponentGroup> </Fragment>
While working with a invoicing system back in 2013 I was asked by my customer if it would be possible to enable them to have some more special handling of their biggest client. The way their system was set up, one client accounted for over half of the accounts receivables. The client and my customer were also very close in their operations. What they needed from the system was the ability to split the client into four legal entities while remaining all the custom logic and prices. They asked me if a quick-fix was possible.
“A quick-fix solution is as fast, as easy and as well-documented as its explanation from the customer” – me just now.
The system had a procedure which would work out all the numbers for each client and for this particular one handle subsidiaries together on all invoices. With this reorganization coming, all units would be connected to one of four new client subsidiaries and each subsidiary would instead receive its own invoices for its units.
What the options for foreign key constraints do
You can add two clauses (ON UPDATE and ON DELETE) when creating a foreign key constraint. Both can have one of four options.
NO ACTION – this is standard, an error is raised and the execution halts if the new value would violate referential integrity.
CASCADE – data integrity is conserved by updating or deleting records in the related tables in the same transaction. In other words completely hazardous when dealing with deletes. Use with caution.
SET NULL – If the relation between records is broken, the integrity of the referencing record is upheld by setting the value of the foreign key column to NULL.
SET DEFAULT – Same as above except the value becomes the default for the column instead of NULL, there has to of course be a default value for the column for this to work.
From rough draft to the final solution
On the back of an envelope I wrote down these nine steps that would re-route the client logic and make sure everything would still sum up nicely:
- Begin a transaction and run the stored procedures to create the invoices as normal
- For each unit represented for the four new subsidiaries: create a new invoice header with a temporary and made up sequence starting from -1 and going backwards to avoid collisions.
- For each invoice line: map invoiceId by subsidiary (-1 through -4)
- Drop the now empty and unused invoice headers created in step 1.
- Drop the foreign key constraint between headers and lines
- Re-create this foreign key but set it with action ON UPDATE CASCADE
- Re-run the stored procedure that fixes invoiceIds for headers, this procedure is a sub-procedure when creating invoices. When the headers are updated, the lines inherit the information through our foreign key constraint.
- Drop and re-create the foreign key once again as it existed before the operation.
- Commit transaction. Done! No lines removed, all accounted for and all connected nicely.
I am excited to announce to our community and partners that I have moved on from my North American leadership role to a new role as our Corporate Chief Technology Officer. My new job is to align the long-term vision of what SolidQ does and how we do it, across all our international markets. Joining me will be Antonio Soto as Corporate COO, dedicating himself to our global operations and driving delivery support in both established and growing markets. (more…)
This blogpost is both an introduction to building and managing CLR functions in SQL Server as well as a guide to avoid storing sensitive information that is only used for authentication. (more…)