Neal Waterstreet

  • Home
  • About
  • Book Reviews
    • 2018 Reading List
    • 2017 Reading List
    • 2016 Reading List
    • 2015 Reading List
    • 2014 Reading List
    • 2013 Reading List
  • Projects
    • OBi200
    • DS716+ memory upgrade
    • Cord cutting
  • Presentations
  • Contact

Default value for “ErrorCode”

March 15, 2018 by neal Leave a Comment

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.

Filed Under: MDS, SSIS Tagged With: Entity-based staging, Leaf Tables

Problem with SCD

February 15, 2015 by neal Leave a Comment

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.

Filed Under: Data Warehouse, ETL, SSIS

Accessing CSV files on a UNC file share

February 8, 2015 by neal Leave a Comment

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

http://sqlblogcasts.com/blogs/simons/archive/2010/03/03/SSIS—Access-Denied-with-UNC-paths—The-file-name-is-a-device-or-contains-invalid-characters.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.

Filed Under: SSIS, Work

Error 0x8009000B “Key not valid for use in specified state.”

April 8, 2013 by neal Leave a Comment

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.

Filed Under: SSIS Tagged With: Data Warehouse, ETL, SSIS

  • 1
  • 2
  • Next Page »
July 2025
M T W T F S S
 123456
78910111213
14151617181920
21222324252627
28293031  
« Mar    

Blogroll

  • Atlanta MDF
  • Atlanta Microsoft Business Intelligence Group
  • Atlanta Modern Excel User Group
  • Chris Webb's BI Blog
  • codegumbo
  • Lance England
  • PowerPivotPro
  • Prologika
  • SQLBI

Tags

#SQLSatATLBI Aereo at Atlanta Microsoft BI Azure Azure DataFest Backups Batch Certification changeset Chicago Conference CrashPlan Data Modeling Data Quality DAX Deployment DVR Entity-based staging Error Messages Excel Filter Goals Headphones Healthcare Leaf Tables MDM MDS MDS Add-in for Excel Oops! Something went wrong PASS Healthcare VC Power BI Power Pivot review Roku SQLSaturday SQL Saturday 800 SQLSaturday Atlanta BI SSIS Streaming Subscription View Time Training Validation Version Flag

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Copyright © 2025 · Minimum Pro Theme on Genesis Framework · WordPress · Log in