SSIS File Custom Attributes

Today I was given a requirement to extract custom attributes of files and then update the database with these value.


Searching for a DLL's lead me to this URL:-Interop.DsoFile.Dll from Microsoft. Once you download and extract the files go to the Demo folder and use the Interop.dsofile.dll. The dsofile.dll in the main directory is a COM DLL and not .NET dll. 


Using Interop.Dsofile.Dll
To use it in SSIS-Script task, copy the file to "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\binn".
Add a script task and add this dll as reference from "Add Reference". Access the custom attributes using the code below

DSOFile.OleDocumentPropertiesClass _file; _file = new DSOFile.OleDocumentPropertiesClass();              _file.Open(@"C:\TestFile.doc", false, DSOFile.dsoFileOpenOptions.dsoOptionDefault); MessageBox.Show(_file.CustomProperties.Count.ToString()); MessageBox.Show(_file.CustomProperties[0].Name.ToString() + ":-" + _file.CustomProperties[0].get_Value().ToString());

Average of Dimension attribute value

Query to find out the average of a value that is an attribute in the dimension.

WITH MEMBER [Measures].[Average Cars Owned] As       Avg(             DESCENDANTS(                   [Customer].[Customer Geography].CURRENTMEMBER,                   [Customer].[Customer Geography].[Customer]             )AS Set1,             StrToValue(Set1.Current.Properties("Number of Cars Owned"))       )       SELECT     {             [Measures].[Average Cars Owned]     } ON COLUMNS,     DESCENDANTS(     [Customer].[Customer Geography].CURRENTMEMBER,     [Customer].[Customer Geography].[State-Province],SELF_AND_BEFORE)      ON ROWS FROM    [Adventure Works] where [Customer].[Country].&[Australia]