Friday, January 19, 2018

SSDT Error Message: Unable To Cast COM Object of Type 'X' To Interface Type 'Y'

Just a short blog post today explaining a problem (and solution) I ran into when working at a client a while ago. Hopefully it is helpful for anyone.

Problem

I got this error message at a client when I tried to open an SSIS solution in SSDT:

Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSObject100'

According to this source the reason could be a badly applied Service Pack or CU for example, but I’m not entirely sure about that in my case.

Solution

The solution for this is to reregister the dll that is referred to, in this case: dts.dll.
Before starting, make sure you close any open Visual Studio instance.
In a Command Prompt go to the following path (be sure to start the Command Prompt as Administrator):

C:\Program Files\Microsoft SQL Server\120\DTS\Binn\

where 120 is you SQL Server version number.
Reregister the dts.dll by executing the command:

regsvr32 dts.dll

image

If all goes well you should see the following message pop up:

image

I found info here.


Happy coding :)

@NickyvV

Thursday, December 14, 2017

T-SQL Tuesday #97: Setting Learning Goals for 2018

This months T-SQL Tuesday is hosted by Malathi Mahadevan (@sqlmal | b).
Besides my strict learning goals I’ll also provide my retrospective for 2017.

Data Science Program

I recently started the Microsoft Professional Program for Data Science, of which I completed the first 3 courses already. I want to complete course 4 before the end of the year. That leaves me with 5 courses and the capstone project. Finishing this program in the Fall of 2018 seems doable, so I’ll put that in my calendar!

MCSE

Ow, already 3 years back I passed my final exam for my MCSA. Until recently the progress to my next goal (MCSE) kind of stopped. I already had one attempt at 70-464 this year, but I failed… I guess I underestimated that one a bit, so that’s one reason I’m not going to take my next exam too early. I just didn’t have too much time to work on it since my recent switch out of consultancy and to Van Lanschot Bankiers. Another reason is the fact I also started the Data Science Program, and the fact that this year is almost finished. I’d rather wait a few months and have my MCSE for 2018 already.

Podcasts

The fact that I made the switch to Van Lanschot also meant less commuting, which is a good thing of course, but not for my time listening to podcasts. I’m having trouble keeping up with all the feeds I’m in at the moment so I’ll have to either cut the feeds or find some other time (during running maybe?) to listen to the podcasts.

Books

As you can see on My Reading Journey page I made some steady progress on my reading goal. Although there have been days and weeks I didn’t read, since I started tracking my progress again in my spreadsheet I see I keep advancing more steadily. I still have some days I don’t read at all, but at least now this is visible and I can hold myself accountable for that. The books I read this year have been a good mix between technical, personal skills and process oriented topics. I’m going to switch my technical topics this year a little bit to more closely match my work at Van Lanschot. In the beginning of next year I’ll write a blog post about my first months here.

Running

As for my personal goals: I completed 2 half marathons this year in Venlo and Eindhoven, with a PR of 1:38:59 in Eindhoven this October! Now on to a PR on the 10 km, 40 minutes would be a great goal, as my PR is now 44:27.


@NickyvV

Friday, November 17, 2017

SSRS: The Hidden Expression For the Tablix Has a Scope Parameter That Is Not Valid For an Aggregate Function

A while ago I ran into an issue when creating an SSRS report. It was a bit of a cryptic error message for me at first, as you can see below. After struggling with it for a while I finally found the issue and I thought I could as well share it so you can find it faster than I did. :)

Problem

When running my report I encountered this error message. It talks about a Hidden expression of Tablix1, and that the scope parameter used is not valid. After finding the solution the message was kind of clear to me, but the thing is, when running the report at first I was a little lost.

nickyvv.com - SSRS: The Hidden Expression For the Tablix Has a Scope Parameter That Is Not Valid For an Aggregate Function - Report error

Solution

It is very obvious the message is about Tablix1, so I start with looking at aggregate functions I am using in that tablix. Finally, I end up checking the visibility of the tablix and I notice the expression for the display option of the tablix.

nickyvv.com - SSRS: The Hidden Expression For the Tablix Has a Scope Parameter That Is Not Valid For an Aggregate Function - Display options

This feature is used to show or hide the tablix based on an expression. You can see the expression I used below.

nickyvv.com - SSRS: The Hidden Expression For the Tablix Has a Scope Parameter That Is Not Valid For an Aggregate Function - Expression Editor

As the Expression dialog states, you have to Set the expression for: Hidden. This actually means the following: if the expression evaluates to true, the tablix will be hidden. So if the expression evaluates to false, the tablix will be shown.
The problem however, is in my scope parameter: “DataSet1”. This scope parameter is optional for an aggregate function that can evaluate the aggregate function over that parameter. It can be a Row or Column group, a data region or (as it is here) a dataset.

TL;DR

The problem is, when I started with the report I entered this expression, but afterwards I altered the name of the dataset. This makes the above Hidden expression for the tablix invalid because Dataset1 doesn’t exist anymore.
So after updating the name of my dataset in the expression for the tablix my report ran just fine again.


If you have any remarks or questions, feel free to contact me on Twitter:
@NickyvV