none
Can we execute the MDX query through SSIS Script task

    Question

  • Hi,

    Can we execute the MDX query through SSIS script task? Is it possible to dump the output of MDX query to some temp table or text file through Script task?

    Has anybody tried this before if yes could you please provide me the sample code for the same.

    I have struck in some urgent deployment need to complete this thing ASAP.

    Your help is really appreciated.

    Thanks,

    Vaibhav

    Thursday, August 04, 2011 5:19 AM

Answers

  • Hi,

    Yes, You can retrieve cube data by using MDX through SSIS Script task but for that you will need to reference Microsoft.AnalysisServices.AdomdClient.dll in your project and then you can use different classes and methods of Adomdclient dll. 

    You can download dll from Microsoft download center.download "Microsoft ADOMD.NET" using following link.

    http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=8824

    After downloading, run the set-up file and you will get Microsoft.AnalysisServices.AdomdClient.dll under folder "C:\Program Files\Microsoft.NET\ADOMD.NET\100"

    Follow following steps for getting MDX data into a datatable..

    Take "Script task" in your SSIS package -> Right click on control and select Edit option -> under "Script Task Editor", click on "Edit Script" button, so if you select ScriptLanguage option as MS Visual Basic 2008 then it will open VB.net class -> press "Ctrl+ R", which will open "Project Explorer" window -> Click on "Show All Files" icon under "Project Explorer" window -> Right click on "References" node and select "Add Reference" -> Go to browse tab and under "File Name" combobox insert following path "C:\Program Files\Microsoft.NET\ADOMD.NET\100\Microsoft.AnalysisServices.AdomdClient.dll" and press Ok button, so that adds the reference of Microsoft.AnalysisServices.AdomdClient.dll in your project.

    Then under VB.Net class import Microsoft.AnalysisServices.AdomdClient.dll i.e. write code "Imports Microsoft.AnalysisServices.AdomdClient" on the top of class, there you will find other Import options. and under methode i.e "Public Sub Main" write code like below one.

    Modify following code as per your requirement like change datasource,catalog and MDX.

     

    Dim objCellSet As CellSet
      Dim objConnection As New AdomdConnection("Data Source=localhost;Initial Catalog=Adventure Works DW 2008R2")
      Dim objCommand As New AdomdCommand()
      Dim objDatatable As New DataTable
      Dim strCommand As String
    
      strCommand = "SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,"
      strCommand = strCommand & " [Product].[Category].[Category] ON ROWS "
      strCommand = strCommand & " FROM [Adventure Works]"
    
      objConnection.Open()
      objCommand.Connection = objConnection
      objCommand.CommandText = strCommand
      objCellSet = objCommand.ExecuteCellSet()
      Dim objDataAdapter As New AdomdDataAdapter(objCommand)
      objDataAdapter.Fill(objDatatable)
    
      objConnection.Close()
    

    http://msdn.microsoft.com/en-us/library/ms123479.aspx

    http://msdn.microsoft.com/en-us/library/microsoft.analysisservices.adomdclient.aspx

     


    Aniruddha http://aniruddhathengadi.blogspot.com/
    • Marked as answer by Abhiyanta Saturday, August 06, 2011 1:07 PM
    Thursday, August 04, 2011 8:48 AM

All replies

  • Hi,

    Yes, You can retrieve cube data by using MDX through SSIS Script task but for that you will need to reference Microsoft.AnalysisServices.AdomdClient.dll in your project and then you can use different classes and methods of Adomdclient dll. 

    You can download dll from Microsoft download center.download "Microsoft ADOMD.NET" using following link.

    http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=8824

    After downloading, run the set-up file and you will get Microsoft.AnalysisServices.AdomdClient.dll under folder "C:\Program Files\Microsoft.NET\ADOMD.NET\100"

    Follow following steps for getting MDX data into a datatable..

    Take "Script task" in your SSIS package -> Right click on control and select Edit option -> under "Script Task Editor", click on "Edit Script" button, so if you select ScriptLanguage option as MS Visual Basic 2008 then it will open VB.net class -> press "Ctrl+ R", which will open "Project Explorer" window -> Click on "Show All Files" icon under "Project Explorer" window -> Right click on "References" node and select "Add Reference" -> Go to browse tab and under "File Name" combobox insert following path "C:\Program Files\Microsoft.NET\ADOMD.NET\100\Microsoft.AnalysisServices.AdomdClient.dll" and press Ok button, so that adds the reference of Microsoft.AnalysisServices.AdomdClient.dll in your project.

    Then under VB.Net class import Microsoft.AnalysisServices.AdomdClient.dll i.e. write code "Imports Microsoft.AnalysisServices.AdomdClient" on the top of class, there you will find other Import options. and under methode i.e "Public Sub Main" write code like below one.

    Modify following code as per your requirement like change datasource,catalog and MDX.

     

    Dim objCellSet As CellSet
      Dim objConnection As New AdomdConnection("Data Source=localhost;Initial Catalog=Adventure Works DW 2008R2")
      Dim objCommand As New AdomdCommand()
      Dim objDatatable As New DataTable
      Dim strCommand As String
    
      strCommand = "SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,"
      strCommand = strCommand & " [Product].[Category].[Category] ON ROWS "
      strCommand = strCommand & " FROM [Adventure Works]"
    
      objConnection.Open()
      objCommand.Connection = objConnection
      objCommand.CommandText = strCommand
      objCellSet = objCommand.ExecuteCellSet()
      Dim objDataAdapter As New AdomdDataAdapter(objCommand)
      objDataAdapter.Fill(objDatatable)
    
      objConnection.Close()
    

    http://msdn.microsoft.com/en-us/library/ms123479.aspx

    http://msdn.microsoft.com/en-us/library/microsoft.analysisservices.adomdclient.aspx

     


    Aniruddha http://aniruddhathengadi.blogspot.com/
    • Marked as answer by Abhiyanta Saturday, August 06, 2011 1:07 PM
    Thursday, August 04, 2011 8:48 AM
  • Thanks Aniruddha for your help.

    Now I want to to transfer the data from dataset to some temp table or text file.Is there any way to achieve the same. It would be great if you could provide me the sample code.

    Thanks for your help again.

    Regards,

    Vaibhav

    Thursday, August 04, 2011 12:06 PM
  • Hi Aniruddha,

    Thanks  for your help.

    Meanwhile I am facing another issue as well. If you have any solution to overcome the issue mentioned in below issue. I need solution on this issue urgently.

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/1c22c1aa-7789-4111-bfdc-7ae8baf98696

     

    Thanks for your help as always !

    Regards,

    Vaibhav

    Friday, August 05, 2011 9:03 AM