How to deal with max string length of 255 in SSAS DMV metadata

Question

I'm trying to import SSAS DMV metadata into SQL Server using a data flow in SSIS. In this example, I'm attempting to import data from the $SYSTEM.TMSCHEMA_PARTITIONS DMV. My select statement (which looks like SQL but is not SQL), is:

SELECT
[ID]
,[TableID]
,[Name]
,[Description]
,[DataSourceID]
,[QueryDefinition]
,[State]
,[Type]
,[PartitionStorageID]
,[Mode]
,[DataView]
,[ModifiedTime]
,[RefreshedTime]
,[SystemFlags]
,[ErrorMessage]
FROM $SYSTEM.TMSCHEMA_PARTITIONS

The QueryDefinition column contains records with several thousand characters. However the ExternalColumns section of the SSIS data source component is recognising the columns as DT_WSTR with 255 length. It's not possible to change the external column data type as it will keep reverting back. Nor is it possible to use CAST or CONVERT in the DMV select statement as per MS documentation:

The query engine for DMVs is the Data Mining parser. The DMV query syntax is based on the SELECT (DMX) statement. Although DMV query syntax is based on a SQL SELECT statement, it does not support the full syntax of a SELECT statement. Notably, JOIN, GROUP BY, LIKE, CAST, and CONVERT are not supported.

Naturally, I'm getting a truncation error. My question is, is there any way to change the way in which SSIS interprets the SSAS DMV metadata data types?

I've tried using order by (as a test) to return records with string length > 255 first, in order to force SSIS into thinking straight. It was to no avail.

Answer 1

Try adding the query with all the columns except the QueryDefinition column, then define this column afterwards via the following steps. Going through this, I was able to set data type to DT_WSTR with a length of 4000 for both the External and Output Columns on the OLE DB Source component.

• Define Connection Manager as an OLE DB connection with the Native OLE DB\Microsoft OLE DB Provider for Analysis Services 13.0 provider type.
• In a Data Flow Task, add an OLE DB Source component with all of the columns but the QueryDefinition column and make sure the data types for the other columns are set appropriately.
• After this, set ValidateExternalMetadata to False on the OLE DB Source.
• Open the Advanced Editor of the OLE DB Source (right-click, then Show Advanced Editor...). Add the QueryDefinition column in the SqlCommand field on the Component Properties page.
• On the Input and Output Properties page, go the the External Columns folder under OLE DB Source Output, press the Add Column Button, and add the QueryDefinition column with the proper data type and longer length. Make a note of the value in the ID field for this column.
• On the Output Columns folder, add the QueryDefinition column again with the same data type and length. In the ExternalMetadataColumnID field, enter the same value that this column has in the ID field in the External Columns folder to map these together.