BLOG

MY PERSONAL JOURNEY

I just got 15.000 new colleagues

rehfeldims

Guess what. I just got 15.000 new colleagues.

Rehfeld Partners is to be acquired by IMS Health. IMS Health is a leading global information and technology services company, with more than 60 years experience, providing clients in the healthcare industry with comprehensive solutions to measure and improve their performance.

Rehfeld has long history – back in 1998 it started with the current directors Ditlev Moltke {linkedin} and Claus Rehfeld {linkedin}. From the start Rehfeld has focused on business intelligence. The foundation started in the healthcare part of Denmark and has expanded ever since to be the biggest Business Intelligence consulting company in Denmark.

Now a new era is about begin. Ditlev and Claus is handing over the company to IMS Health, including all staff.

Am I scared you might ask? A bit, but positively – absolutely. It is a big step for a company like Rehfeld to be part of the globally biggest information and technology company. We are adding our professionalism and know-how about business intelligence, knowledge of the Nordic customers and a strong technologic staff to IMS Health and IMS Health is adding international experience, good contacts with the life science indutry and even more technology.

Rehfeld and the staff is going to do excactly the same job in the future. Nothing has changed there. We are going to get new processes and new way of doing the work, but the work and projects are the same. Still the best in class on delivering business intelligence to the Denmark in order to make Denmark a more effective society.

I’m so looking forward to the next page of my work-life. It is going to be thrilling, fast and good.

Fully automate the BIML expansion

Mist 4.0

With all the BIML code done and the production scenarios start to emerge, it would be great to have the BIML code automatic expanded and create new packages if there are changes to the underlying metadata. Either configured manually or directly from the sources. Also known as BIML expansion.

With the MIST application from Varigence – this is possible through the command line util that ships with the installation.

When the installation of MIST has finished, you’ll find a new .exe-file in the installation folder called bimlc.exe.

This file is the core engine of the command line util.

According to the online documentation found here – the command line util is pretty straight forward.
The observant reader will notice that the link points to a documentation for hadron.exe – this is the old name for the same tool. See this documentation from Varigence.

The syntax is


bimlc.exe -s="<sourcefile>" -t="<targetfolder>"

I’ve made a quick demo biml-project with below code:


<# var meta_connectionstring = "Provider=SQLNCLI11;Server=[myServer];Initial Catalog=[myDatabase];Integrated Security=SSPI;"; #>
<#
DataTable tables;
tables = ExternalDataAccess.GetDataTable(meta_connectionstring, "[dbo].[storedProcedureThatGivesMeTheData]");
#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<OleDbConnection Name="BIMLMETA" CreateInProject="true" ConnectionString="Provider=SQLNCLI11;Data Source=[myServer];Integrated Security=SSPI;Initial Catalog=[myDatabase]" />
</Connections>
<Packages>
<# foreach(DataRow table in tables.Rows) { #>
<Package Name="SSIS package <#=table["schema"] #>_<#=table["table"] #>">
</Package>
<# } #>
</Packages>
</Biml>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Linq"#>

This file is called AutoCompile.biml, and I want to put the generated files in c:\AutoCompilePackages. Then the commandline would be:

bimlc.exe -s="c:\AutoCompile\AutoCompile.biml" -t="c:\AutoCompilePackages"

When I then press ENTER the compiler starts up and does it’s thing:

BIMLAutoCompileCommand

The output tells us that there are 9 packages created in each of its own projects.

I can find the compiled projects and packages in my defined output folder.

BIMLAutoCompileOutput

And the content of each folder.

BIMLAutoCompileOutputContent

Thats it. Easy job 🙂 thanks to Varigence and their MIST product.

Referencing the same custom script task in SSIS with BIML

Biml

In these times of data warehouse automation and fast track SSIS, BIML has become a huge contributor to my daily work.
On one of my most recent projects we had a custom script task in SSIS that we needed to implement in the same package several times.

The code of the custom script is in its own biml-file in the project and referenced from the main biml-file.

We kept getting a mysterious error when executing the package in debug mode from Visual Studio. (No warning upon building the packages).

After alot of debugging and hair pulling hours, we finally got our arms around the bug.

The error was that the ProjectCoreName of the new 3 script tasks in the same package had the same name.

My learning from this, and yes, I’m not a .NET developer, is that upon executing the custom script task, the SSIS engine must compile the code and store it for usage upon handling the data. This storage is shared with the whole package, and therefore cannot contain the same .NET project name. [Written from my personal understading – correct me if I’m wrong].

So for any future BIML users who wants to add the same custom script task to the BIML project – remember to add a variable to the ProjectCoreName of your biml-script.

This can be done fairly easy, as your biml-scripts can relate to the variables calling the biml-file. Just add the standard

<#=variablename#>

to he ProjectCoreName-tag of your biml-file containing the custom script.

Happy BIML-ing…

en_USEnglish
da_DKDanish en_USEnglish