Pagina's

Thursday, January 21, 2016

SSIS: An Item With The Same Key Has Already Been Added

Problem
Recently I was working on a new project and was trying to deploy my SSIS project with several packages to the catalog for the first time. I received the error when trying to build the project:
2016-006

Explanation
This occurs when e.g. you copied a (package) connection manager to another package and later promoted one of the 2 to a project connection.
Let’s say you have package A and B, both with the same package connection manager ConMgr. When you promote ConMgrof package A it will become (project)ConMgr, but in package B it will still be ConMgr, the package connection, and the new project connection will not be shown.


Solution
(I found my solution @ SO.) By deleting the package connection ConMgr (in package B) the project connection will become visible in the package and (in my project) succesfully replaced the connections in all data flows. It could be that you have to go through all components that referenced the old package connection to redirect them to the (project)ConMgr instead.

Hope that helps!

Monday, January 18, 2016

SSRS: Sorting in Particular Order in a Report

This week I had the need to order values in a matrix in SSRS in a particular order. The names need to be ordered in alphabetical order, except for one name that always needs to come first. I’ll show you how I did it with a sorting expression in the Group.
I’ve taken a (light-weight) version of AdventureWorks for the examples.


Let’s take the Sales.ProductCategory and Sales.Product tables as an example. I’ve counted the number of Products of the 4 main (parentless) ProductCategories:
2016-001

The sorting in the Row Group Name is automatically done from A-Z. If you want it in any other order you’ll have to set it explicitly. You can either pick a(ny) column and set it to sort A-Z/Z-A or you can enter an expression. We’re now going to implement the latter.
When you click on the arrow of the Row Group Name in Row Groups box at the bottom of the screen and click on Group Properties… you’ll end up with the following screen:
2016-002

We want to leave the existing sorting expression as it is, because we can use it later. Add another row and click on the fx button of the new row to open up the expression dialog box:
2016-003

By entering the formula you see in the picture above, you’re saying to the sorting engine: Whenever the value of the field Name is equal to “Clothing”, give it a sort order of 0, otherwise give it a sort order of 1. When ordering from A-Z 0 comes before 1 so “Clothing” always comes before the rest. You can adjust this to your needs ofcourse to make a specific item come last, or sort all the items in particular order by nesting IIF statements.
When you click OK and click on the up arrow to sort on this expression first you end up with the following sorting options:

2016-004

Sorting on the Group is first done on the expression, so “Clothing” comes before anything else, second alpabetically on the Name, so the rest of the values are placed after ”Clothing” from A-Z. This gives me the same values as before, but now sorted differently:
2016-005

Tuesday, January 12, 2016

SQL (Orphaned) User Without a Login: HowTo Create a Login For The User

Whenever I restore a database from a customer in my development environment I have the issue of orphaned users: users in the database have no corresponding login on the server/instance.
I found this nice answer on SO that uses the sp_change_users_login stored procedure for this.
You can use sp_change_users_login to create a login for users already in the database.


USE databasename                      -- The database I recently attached
EXEC sp_change_users_login 'Report'   -- Display orphaned users
EXEC sp_change_users_login 'Auto_Fix', 'UserName', NULL, 'Password'
You get the UserName(s) from the sproc when you run it with @Action='Report'.

Thursday, December 10, 2015

Open Live Writer On GitHub

A few days ago an open source fork of the Microsoft Windows Live Writer code was announced. It is now called: OpenLiveWriter. It makes it easy to write, preview, and post to your blog. The current release has version 0.5.


As I host my blog on Blogger I encountered some issues when I wanted to login to the application while installing it. According to the issue tracker at GitHub this was a known issue and is being dealt with in the next minor release 0.5.1. I hope they are able to fix this soon as I'm eager to try it out!

Have a look at the project on Github or see what they have in mind for next releases at the roadmap!

Monday, November 30, 2015

Round a datetime to the nearest minute (or down to the minute)

Last week I was doing a comparison of 2 datetime fields, but I only wanted to do the comparison on the minute level. So I needed something to round the datetime to the nearest minute or down to the minute.

I found this great answer on SO:

DECLARE @dt DATETIME
SET @dt = '09-22-2007 15:07:38.850'
SELECT DATEADD(mi, DATEDIFF(mi, 0, @dt), 0)

And if you want to round up or down, you can add 30 seconds to the parameter like this:
SELECT DATEADD(mi, DATEDIFF(mi, 0, DATEADD(s, 30, @dt)), 0)