Read more about the article Running an MapForce mapping from SSIS
Mapforce 2014

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.

(more…)

Continue ReadingRunning an MapForce mapping from SSIS
Read more about the article Using MapForce to insert hierarchical XML to relational tables
Mapforce 2014

Using MapForce to insert hierarchical XML to relational tables

In an earlier blog post, I demonstrated a way to insert a hierarchical XML document into a relational table structure by using XQuery. That solution works well in smaller solutions, but it’s a “code only” solution that can become complex and hard to maintain in larger projects.

An alternative solution would be to use the graphical mapping tool Mapforce from Altova.

(more…)

Continue ReadingUsing MapForce to insert hierarchical XML to relational tables
Read more about the article Default collation different from instance default
Microsoft SQL Server

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]

(more…)

Continue ReadingDefault collation different from instance default
Read more about the article Error 3624 when creating natively compiled stored procedure
Microsoft SQL Server

Error 3624 when creating natively compiled stored procedure

I was trying to upgrade my MACD technical analysis calculation to use the new In Memory technologies introduced in SQL Server 2014. The statement used in the SQL 2012 calculation worked just fine until I put it in a natively compiled stored procedure.

(more…)

Continue ReadingError 3624 when creating natively compiled stored procedure
Read more about the article Add missing lookup in SSIS
Lookup

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.

(more…)

Continue ReadingAdd missing lookup in SSIS
Read more about the article Table Valued Parameters in Stored Procedures
Microsoft SQL Server

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.

(more…)

Continue ReadingTable Valued Parameters in Stored Procedures