Dynamics AX Mobile 1.5 SSIS Package Failure when using SQL Server collation Danish_Norwegian_CI_AS

Recently we received some feedback around the SQL Server with Danish and Norvegian collation and we will like to communicate this work around to solve the problem.

Error Description: When running the Dynamics Mobile SSIS Package in SQL Server 2005/2008 the package may fail with messages similar to:

                Package Execution Utility: “Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DBError has occurred. Error code 0x80040E14. An OLE DB record is available. Source: Microsoft SQL Native Client Hresult : 0x80040e14 Description: “Invalid column name DataAreaID”.

                SSIS Package in SQL Server: “Source: Activity DTS.Pipeline Description: One or more component failed validation. Code: 0xC0024107 Source: Activity Description: There were errors during task validation. DTExec: The package execution returned DTSER_FAILURE (1).”

Reason: The SQL Server variables defined in the SSIS package represent a SQL select statement that uses a column with the name “DataAreaID”. SQL Server throws and error as the combination of two A’s in the column name represent a special character in the Danish language.

Work Around: All the select statements must be changed so that the two “A” characters are the same case. Suggested change is to change “DataAreaID” to “DATAAREAID”. To do this;

1.  Navigate to the AX SSIS package (typically located in C:\Program Files\Microsoft Dynamics Mobile\Solutions\Microsoft Dynamics AX 2009 SP1\Mobile Sales\Server\Staging Database\SSIS 2005 or C:\Program Files\Microsoft Dynamics Mobile\Solutions\Microsoft Dynamics AX 2009 SP1\Mobile Sales\Server\Staging Database\SSIS 2008 depending on the version of SQL Server you are running.)

2. Open up the AX Package in Notepad (Mobile Sales-AX2009-Package.dtsx).

3. Perform an edit->Replace to all instances of “DataAreaID” and change it to “DATAAREAID”.

4. Save the file.

5. Package should now run successfully with the Danish collation.