Month: January 2007

Working with SSIS Part#1

It is often necessary when working with customer data to do import routines that will input each individual customer’s data. One way whihc is often implemented in Sales is to have the individual sales personnel input their weekly prospects into a predefined Excel spreadsheet and then to send that off to be placed on a common directory location. Your job would eb to write the SSIS package that would process it into the database. Luckily with SQL Server Integration Services 2005 this is an easy task. Drag a ForEach Container onto the work surface. For this we will want to also set a variable. So create a new string variable in the variable pane called strCurrentFile. This is what we will store the current file being accessed in the Loop. Rigth click on the ForEach Container and select Edit. Click on Collections on the left hand pane and select ‘Foreach File Enumerator’ from the Enumerator drop down at the top. Now browse and select the folder that you wish to be searched and place ‘*.xls’ in the Files box in order to tell the control that you want all of the Excel files in this directory. We also want to make sure that the fully qualified path option is selected. Once you are done your Properties pane should look something similar to the image below. Now click on the...

Read More

AS2005 Many-to-Many Relationships

I have been working for the past couple of months with Analysis Services 2005 and trying to get a hold on the Many-to-Many relationships. It si a pretty simple concept when you get down to it. AS2005 takes care of the many-to-many relationship and all that is required of you is to provide it with a simple intermediate mapping table. The only problem seems to be that when you actually get the aggregations for the group. I cannot have a relationship as follows: So we have this diagram in which there are Accounts that can have many clients and clients may have many accounts. However, we also have a Groups table in which you are able to group Accounts. However, these accounts can belong to many groups. When we proces such a heirarchy through AS then it does not seem to process the aggregations properly. What make it worse is that if I have another dimensional table attached to the fact table that tells me perhaps something like Manager. I query to give me something like ‘Give me all of the Groups and Accounts for this Manager person with their corresponding totals’ it kind of pukes and instead gives me the Manger with ALL of the Groups …even if the Manager is not assigned to any of the accounts contained within them???? Instead I have to match up subsets...

Read More

Distinct Component for SSIS

If anyone has read Jamie Thomson’s piece A Distinct Component Please! .. I would like to ititerate his frustration. This seems like a simple thing for the powers that be on the business intelligence end to come up with. Of course, there have been some simple illustrations on how to do this via hash-tables and the script component but I would have to wonder how these scale for very large sets like the ones in which I am used to dealing with. Of course, there are already third party components in the works like NSort but this seems like such a simple component that not having it as a default in Business Studio is ridiculous. Of course, if anyone has a really good piece of custom code that they have implemented with the script component that scales smoothly then I would appreciate dropping me an...

Read More

SQL Server 2005 SP2

Okay so it has been about a month now since the SP2 CTP for December has been released and I am still awaiting the official release of the service pack. For those of you that are uninitiated with what the service pack contains …well, the biggest thing for me is the addition of vardecimal type. I work with a lot of financial data warehousing applications and this is a godsend. It will save a lot of space for me on my servers so I can’t wait to get a full fledged version so that I can load on my production systems. If anyone hears anything about a possible release date then please let me know…it was promised for Q1 of this year but time is ticking and no announcement...

Read More