The type of the value being assigned to variable differs from the current variable type.

August 12th, 2014
by Kristof

This post addresses the following error message.

The type of the value being assigned to variable differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

In my case, the error was because I tried to assign a varchar(max) to a string type variable in SSIS. It turns out that varchar(max) is not assignable to a string type object. The best option is to forcefully convert the value to a varchar(8000) or nvarchar(4000) before assigning it.

I previously stated that a string variable in SSIS can only contain 4000 characters, but that is not correct. A string in SSIS has an unlimited length, but the maximum length of an evaluated expression is 4000 characters, so concatenating over 4000 characters using an expression will result in an error. However, when assigning a value to a string from a resultset, the length is actually unlimited and you can assign over 4000 characters to a string using this method. This also means that you can work around the SSIS expression limitation by using a SQL box to concatenate two strings using a select ? + ?, for example.

Posted in SSIS | Comments (0)

How to differentiate in SSIS between design and execution mode

July 29th, 2014
by Kristof

Sometimes it is necessary to differentiate between design and execution mode. For example when you dynamically generate tables in your SSIS package. By making your package aware of the differences between design and execution mode, you can use existing tables during design mode but switch to the dynamically generated tables during execution mode. All you need to do is set the source for your table to a variable, and add the following expression to that variable.

@[System::CancelEvent] == 0 ? "Design mode" : "Execution mode"

The key is the System variable CancelEvent. This will be 0 when you are running in design mode, but will contain an integer during execution.

Posted in SSIS | Comments (0)

BUG: Dynamically altering Connection String for File Connection used in Execute Package Task.

August 26th, 2013
by Kristof

Setup
I designed the following setup.

[script task] -> [[for each loop container] [execute package task]]

The script task parses a variable and creates an array containing a list of packages to be executed.
The For Each Loop Container parses the array and assigns each item to a variable named @PackagePath.
The File Connection for the Execute Package Task refers to @PackagePath in the expression for its Connection String.

Expected behaviour
Expected behaviour would be for the EPT to open each package according to the value in @PackagePath.

Actual behaviour
Every first succesful iteration of the EPT will open whatever package the File Connection is initially configured with. The second iteration will open the package that was configured in the previous loop. The third iteration will open the package from the second loop et cetera.

The contents of the @PackagePath variable are correct. The connection however isn't updated until the FELC has finished processing the current loop.

Changing the FELC to list the packages in a certain folder and then open each package in that folder works perfectly, but fetching the items from an array will produce the behaviour as described above.

Solution
There is no actual solution for this, although I have a crude but effective workaround.

The workaround is to have a script file to set the connection string for the File Connection. It's crude, but it works.

Posted in Selfish | Comments (0)

SQL Server Management Studio shows result tab that does not match the query

April 22nd, 2013
by Kristof

I couldn't figure out why my SQL Server Management Studio (SMSS) would show results that would not match the query underneath. They all looked like results for sp_who and sp_lock, system stored procedures that can be used for fetching information from SQL Server.

It turns out that, by default, ctrl-1 and ctrl-2 are assigned to run sp_who and sp_lock. Since the shortcuts for switching between PC's on my KVM switch are set to ctr-alt-1 and ctrl-alt-2, I would regularly get this issue without really understanding what was going on.

If you want to get rid of this, simply go to Tools, Options, Keyboard and clear the text for the keys that you accidentally press once in a while.

Posted in Sql | Comments (0)

Hoofdkraan.nl: oude wijn in spamkruiken

April 5th, 2013
by Kristof

[This article is in Dutch. It pertains to a Dutch audience]

Het is een tijd geleden dat ik Nederlandse spam in mijn elektronische brievenbus kreeg. Het is eveneens een tijd geleden dat ik dubieuze aanbiedingen kreeg voor doorstuurpraktijken.

Hoofdkraan.nl is de trieste verantwoordelijke voor het einde van dat tijdperk. Uit naam van Maartje de Vries kreeg ik vandaag een mail met daarin de vraag of ik eenvoudig 20 euro wou verdienen door nog meer mailadressen te verzamelen voor deze spammer.

Hoofdkraan.nl is de nieuwe naam van freelancematch.nl, een bedrijf dat ondertussen blijkbaar niet meer bestaat. De reden waarom is wel duidelijk, aangezien freelancematch.nl herhaaldelijk negatief op het internet is geweest. Helaas heeft niemand daar wat van geleerd. Hieronder enkele links die de algemene teneur weergeven.

Slechte dienstverlening: http://hallo.kvk.nl/hallo/marketing/klanten_werven/f/144055/t/12439.aspx
Slechte dienstverlening: http://www.linkedin.com/groups/Wie-heeft-er-ervaring-met-929747.S.46446302
Slechte privacy en security: https://www.security.nl/artikel/37645/1/FreelanceMatch.nl_lekt_gegevens_10.000_freelancers.html

Wat nog meer op bizarre praktijken duidt is dat het telefoonnummer van het bedrijf niet in de spam-mail is opgenomen. Dat bewijst dat ze verwachten dat mensen niet blij gaan zijn en een extra buffer inbouwen om contact te vermoeilijken. Daarnaast blijkt dat het postadres in Amsterdam zit maar het telefoonnummer wat bij het bedrijf zit begint dan weer met 030 en dat zou er op duiden dat het bedrijf in Utrecht zit. Daarnaast zijn op de homepage een verzameling van logo's van kranten en tijdschriften te vinden, waardoor Hoofdkraan.nl de indruk wil wekken dat ze een "bonafide" partij zijn die gelinkt is aan uitgeverijen, maar niets is minder waar.

Heeft u zelf ook ongevraagd mail ontvangen van hoofdkraan.nl en wenst u deze spam stop te zetten? Rapporteer het bericht dan ook bij spamcop: http://www.spamcop.net/sc en bij de OPTA: https://www.spamklacht.nl/

Posted in Selfish | Comments (1)

SEO Powered by Platinum SEO from Techblissonline