MDS 2016 is a big improvement over the previous versions. There are many enhancements in almost every area. One change I had not noticed in MDS 2016 is that a default value has been added for the “ErrorCode” attribute in the staging tables.
One situation where this may be an issue is when SSIS is used to stage data. If the “Keep nulls” value is checked in the Destination configuration, the insert will result in an error.
Problem
A common (and very well documented) best practice for performance is to specify the columns in a SQL statement instead of using SELECT *. This applies to SSIS in the Data Flow Task, but there are other reasons to avoid this too.
At a recent client the current member row was being recreated daily for a SCD 2 dimension. The data in the source rarely changed, so it was obvious that something was wrong.
My first step was to look at the code, including the Conditional Split task. All of the logic there looked correct – all of the comparisons appeared to be functioning as intended and NULL values were being accounted for in the code.
I then checked some of the columns to determine if there was a problem with spaces by using the RTRIM() and LTRIM() functions. That reminded me that there is still not a TRIM() function. All of this still looked to be functioning correctly.
Finally I checked the Source and Destination Connection Managers and found they were configured to use tables as a source. The end result of this was that the current member rows were not filtered in the Destination and all rows were incuded.
Solution
I changed the Destination SQL statement to select only the current member rows selected in the WHERE clause. This resolved the issue and the SCD 2 no longer created duplicate rows.
Recently I was working on a project to extract data from CSV files for a client. The package was pretty standard and it ran fine under my account in BIDS. The issue occurred when I tried to deploy and executed under the service account. The error message “Description: The file name property is not valid. The file name is a device or contains invalid characters” was not too helpful, but it did lead me in the right direction.
After asking around I found that another developer had a similar issue and pointed me to a couple of blog posts:
http://www.sqlservercentral.com/Forums/Topic1128829-148-2.aspx
It turned out that the service account had access to the directory that contained the CSV files, but not the entire path.
\\Server\Directory 1\Directory 2\CSV Files\My File.csv
Once the account was granted access to ‘Directory 1’ and ‘Directory 2’ the issue was resolved. Hopefully this post will save someone some time if they run into this.
This error message appeared the other day and it turned out it wasn’t as bad as it looked.
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.
The protection level is set to EncryptSensitiveWithUserKey on the package and the account password used to access the source database was changed. Once the password was modified in the package everything ran as expected.