May 1, 2015 at 1:13 pm
Hi,
I want to refresh my cube. I am using SSIS package, and used SSAS Analysis Task.
For Fact table, there is a mode "PROCESS ADD" which actually add data to Fact table.
I wanted to do the same to our Bigger Dimensions also. But I don't see the m ode "PROCESS ADD". I have only "PROCESS UPDATE".
I read on internet that I can use "PROCESS ADD" using the XMLA code in SSIS package.
I need some direction on it and how to do that and how to handle the error if we want to use XMLA code.
Is there any way that I can do a incremental load of my bigger dimensions daily.
Thank You
May 1, 2015 at 2:59 pm
1.In SSIS 2012 we have option. Below link as good info.
2.For XMLA processing and SSIS 2008 . follow below steps
2.1Build XMLA script using script Task Editor - Substitue Dimension Name Process Type
some code snippet as below
Dim xmla As String
xmla = "<Batch xmlns='http://schemas.microsoft.com/analysisservices/2003/engine'>"
xmla = String.Concat(xmla, "<ErrorConfiguration xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:ddl2='http://schemas.microsoft.com/analysisservices/2003/engine/2' xmlns:ddl2_2='http://schemas.microsoft.com/analysisservices/2003/engine/2/2'>")
xmla = String.Concat(xmla, "<KeyNotFound>IgnoreError</KeyNotFound>")
xmla = String.Concat(xmla, "</ErrorConfiguration>")
xmla = String.Concat(xmla, "<Process xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:ddl2='http://schemas.microsoft.com/analysisservices/2003/engine/2' xmlns:ddl2_2='http://schemas.microsoft.com/analysisservices/2003/engine/2/2'>")
xmla = String.Concat(xmla, "<DimensionID>###Substitue Dimension Name####</DimensionID>")
xmla = String.Concat(xmla, "<Type>## Substitue Process Type name like Process Add/Update##</Type>")
xmla = String.Concat(xmla, "<WriteBackTableCreation>UseExisting</WriteBackTableCreation>")
xmla = String.Concat(xmla, "</Process>")
xmla = String.Concat(xmla, "</Batch>")
2.2 Use Analysis Services Execute DDL Task
In DDL Tab
source type variable
source - Script task xmla varable
connection - server connection string
May 4, 2015 at 8:10 am
k_kasa78 (5/1/2015)
1.In SSIS 2012 we have option. Below link as good info.2.For XMLA processing and SSIS 2008 . follow below steps
2.1Build XMLA script using script Task Editor - Substitue Dimension Name Process Type
some code snippet as below
Dim xmla As String
xmla = "<Batch xmlns='http://schemas.microsoft.com/analysisservices/2003/engine'>"
xmla = String.Concat(xmla, "<ErrorConfiguration xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:ddl2='http://schemas.microsoft.com/analysisservices/2003/engine/2' xmlns:ddl2_2='http://schemas.microsoft.com/analysisservices/2003/engine/2/2'>")
xmla = String.Concat(xmla, "<KeyNotFound>IgnoreError</KeyNotFound>")
xmla = String.Concat(xmla, "</ErrorConfiguration>")
xmla = String.Concat(xmla, "<Process xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:ddl2='http://schemas.microsoft.com/analysisservices/2003/engine/2' xmlns:ddl2_2='http://schemas.microsoft.com/analysisservices/2003/engine/2/2'>")
xmla = String.Concat(xmla, "<DimensionID>###Substitue Dimension Name####</DimensionID>")
xmla = String.Concat(xmla, "<Type>## Substitue Process Type name like Process Add/Update##</Type>")
xmla = String.Concat(xmla, "<WriteBackTableCreation>UseExisting</WriteBackTableCreation>")
xmla = String.Concat(xmla, "</Process>")
xmla = String.Concat(xmla, "</Batch>")
2.2 Use Analysis Services Execute DDL Task
In DDL Tab
source type variable
source - Script task xmla varable
connection - server connection string
Thank you and let me try these options. I think first option is appropriate for me.
Thank You
May 4, 2015 at 2:23 pm
k_kasa78 (5/1/2015)
1.In SSIS 2012 we have option. Below link as good info.2.For XMLA processing and SSIS 2008 . follow below steps
2.1Build XMLA script using script Task Editor - Substitue Dimension Name Process Type
some code snippet as below
Dim xmla As String
xmla = "<Batch xmlns='http://schemas.microsoft.com/analysisservices/2003/engine'>"
xmla = String.Concat(xmla, "<ErrorConfiguration xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:ddl2='http://schemas.microsoft.com/analysisservices/2003/engine/2' xmlns:ddl2_2='http://schemas.microsoft.com/analysisservices/2003/engine/2/2'>")
xmla = String.Concat(xmla, "<KeyNotFound>IgnoreError</KeyNotFound>")
xmla = String.Concat(xmla, "</ErrorConfiguration>")
xmla = String.Concat(xmla, "<Process xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:ddl2='http://schemas.microsoft.com/analysisservices/2003/engine/2' xmlns:ddl2_2='http://schemas.microsoft.com/analysisservices/2003/engine/2/2'>")
xmla = String.Concat(xmla, "<DimensionID>###Substitue Dimension Name####</DimensionID>")
xmla = String.Concat(xmla, "<Type>## Substitue Process Type name like Process Add/Update##</Type>")
xmla = String.Concat(xmla, "<WriteBackTableCreation>UseExisting</WriteBackTableCreation>")
xmla = String.Concat(xmla, "</Process>")
xmla = String.Concat(xmla, "</Batch>")
2.2 Use Analysis Services Execute DDL Task
In DDL Tab
source type variable
source - Script task xmla varable
connection - server connection string
The first option looks easy but I am ending up with errors which are not giving any direction.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Dimension Processing" (32) failed with error code 0x80004005 while processing input "AnalysisServicesServerInput" (45). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
[Dimension Processing [32]] Warning: Parser: Out of line object 'DataSource', referring to ID(s) '', has been specified but has not been used.
I am not why is this error coming first of all.
I do see some warnings like "Duplicate Key found."
Thank You
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply