SSIS and IBM DB2 – Don’t do it!

SSIS is a product that should be indifferent to whatever RDBMS you use. That’s also true. It will work with every RDBMS through drivers ODBC, OLE DB, ADO.NET etc. BUT it works best with Microsofts SQL-server.

This blogpost will take a look at using IBM DB2 with SSIS. Below is a test using ODBC-driver for IBM DB2 from IBM, OLE DB for IBM DB2 from Microsoft and an ADO.NET-driver.

Transfer rate of data

Transferring 100.000 rows from one IBM DB2-server to another IBM DB2-server.

OLE DB-driver for IBM DB2 from Microsoft 6 minuts
ADO.NET-driver 30 seconds
ODBC-driver for IBM DB2 from IBM 5 seconds

As seen above the ODBC-driver for IBM DB2 from IBM is the best solution when it comes to transfer rate. The OLE DB-driver for IBM DB2 from Microsoft is a really poor choice. But using the ODBC-driver for DB2 from IBM is not the solution to everything!

Lookup-task in SSIS

As seen in the picture below it’s only possible to use an OLE DB-connection in the Lookup-task. The Lookup-task is really slow and it might be better doing it in SQL. Regarding a lot of the tasks in SSIS, they are really slow (see links below).

OLE DB Command

SSIS also contains a ‘OLE DB Command’-task and no ‘ODBC Command’-task.

Fast load/Bulk load

OLE DB
It’s NOT possible to use the fast load option with the OLE DB-driver when it comes to IBM DB2. It’s possible to choose the fastload option, but you will get the error below.

The reason why the OLE DB-driver is that slow is probably that it transfers one row at a time.

ODBC
It’s possible to use a batch/fast load load option in ODBC.

ADO.NET
It’s also possible to use a bulk/fast load option in ADO.NET.

Links

The links below is to different sources that can help you with SSIS.

SQL Server Integration Services Design Patterns
A book that describes different design pattern for SSIS.

Task Factory for SSIS from Pragmatic Works
A collection of optimized SSIS-tasks.

Cozyroc
A collection of optimized SSIS-tasks.

PragmaticWorks free online training videos
Free online training videos about SSIS (search for SSIS).

Pluralsight
Online training videos in SSIS (requires a paid subscription to Pluralsight).

SSIS and versioning

This post will describe the big challenges that SSIS has when it comes to versioning solutions in SSIS when working as a team on the same SSIS-solution.

These experiences are based on using SubVersion (SVN) and Microsoft Team Foundation Server (TFS) for versioning solutions in SSIS. I have no experience with Git. But I will assume that the same problem occurs.

An example could be that John and Allan is working together on a SSIS-solution. Let’s call it ‘Staging DW’. They start out with local (their own) identical working copy of the SSIS-solution.

John creates a new SSIS-package in the SSIS-solution ‘Staging DW’. Let us call the SSIS-package ‘Stage Company’.
Allan creates a new SSIS-package in the SSIS-solution ‘Staging DW’. Let us call the SSIS-package ‘Stage Sales’.

John updates his working copy of the SSIS-solution ‘Staging DW’ and commits his changes to ‘Staging DW’ containing the SSIS-package ‘Stage Company’. Everything goes well.

Now Allan wants to commit his changes to the SSIS-solution ‘Staging DW’. He updates his working copy of the SSIS-solution ‘Staging DW’ – but he gets a conflict in the file ‘Staging DW.dtproj’ (an XML-file containing information about the SSIS-solution ‘Staging DW’ and its SSIS-packages).

The problem is that John’s new SSIS-package ‘Stage Company’ is a new package in the SSIS-solution ‘Stage DW’ and Allans SSIS-package ‘Stage Sales’ is a new package n the SSIS-solution ’Stage DW’. In the XML-file ‘Staging DW.dtproj’ information about the SSIS-package ‘Stage Company’ created by John occupies the same line as information about Allan new SSIS-package ‘Stage Sales’ in the XML-file ‘Staging DW.dtproj’.

The tools for solving conflicts in SVN and TFS isn’t capable of adding to the file. They can only overwrite.

One solution could be that Allan overwrites his ‘Staging DW.dtproj’ file. This means that his SSIS-solution ‘Staging DW’ looses it’s knowledge of his new SSIS-package ‘Stage Sales’. But he doesn’t lose his SSIS-package ‘Staging Sales’ it will still in the directory of the SSIS-solution ‘Stage DW’. He can now add this SSIS-package ‘Staging Sales’ to the SSIS-solution ‘Staing DW’ now containing John’s new SSIS-package ‘Staging Company’ and commit the SSIS-solution ‘Staging DW’ containing both John’s new SSIS-package ‘Stage Company’ and Allan’s new SSIS-package ‘Stage Sales’ without any problems.

Another solution is good old communication between team members. John could have told Allan that he was going to create the SSIS-package ‘Stage Company’ and that Allan should wait a bit with creating his SSIS-package ‘Stage Sales’ until John had committet the SSIS-solution ‘Stage DW’ containing his SSIS-package ‘Stage Company’. Then Allan could have updated his local copy and got the changes John made. Then Allan could start making his SSIS-package ‘Stage Sales’.

Or they could upfront have made all the SSIS-packages (without content) in the SSIS-solution. They could also choose to make SSIS-solution that was so specific or small that they did have to work together on them.

As of medio 2017 there is no real solution to this challenge.

Partitions in SSAS with no data

If you get the error below when processing a cube in SSAS. You might have partitions in the cube, that doesn’t have corresponding data in the underlying relational tables.

Errors in the OLAP storage engine: The restrictions imposed on partition slice where violated.‏

A solution could be to just delete the affected partitions from the cube.

Using special national characters in unittests with NBI

First of all, if you have a .csv file containing the results that you want to compare with your test and the result contains special national characters, then you need to encode the file in Unicode before saving the file. You change the encoding in Notepad for Windows as shown below.

SaveUnicode

If your .mdx file with the test to perform contains national characters in Danish that could be the characters æ (ae), ø (oe) and å (aa). Then you have to translate them to these characters to make them work.

æ æ
Æ Ã†
ø ø
Ø Ã˜
å Ã¥
Å Ã…

More characters can be found here.

An example of and MDX-query with national characters for in .mdx file for unittesting in NBI would look like the query below.

 

Using Search Engine Optimization (SEO) Toolkit in ISS 8.5

The guide below will show you how to use the Search Engine Optimization Toolkit for IIS. The toolkit is built for IIS 7.0 and ISS 7.5, but it works in IIS 8.5. The problem is that you can’t follow the guides available for IIS 7.0 or 7.5.

When you have installed the toolkit and wants to use it, you should go to the Default Web Site and choose Search Engine Opt…

Unavngivet

Here it’s possible to Create a new analysis.

2Unavngivet

But it’s not possible to change the path away from http://localhost/ in IIS 8.5. Please comment, if you know how to do this.

3Unavngivet

What you can do is the following. Right click on Sites and choose Add Website

4Unavngivet

You will meet this screen.

5Unavngivet

You can enter the information below. But in the Host name (www.mysite.com) you should enter the site you want to check. And press OK, when you’re done.

6Unavngivet

Now you have a new site. Go to the new site (Test site) and choose Search Engine Opt…

7Unavngivet

Choose Create a new analysis

8Unavngivet

Now you can give the analysis a name.
The path has changed to http://www.mysite.com (or what ever you have entered) and is not http://localhost/.
When you have given the analysis a name, you can hit OK, and the analysis of the website starts.

10Unavngivet

Getting the CU for your SharePoint installation

The pages below shows you how to get information about the CU (Cumulative Update) for your SharePoint installation.

The first page shows you how to get information about the CU.
http://www.brightworksupport.com/find-your-sharepoint-cumulative-update-or-patch-level/

The second page shows you what CU you are using based on the information found through the first page.
http://blogs.technet.com/b/steve_chen/archive/2013/03/26/3561010.aspx

SAS crashes when you connect with RDP

If your SAS-session crashes when you connect with RDP to a server the root-cause could be a check that SAS does for available printers.

To avoid these crashes it is possible to set a parameter in the config-file for SAS. The main config-file for SAS is called SASv9.cfg and in SAS 9.2 it’s usually located in C:\Program Files\SAS\SASFoundation\9.2\nls\en.

The parameter you have to add to the config-file is:
-noqueryports