Wednesday, March 21, 2012

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());


Tuesday, March 6, 2012

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]

LinkWithin

Related Posts with Thumbnails