Sparx EA – Data Type for SQL Server 2008

 

I was modeling a database for SQL Server 2008 in Sparx Enterprise Architect 8.0, but when I looked in the data types list (“Settings | Database Datatypes) I couldn’t find SQL Server 2008 in the product name list (although it is said to be added since the release of EA 7.5 build 843).

On the site http://www.sparxsystems.com.au/resources/ I’ve found an xml containing all the data types for SQL Server 2008.

To add the data types for SQL Server 2008 import the xml file as reference data via "Tools | Import Reference Data".

SSAS deployment and Role-based security

 

When developing a SQL Server Analysis Services project, one normally deploys the project to a dev or test server before deploying to an acceptance or production environment to test the solution. After testing is completed, and once the solution has been deployed to the acceptance/production server, an administrator may change the role-based security.

When deploying an update of the solution, you have to decide if you want to retain those changes or if you want to overwrite them.

SSAS includes a tool called the Deployment Wizard which will allow you to do exactly what you want. When using the Wizard, one has to determine how existing security roles, permissions and role members are treated during deployment.

Default the Wizard suggests to overwrite the changes made by the administrator.

However if you select “Retain roles and retain members.”, you don’t overwrite the changes made by the administrator.

SSIS Data Load and geography data-type

When loading and saving data to a SQL Server table containing a geography field, execution results in an error (0xC0209029; DTS_E_PROCESSINPUTFAILED). It turns out that there's no built-in support in SSIS for spatial data.
After some googling I ended up with this post suggesting a workaround for the problem. In short the workaround is to first remove the spatial data column, then import your data, and finally recreate your spatial data column.
In SSIS this can be done as following:
1. In the control flow pane, add a 'Execute SQL Task' before and after the 'Data Flow Task'

2. In the first task ('Remove spatial column') add a SQL statement to remove your column:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourTableName' AND TABLE_SCHEMA = 'yourSchemaName' AND COLUMN_NAME = 'yourSpatialColumnName') BEGIN ALTER TABLE yourSchemaName. yourTableName DROP COLUMN yourSpatialColumnName END

3. In the Data Load task, do what you need to do to load and save your data (without the spatial data column !)
4. In the last task add a SQL statement to add your spatial column again.

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourTableName' AND TABLE_SCHEMA = 'yourSchemaName' AND COLUMN_NAME = 'yourSpatialColumnName') BEGIN ALTER TABLE yourSchemaName. yourTableName ADD yourSpatialColumnName geography NULL END