Using Row Count data flow transformations

Posted by Jeff Rix on January 19, 2012 under SSIS | Be the First to Comment

Create an Int32 user variable with a package scope. Configure the Row Count property Custom Properties>Variable Name to the user variable.

You cannot use the Row Count variable in the same dataflow it is set because it is processing the records and sending them to the destination at the same time.

to use in the variable in an Execute SQL Task use an ADO.Net Connection Type and map the variable to an input parameter with the same data type using a parameter name that starts with the @ character.

In your query reference the variable using the parameter name and you will be all set!

Duplicate Keys in a dimensional hierarchy

Posted by Jeff Rix on January 13, 2012 under SSAS | Be the First to Comment

This  date hierarchy has year at the highest level and date at the lowest level. Months and quarters are between the highest and lowest levels in this data hierarchy.

Each year has a fourth quarter and each Quarter has the month of December.

The default property for dimensions in SSAS are set up to treat this as an error.

For most dimensions this would align with the business rules. If you had a football

league roster you would want the dimension to throw an error if it had a player

playing on more than 1 team.

Change the Key Duplicate Error Configuration to Ignore Error.