After sometime now using SSIS to load data into other systems such as Microsoft CRM and Oracle Hyperion, I have finally started down the path towards building my first Data Mart using SSIS.
At this stage I am really looking at what is going to work best so I have built some dimensions and now I am loading a fact with ‘actuals’ taken from the Navision General Ledger.
I am attempting to follow the Kimball method, and in loading the data I am performing lookups using the Lookup Transformation on the dimensions to retrieve the surrogate keys. Standard stuff so far!
I have one particular dimension where the natural key is of type varchar(20). The corresponding column in the data stream is also of type varchar(20). The data loads with some lookup errors all of the same string – ‘STT2003’. Okay, so it must be missing from the dimension. Imagine my surprise when I find that ‘STT2003’ is in the dimension!
So I checked the types – both varchar(20). I checked the string length on the lookup in case there were any non-printing characters but nothing. I applied the ltrim and rtrim functions to the source data but still no luck. Fortunately, I do not have a lot of hair to pull out. So I search the MSDN forum for SSIS and I saw a reference to a similar problem which referred to string length. But, I had already done that – or had I ?
So I applied the ltrim/rtrim functions to the natural key in the lookup and it worked. It seems that the key had acquired extra characters in the lookup.
Stay tuned for the next installment.
My official job title is Database Systems Administrator. You might think that I spend my whole working day tinkering under the bonnet of our data base platform – SQL Server 2005. I wish!
Being part of of a relatively small I.T Department in a company of less than 500 employees means that I have to do more than just databases.
In my previous working life I have spent a good part of it developing Business Intelligence applications with products from Oracle, Business Objects, and Cognos. In my current workplace, the tools of choice are Integration Services for ETL, Reporting Services for Relational Reporting, ad Hyperion Planning and Essbase for Planning, Reporting and Analysis.
I have also worked with web tools such as ColdFusion and ASP.NET as well as Windows Forms applications using VB.NET. I am currently building Web Services using Visual Studio 2005 – using a n-tiered architecture for an Online Sales application.
All of these tools that we use are complex and require a considerable investment of one’s time just to learn the basic concepts. How does one keep pace and balance one’s family life ? (I have two children under six years old).
My wife and I have taken to Podcasts as a way of keeping up with things while doing something else.
For me, it’s the tech podcasts such as TWIT, .Net Rocks, and Windows Weekly with Paul Thurrott. For example, I was listening to one such pod cast on the weekend from .NET Rocks where Carl and Richard interviewed Michelle Leroux Bustamante about WCF. At the same time, I was helping my wife clean the house. So now I have some understanding of Contracts, Claims, Cardspace, SML and other stuff that I would not have learnt about unless I sat down and ploughed through all the documentation at MSDN.
I have been using this tool for a while now and it contains a lot of good things that, in general, augurs well for future releases.
I have griped before in the News Group about some things that are way too complicated compared to other ETL Tools such as Informatica and Cognos DecisionStream.
One of things that really annoys me is that the property: ValidateExternalMetaData is always set to True. Basically, it assumes that all tables will pre-exist. As we all know, tables are apt to be changed and hence our code contains bits that create tables and indexes. Hence one has to set the property to False if a source or destination is dynamically generated.
I just want to set it’s default to False. Where does one do this?