Date continued

When you have the DateID as an int that represents a real date it can be good to be able to use SSIS to convert datetime to int in an easy way.

In this blog post by Mike Davis shows a very easy to do it. You can find his code below

(year(@[User::dtStartDate]) * 10000)+(month( @[User::dtStartDate]) * 100)+Day( @[User::dtStartDate])

Posted in BI, SSIS | Tagged , , , , , | Leave a comment

Don’t book Lufthansa online

I’ve been trying to book a flight with lufthansa online for about 5 hours now. Tried it with different browsers, credit cards and different options in the booking. After a while I call the technical support who says that there is obviously some error and that I should call phone booking. Phone booking wants to charge 100 Euro extra for the “service” even though Lufthansa is at fault.

I’m not booking there again..

Posted in Uncategorized | Leave a comment

Ett inlägg på temat Svensk bostadspolitik

Ett inlägg som jag först tänkte posta som svar i den här diskussionen.

Efter att ha läst ett inlägg som kantas av framför allt politisk fanatism så kände jag att jag behövde skriva min syn på saken.

Oavsett politiskt styre i Sverige har vi haft

  • Ränteavdrag
  • Bostadsbidrag
  • Byggbidrag
  • Skatt/avgift på fastighet
  • Kapitalskatt
  • Hyresregliering

Vilka effekter får de här sakerna?

  • Ränteavdrag gör så att det är bättre att köpa nu och betala sen.
  • Bostadsbidrag gör så att folk bor större än vad de egentligen har råd med.
  • Byggbidrag gör så att fastighetsbolag bygger färre bostäder (bygger bara när de får bidrag, alla får inte alltid bidrag)
  • Skatt/Avgift på fastighet gör så att det bli dyrare att bo för alla. att den pendlar lite nu och då skapar en osäkerhet för folks boendekostnad
  • Kapitalskatt gör så att det är “dumt” att spara till sin bostad och sedan amortera på den
  • Hyresregleringen, sist men inte minst, gör så att vi helt enkelt inte vet vad folks eller företags värderingar av bostäder/fastigheter egentligen är. Detta leder till handel med hyresrätter samt tvingar folk som egentligen vill bo i en hyresrätt till att köpa en bostadsrätt (om de har råd) eller lösa sitt boende på annat sätt (föräldrar, kompisar, flytta till annan stad, gatan, m.m.)

Nytt på listan över bostadsrelaterade subventioner/avgifter/regleringar är R*T-avdragen. som bostadsrättsägare tar jag emot denna, principiellt vidriga, subvention. Min fråga är egentligen bara varför arbetande människor i hyresrätter ska sponsra den subventionen?

Kontentan blir då att vi inte vet vad som är ett rimligt långsiktigt pris på bostäder. Är det en bubbla? Vad händer om vi tar bort subventioner och regleringar?

Med rådande skatte- och bostads-politik så skulle jag påstå att vi sannolikt inte har någon bubbla men om vi tar bort subventionerna och regleringarna så har vi sannolikt en bubbla.

 

Posted in Taher tycker | Tagged , , , , , , , , | Leave a comment

Page numbers in SSRS when exporting to word

Recently I had some problems when developing a report in SSRS that needed to be exported to word.

In the page header I wanted to display the page number in the following format:
page x(y)

Should be simple enough
="Sida "+CStr(Globals!PageNumber)+" ("+CStr(Globals!TotalPages)+")"
or
"page: " + Globals!PageNumber.ToString + " of " + Globals!TotalPages.ToString

But that didn’t work. In MS Word the header always said page 1(1). The solution is to use “Expression with Text Runs” as stated on technet. In the page number example that means
="Sida " & Globals!PageNumber & " (" & Globals!TotalPages & ")"

Posted in BI, IT, SSRS | Tagged , , , , , , , | Leave a comment

Creating and populating a date dimension with Swedish or Norwegian weeks

Quite often when you work with analyzing data you need to be able to see your KPI’s per day, week or some other time-frame. Both Sweden and Norway has a fairly unusual way to handle week numbers which might make it a bit difficult to find scripts that are ready to use. Here is my DimDate table and the procedure I have to populate the table.

This script is based on a post by Robin Langell.

The changes that I have made are:

  1. Changed object names to English
  2. Added dayInYear
  3. Use ISO_WEEK instead of WEEK (gives Swedish/Norwegian week behavior)
  4. Made it into a procedure
  5. Set start and end date (instead of creating a certain number of days)
  6. Made Sunday to be day number 7 (instead of day 0)

Ofcourse feel free to let me know if there are any errors in my code!

Creating the table:

use [YourDB]
go

declare @table as varchar(50)
SET @table = 'DimDate'

IF NOT EXISTS (
	select 'Y'
	from sys.tables
	where name = @table
	)

	BEGIN
		CREATE TABLE dbo.dimDate (
			ID	INT NOT NULL,
			[Day]	DATETIME NOT NULL,
			[Week]	INT NOT NULL,
			[Month]	INT NOT NULL,
			[Quarter]	INT NOT NULL,
			[Year]	INT NOT NULL,
			DayInYear INT NOT NULL,
			DayInMonth	INT NOT NULL,
			dayInWeek	INT NOT NULL,
			YearWeek	INT NOT NULL,
			MonthWithZero NVARCHAR(2) NOT NULL,
			[DayName]	NVARCHAR(10) NOT NULL,
			[MonthName]	NVARCHAR(20) NOT NULL
		CONSTRAINT PK_DimDatum_datumNyckel PRIMARY KEY (ID)
		);
		SELECT 'Table did not exist, table '+ @table +' created';
	END ELSE
		IF NOT (
			SELECT COUNT(*)
			FROM DimDate
			) > 0
		BEGIN
			Drop Table DimDate;

			CREATE TABLE dbo.dimDate (
				ID	INT NOT NULL,
				[Day]	DATETIME NOT NULL,
				[Week]	INT NOT NULL,
				[Month]	INT NOT NULL,
				[Quarter]	INT NOT NULL,
				[Year]	INT NOT NULL,
				DayInYear INT NOT NULL,
				DayInMonth	INT NOT NULL,
				dayInWeek	INT NOT NULL,
				YearWeek	INT NOT NULL,
				MonthWithZero NVARCHAR(2) NOT NULL,
				[DayName]	NVARCHAR(10) NOT NULL,
				[MonthName]	NVARCHAR(20) NOT NULL
			CONSTRAINT PK_DimDatum_datumNyckel PRIMARY KEY (ID)
			);
			SELECT 'Table '+ @table +' existed without data, table dropped and recreated';
		END ELSE
		SELECT 'Table '+ @table +' contains data, no action taken';

And then creating the procedure:

Use [YourDB]
go

IF EXISTS (
	SELECT 'Y'
	FROM sys.objects
	WHERE name = 'AddDates'
	)
	Drop proc AddDates
go

CREATE PROC AddDates
	(
	@StartDate as DateTime,
	@EndDate as DateTime
	)
AS 

  DECLARE @CurrentDate AS DATETIME
  SET @CurrentDate = @StartDate
  WHILE @CurrentDate <= @EndDate
	BEGIN
		INSERT INTO dbo.DimDate
		SELECT
		CAST(CONVERT(nvarchar(10), @CurrentDate, 112) as int) as ID,
		CAST(CONVERT(nvarchar(10), @CurrentDate, 112) as DATETIME) as [Day],
		DATEPART(ISO_WEEK,@CurrentDate) as [Week],
		MONTH(@CurrentDate) as [Month],
		DATEPART(Q,@CurrentDate) as [Quarter],
		YEAR(@CurrentDate) as [Year],
		DatePart(DayOfYear, @CurrentDate) AS DayInYear,
		DATEPART(DD,@CurrentDate) as DayInMonth,
		CASE WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 0 THEN 7
			ELSE DATEPART(WEEKDAY,@CurrentDate) -1
		END as dayInWeek,
		CASE WHEN DATEPART(WEEK,@CurrentDate) < 10 THEN CAST(YEAR(@CurrentDate) AS NVARCHAR(4)) + '0' +  CAST(DATEPART(WEEK,@CurrentDate) AS NVARCHAR(2))
			ELSE  CAST(YEAR(@CurrentDate) AS NVARCHAR(4)) + CAST(DATEPART(WEEK,@CurrentDate) AS NVARCHAR(2))
		END as YearWeek,
		CASE WHEN MONTH(@CurrentDate) < 10 THEN '0' + CAST(MONTH(@CurrentDate) AS NVARCHAR(2))
			ELSE CAST(MONTH(@CurrentDate) AS NVARCHAR(2))
		END as MonthWithZero,
		CASE
			WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 1 THEN 'Måndag'
			WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 2 THEN 'Tisdag'
			WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 3 THEN 'Onsdag'
			WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 4 THEN 'Torsdag'
			WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 5 THEN 'Fredag'
			WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 6 THEN 'Lördag'
			WHEN DATEPART(WEEKDAY,@CurrentDate) -1 = 0 THEN 'Söndag'
		END	as [DayName],
		CASE
			WHEN MONTH(@CurrentDate) = 1 THEN 'Januari'
			WHEN MONTH(@CurrentDate) = 2 THEN 'Februari'
			WHEN MONTH(@CurrentDate) = 3 THEN 'Mars'
			WHEN MONTH(@CurrentDate) = 4 THEN 'April'
			WHEN MONTH(@CurrentDate)= 5 THEN 'Maj'
			WHEN MONTH(@CurrentDate)= 6 THEN 'Juni'
			WHEN MONTH(@CurrentDate)= 7 THEN 'Juli'
			WHEN MONTH(@CurrentDate)= 8 THEN 'Augusti'
			WHEN MONTH(@CurrentDate)= 9 THEN 'September'
			WHEN MONTH(@CurrentDate)= 10 THEN 'Oktober'
			WHEN MONTH(@CurrentDate)= 11 THEN 'November'
			WHEN MONTH(@CurrentDate)= 12 THEN 'December'
		END	as [MonthName]
		SET @CurrentDate += 1
    END
go

And finally populating the table

exec AddDates '19700101','20201231';
Posted in BI, IT | Tagged , , , , , , , | Leave a comment

Blast from the past

taher.se/Centralen and taher.se/shield
There you have som old school gaming stuff. Don’t know if the game is still around.. I see that I don’t have the images, I guess they were to big for me to save =)

Posted in Uncategorized | Tagged | 1 Comment

SSIS – How to handle sensitive data

While working with developing SSIS you will eventually have to deploy your packages to a production environment. Chances are that you run in to this error message

Error at  []: Failed to decrypt protected XML node “DTS:Password” with error 0x8009000B “Key not valid for use in specified state.”. You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

If you do then this blog post and this one will help you understand why and how you should handle it in the future.

In short: Before you start developing your packages you need to think about deploying the solution to the production environment. What you should do is set your package cariable called ProtectionLevel to DontSaveSensitive, why? Cause you should store your sensitive data in a database and keep your connection data to that table in one package variable. The first link above explains why and the second link above explains how.

 

 

Posted in IT, SSIS | Tagged , , , | Leave a comment

SSIS naming conventions

I would suggest using the following naming conventions when working with naming conventions. Why? Naming conventions are good as it makes it easier for you and your fellow developers to read the existing packages. Why this set? The list comes from the Microsoft most valued professional (MVP )Jamie Thomson. The list is taken from this post

http://consultingblogs.emc.com/jamiethomson/archive/2006/01/05/SSIS_3A00_-Suggested-Best-Practices-and-naming-conventions.aspx

The acronymns below should be used at the beginning of the names of tasks to identify what type of task it is.

Task Prefix
For Loop Container FLC
Foreach Loop Container FELC
Sequence Container SEQC
ActiveX Script AXS
Analysis Services Execute DDL ASE
Analysis Services Processing ASP
Bulk Insert BLK
Data Flow DFT
Data Mining Query DMQ
Execute DTS 2000 Package EDPT
Execute Package EPT
Execute Process EPR
Execute SQL SQL
File System FSYS
FTP FTP
Message Queue MSMQ
Script SCR
Send Mail SMT
Transfer Database TDB
Transfer Error Messages TEM
Transfer Jobs TJT
Transfer Logins TLT
Transfer Master Stored Procedures TSP
Transfer SQL Server Objects TSO
Web Service WST
WMI Data Reader WMID
WMI Event Watcher WMIE
XML XML

 

These acronymns should be used at the beginning of the names of components to identify what type of component it is.

Component Prefix
DataReader Source DR_SRC
Excel Source EX_SRC
Flat File Source FF_SRC
OLE DB Source OLE_SRC
Raw File Source RF_SRC
XML Source XML_SRC
Aggregate AGG
Audit AUD
Character Map CHM
Conditional Split CSPL
Copy Column CPYC
Data Conversion DCNV
Data Mining Query DMQ
Derived Column DER
Export Column EXPC
Fuzzy Grouping FZG
Fuzzy Lookup FZL
Import Column IMPC
Lookup LKP
Merge MRG
Merge Join MRGJ
Multicast MLT
OLE DB Command CMD
Percentage Sampling PSMP
Pivot PVT
Row Count CNT
Row Sampling RSMP
Script Component SCR
Slowly Changing Dimension SCD
Sort SRT
Term Extraction TEX
Term Lookup TEL
Union All ALL
Unpivot UPVT
Data Mining Model Training DMMT_DST
DataReader Destination DR_DST
Dimension Processing DP_DST
Excel Destination EX_DST
Flat File Destination FF_DST
OLE DB Destination OLE_DST
Partition Processing PP_DST
Raw File Destination RF_DST
Recordset Destination RS_DST
SQL Server Destination SS_DST
SQL Server Mobile Destination SSM_DST

 

Posted in IT, SSIS | Tagged , , , , | Leave a comment

SSIS Script component task

Are you getting some weird errors while working with the Script component task in SSIS?

I got errors like

Ambiguity between Input0Buffer.x and Input0Buffer.x

or

The name ‘Output0Buffer’ does not exist in the current context

and while googling the errors I didn’t find much help on the subject.

The errors I had where not caused by the actual Visual C# code I was working on but some settings on hte script component task that I had overlooked.

In my case it was the “SynchronousInputID” that should have been set to “None”. When that was done the errors dissappeared.

Posted in IT, SSIS | Tagged , , , , , | 3 Comments

Ice cream in the sun

Had a nice afternoon with some ice cream in the sun.

Really nice =)

Posted in Uncategorized | Tagged , | Leave a comment