Yet another couple of hours wasted searching a silly microsoft ssis bug, sorry, intended feature. I tried to use the Execute SQL Task in a package, that should simply execute a stored procedure with an output datetime parameter that should be mapped to a local datetime variable inside of the package. Simple enough, but it constantly failed throwing this error message:

[Execute SQL Task] Error: Executing the query “EXEC GetCurrentDate @CurrentDate=? ” failed with the following error: “The type of the value being assigned to variable “User::CurrentDate” differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. “. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

My first thought was that the datatype of the local variable I was mapping to was incorrect, but after trying to map to all available and somewhat meaningful datatypes I realised that this was not the problem. After some searching I finally found the answer on StackOverflow (where else?).

Excerpt from the Microsoft Connect Bug Reporting Pages:

We are closing this case as this is expected behaviour and is a result of the new sql datetime type change. You are using a native oledb connection manager for sql task, in the process of COM interop, we use VARIANT to hold the value and the only way to prevent data loss is to store the value as BSTR variant. If you change User::dateParam to String type it will work, or you can switch to use managed connection manager to bypass the COM interop.

Long story short: just change the variable type of your local package variable to string and it magically works.

We have chosen to use relative paths in Integration Services on several projects now, and since it has worked well, it is time to share this tip with the community. The whole purpose of using relative paths is to make packages more portable, as opposed to packages and dtsConfig files having to live in a specific directory structure or drive letter on servers and developers’ laptops.

This was a very helpful resource for my package development and deployment routine today, thanks!

A great MSDN article about how you can squeeze the best performance out of your package dataflows.

Update: I just found some more performance tuning articles while doing some research on other SSIS related stuff.

Optimizing SSIS Package Performance Part 1 and Part 2 describe whats going on under the hood of the two main engines of SSIS: the runtime and the data-flow engine.

I just read an interesting article that helps me simplify my incremental load packages by using a checksum of the processed row to determine if the underlying data has changed. While scanning through the comments of the article, I realised that using the simple checksum component recommended in the article could eventually lead to missing a data change. Luckily there is a advanced hashing component available, supporting MD5 and SHA1 hashes.

I’m just trying to configure a custom logging mechanism for my SSIS packages and had some problems with the location and structure of the dbo.sysssislog table which is automagically created by the Integration Services (if you happen to use a integrated security database connection; else you need to grant CREATE rights to the corresponding database user). While googling I found some helpful tips by Joe The Business Intelligence Guy:

Read More