Get Inserted, Updated and Deleted members from MDS
To get all inserted or updated members from a SQL Server MDS database, subscription views are used. Deleted members can’t be found in the views, so another technique must be used.
Running an MapForce mapping from SSIS
In an earlier blog post I demonstrated how to map between hierarchical (XML) and relational (SQL) data using Altova MapForce. I also mentioned that MapForce can generate code (C#, C++, XSLT for instance) to execute the mapping in an environment outside MapForce. This being a blog mostly about SQL Server, one obvious question is how to run the generated mapping from SSIS.
Max or min value from a set of columns
The min and max aggregates in SQL Server are normally used to work over rows, getting the max or min value from a set of rows. But what if you want to get the max or min value from a set of columns instead?
Default collation different from instance default
If you have databases with another default collation than the instance default, you may get something like the following error:
[red_box]Msg 468, Level 16, State 9, Line 30 Cannot resolve the collation conflict between “Finnish_Swedish_CI_AS” and “Finnish_Swedish_100_CI_AS” in the equal to operation.[/red_box]
Error 3624 when creating natively compiled stored procedure
Sorted data flows in SSIS
Sometimes a data flow in SSIS needs to be sorted. It may be because the business rules require sorted data, or it may be that you want to use either a Merge or Merge join component. Those component won’t work if the data isn’t sorted previously.
So, how does SSIS know if the data is sorted?
Add missing lookup in SSIS
Before a fact table is loaded, a common pattern is to do a lookup in SSIS before the loading. The lookup can be some key from a dimension for instance, or maybe a repository of primary keys in a MDM solution. But what if the lookup doesn’t return a matching value? Sometimes when this happens, we want to insert new values to the lookup table (to get the key) before the loading of the fact table starts. Here I’ll show one way to handle this scenario.
T-SQL function SERVERPROPERTY
The T-SQL function SERVERPROPERTY returns a lot of properties about the current SQL Server instance. The list of parameters it accepts can be found on Technet. On the top of the page, choose the version of SQL Server you are running.
Table Valued Parameters in Stored Procedures
In most cases stored procedures are developed so that it accepts a number of input parameters with a single value. But a common requirement is to be able to submit multi value parameters. That is, parameters that accept several values as opposed to a single value.
In SQL Server 2008, table values parameters in stored procedures was introduced.