Process Add Dimension using SSIS Data flow task

Muhammad Ali Shah 96 Reputation points
2020-11-12T13:52:51.37+00:00

Hi everyone,

i have a very large dimension table. I want to do a Process Add on this dimension using the 'Process Dimension' task in SSIS Data flow task. However, i am getting a strange error saying 'Interface is not supported'.

I used the OLEDB source to get only the new records and then mapped those to the Dimension Processing task.

I am using SQL Server 2016 and Visual Studio 2017.

Please see attached the screenshot. Can someone please help? Thanks in advance

39431-image.png

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,460 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,247 questions
0 comments
Accepted answer
  1. Muhammad Ali Shah 96 Reputation points
    2020-11-20T10:27:15.473+00:00

    i actually have now developed a worked around.

    I get the MaxKey from Dimension using the MDX and store it to a table. I have created a view based on my dimension table where i get the records > MaxKey and then i execute the ProcessAdd XMLA in SSIS to ProcessAdd the dimension. So, instead of using the actual dimension table, i now use a view to achieve ProcessAdd.

    I was receiving attribute key not found error. i guess this is because my fact table contains all the records (all the keys from dim table), while the view for ProcessAdd only returns the new records. So i set key not found error to ignore.

    Please NOTE that i have slowly changing dimensions so i have to return the complete Fact table each time. My Fact table is actually also a view.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lukas Yu -MSFT 5,816 Reputation points
    2020-11-13T06:49:40.883+00:00

    Hi,

    This should not be issue with SSAS, I think this is the project or data extension version problem.

    You could try check and change the SSIS project's TargetServerVersion to the correct SQL Server 2016 see if this solves this issue . Reference : SSIS Script Task: No such interface supported

    Regards,
    Lukas

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    What can I do if my transaction log is full?--- Hot issues November
    How to convert Profiler trace into a SQL Server table -- Hot issues November

    0 comments
  2. Muhammad Ali Shah 96 Reputation points
    2020-11-13T11:23:59.083+00:00

    Hi @Lukas Yu -MSFT

    thank you for answering. I already checked the configuration properties and Target Server Version is set to SQL Server 2016. It was working a few months back but doesn't work anymore :(

    The error is still the same. Morever, if i just drag the Dimension Processing task to Data flow task it gives me an error that 'Subscript is out of range'. Any idea what might be the reason for this?

    39675-image.png