3

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.

Jonathan Garvey
  • 115
  • 2
  • 9

1 Answers1

0

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.
userfl89
  • 4,610
  • 1
  • 9
  • 17
  • Thanks for the suggestion. I created and configured the components as described. It's worth nothing though that the QueryDefinition column contains records with more than 4000 characters. In fact it contains records with over 8000. I think the max length is somewhere around 8500. For that reason I cannot use DT_WSTR. However I applied all the steps and used DT_NTEXT instead of DT_WSTR and it hasn't worked. "Failed to retrieve long data for column "QueryDefinition". I've set the temp storage and BLOB paths as per other forum suggestions but that hasn't helped. – Jonathan Garvey Jan 08 '19 at 16:32
  • Worth mentioning also that it's not reverting back to DT_WSTR 255 now so at least we know we can force a data type on the column. – Jonathan Garvey Jan 08 '19 at 16:36
  • Nope the truncation error is gone. By the way I would still have gotten a truncation error even it was using DT_WSTR (4000). This is why I've had to move away from DT_WSTR and use DT_NTEXT. As it stands now it's configured to use DT_NTEXT, good. It's not reverting back to DT_WSTR (255 or otherwise), good. But it's failing at runtime. – Jonathan Garvey Jan 08 '19 at 16:44
  • What's error at run-time? Regarding the 4000 characters, that was just an example I used as a test, with the primary intention being the changing of the data type without this switching back. – userfl89 Jan 08 '19 at 16:45
  • And it was a good suggestion and very helpful because I needed to get away from DT_WSTR 255 in any case, I'll post the error. – Jonathan Garvey Jan 08 '19 at 16:52
  • An OLE DB record is available. Source: "Microsoft OLE DB Provider for Analysis Services." Hresult: 0x00000001 Description: "Error Code = 0x80040E21, External Code = 0x00000000:.". Error: 0xC0208265 at DFT Import Metadata, OLE DB Source [619]: Failed to retrieve long data for column "QueryDefinition". Error: 0xC020901C at DFT Import Metadata, OLE DB Source [619]: There was an error with OLE DB Source.Outputs[OLE DB Source Output].Columns[QueryDefinition] on OLE DB Source.Outputs[OLE DB Source Output]. The column status returned was: "DBSTATUS_UNAVAILABLE". – Jonathan Garvey Jan 08 '19 at 16:53
  • Since this is defined as DT_NTEXT now, have you updated the BufferTempStoragePath and SSISBlobTempStoragePath properties to use drives that have sufficient space for this data? – userfl89 Jan 08 '19 at 17:09
  • That's right. There are only 7 records in this dataset (for now). Datasize virtually 0. – Jonathan Garvey Jan 08 '19 at 17:14
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/186372/discussion-between-userfl89-and-jonathan-garvey). – userfl89 Jan 08 '19 at 17:17