All about Business Intelligence

At the end of a phase of a business intelligence project, when it has been accepted and it is time to move into production, comes the deployment on the production environment. The deployment itself should also be testable, reproducible and of course be completely debugged. The method largely depends on the customer’s IT team’s way of working. Some will insist on Installshield executables, some will prefer moving a server from acceptance to deployment, some will restore backups, some will work script based. I prefer that last approach and go into some detail on how to do this for all the typical parts of a MS BI solution: the database, integration services packages, SQL Agent jobs and the SSAS cubes.

Read more »

Often when using parameters in an Execute SQL task the thing just does not understand me. Or at least not what I am trying to do with the parameters. It seems the SQL Native Client is even more dumb when it comes to passing parameters than the MS OLE DB Provider for SQLServer, though I have not done a thorough investigation to identify the dumbest of the two.
A workaround is to put the SQL statement as an expression in the SQLStatementSource property. But be careful… this has already cost me hours of unnecessary debugging. With a simple trick this waste of time can easily be avoided.

Read more »

Visual Studio can deploy Analysis Services and Reporting Services projects to a server but there is no such option for Integration Services. Yet in my current setup I need to run packages on the server: multiple packages maintained by different people are part of a larger chain. Executing only one package makes no sense in this case. Sidenote: another reason that forces me to run packages on the server is because anything that has “TransactionOption = Required” fails to run from a client pc. If somebody knows of a way to make it run, please leave a comment…

Luckily there is an easy way to add the deployment capability to VS. In short: a custom action calls a batch command file that in turn uses dtutil to actually deploy the SSIS package.

Read more »

Apparently its easy to confuse SSIS if you make too many copy and paste operations inside a Data Flow Task. In this case I added six data flows going through six Slowly Changing Dimension Transformations to one Data Flow Task. I know, I could have split the DFL into 6 seperate DFLs, but the data flows are very small. Each flow needs tracking information from a SSIS variable. That is done with a Derived Column Transformation. Each flow receives the same tracking variable so the Derived Column Transformation was configured once and then copied five times. The Data Flow Task runs fine but ends with an error even though all components inside the DFL are marked green! The DFL is marked red in the Control Flow editor.

The error thrown was:

The variable “System::LocaleID” is already on the read list.

The workaround:
- create a new package
- copy the misbehaving Data Flow Task and paste it in the new package
- delete the Data Flow Task
- copy and paste from the new package

Maybe it has something to do with IDs of components that are not getting updated correclty ?
Related: New-suggestion-for-templates-in-Katmai

In many SSIS packages variables control the workflow. Here is a quick and generic way to load the initial values from a database table instead of having to hard-code them into the package or use configuration files.

Read more »

You may get an error when loading a string variable from a varchar(MAX) field via an Execute SQL Task. Here is the complete error I got when loading a variable named SQLStatement:

Error: 0xC001F009 at I_CTHNDL: The type of the value being assigned to variable “User::SQLStatement” differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Read more »