What I wanted to do was to get the output from my MDX query, and then use this in SSIS and take the output and put it into an SSIS Variable which could then be used later in your SSIS Package.

 

Example:

·         We are going to get the Internet Sales Amounts for 2 days.

o    01 June 2008 – $44,650.70

o    02 June 2008 – $35,496.03

·         Then we are going to put the two values into a variable.

·         Then we are going to use a Precedence constraint to compare the values, and if the Internet Sales amount is lower when compared then continue onto the next step.

o    So if the value on 02 June 2008 is lower than the value on 01 June 2008 continue to the next step.

o    So as from the above values it should continue onto the next step.

o    If the amount is higher then do not do anything.

·         NOTE: The reason that we are using an MDX query is so that we can get our data back as quickly as possible.

 

You can get a copy of this here: http://msftdbprodsamples.codeplex.com/releases/view/55330

·         I used the AdventureWorksDW2012 Data File and AdventureWorks Multidimensional Models SQL Server 2012

 

1.       The first thing that we are going to do below is get our MDX query which connects to the AdventureWorksDW2012 SSAS Cube.

a.        This query is for Internet Sales for 01 June 2008

Selectnonempty {[Measures].[Internet Sales Amount]} on 0

,nonempty {(

                     [Date].[Date].&[20080601]

                     )} on 1

from [Adventure Works]

                                                               i.       

b.       This query is for Internet Sales for 02 June 2008

Selectnonempty {[Measures].[Internet Sales Amount]} on 0

,nonempty {(

                     [Date].[Date].&[20080602]

                     )} on 1

from [Adventure Works]

                                                               i.       

2.       Now create your new SSIS Package.

3.       Next we will create our 2 variables that will be used to populate using the MDX queries above.

4.       Below is how we have created them:

a.        clip_image002[1]

b.       NOTE: We have created them as Int64 due to us getting back a numeric value.

                                                               i.      And this can also be compared in our Precedence Constraint

5.       Next we need to create an OLE DB Connection to our SSAS Cube by doing the following below:

a.        Right click in your Connection Managers section and select New OLE DB Connection

b.       When the window opens click on New

                                                               i.      Now from the drop down select the following:

1.       clip_image004[1]

2.       NOTE: You have to select the above so that we can connect to our SSAS Instance.

                                                              ii.      Now once configured you will see the following below:

1.       clip_image006[1]

                                                            iii.      If you want you can click on Test Connection to ensure that it can connect.

                                                            iv.      Then click Ok and then Ok again.

c.        You should now see the following in your Connection Managers Window:

                                                               i.      clip_image008[1]

                                                              ii.      NOTE: If required you can make this a Project Connection if your SSIS Project is in Project Deployment mode.

6.       Now what we are going to do is to assign our MDX Query to our variables using the Execute SQL Task.

7.       Drag in an Execute SQL Task and complete the following steps for One Day back

a.        As per our example I renamed the Execute SQL Task to the following:

Get Internet Sales Amount for One Day Back into Variable

b.       I went into the Execute SQL Task Properties and configured it with the following:

                                                               i.      clip_image010[1]

c.        We then selected our OLE DB connection that we created in step 5 above:

                                                               i.      clip_image012[1]

d.       Next under SQLStatement we are then using our MDX query from Step 1a above:

                                                               i.      clip_image014[1]

                                                              ii.      Then click Ok.

e.       Next click on the Result Set and click on Add

                                                               i.      Now where it says Result Name change this to the following:

[Measures].[Internet Sales Amount]

                                                              ii.      And then ensure that the Variable Name is:

User::InternetSales_OneDayBack

                                                            iii.      clip_image016[1]

f.         Then click Ok.

g.        Now right click and select Execute Task to test that the variable will be populated.

h.       clip_image018[1]

8.       Drag in an Execute SQL Task and complete the following steps for Two Days back

a.        As per our example I renamed the Execute SQL Task to the following:

Get Internet Sales Amount for Two Days Back into Variable

b.       I went into the Execute SQL Task Properties and configured it with the following:

                                                               i.      clip_image010[2]

c.        We then selected our OLE DB connection that we created in step 5 above:

                                                               i.      clip_image019[1]

d.       Next under SQLStatement we are then using our MDX query from Step 1b above:

                                                               i.      clip_image021[1]

                                                              ii.      Then click Ok.

e.       Next click on the Result Set and click on Add

                                                               i.      Now where it says Result Name change this to the following:

[Measures].[Internet Sales Amount]

                                                              ii.      And then ensure that the Variable Name is:

User::InternetSales_TwoDaysBack

                                                            iii.      clip_image023[1]

f.         Then click Ok.

g.        Now right click and select Execute Task to test that the variable will be populated.

h.       clip_image025[1]

9.       Now the final step is to have a next step to go to if the value is lower.

10.    For our example we are going to put the next step as a Send Mail Task

a.        So we dragged in the Send Mail Task and configured it so that it can send an email if run.

b.       Next we dragged the success precedence constraints as shown below:

c.        clip_image027[1]

d.       NOTE: The reason it was done in this order is so that we can get both variables populated before we change the constraint options.

e.       Now double click on the success precedence constraint that goes from Get Internet Sales Amount for Two Days Back into Variable to the Send Mail Task

                                                               i.      clip_image029[1]

f.         Now in the Precedence Constraint Editor Window complete the following:

                                                               i.      Change the Evaluation operation from Constraint to Expression

                                                              ii.      clip_image031[1]

g.        Then where it says Expression click on the Ellipses button.

h.       Now in order to check if the value is less to continue we put the following:

@[User::InternetSales_OneDayBack] <  @[User::InternetSales_TwoDaysBack]

                                                               i.      Click on Evaluate Expression to ensure that it is valid.

                                                              ii.      clip_image033[1]

i.         Then Click Ok and Ok again to go back to the Control Flow

11.    Now finally run your SSIS Package and it should complete with the following as shown below:

12.    clip_image035[1]

13.    NOTE: If you had to change your Precedence Constraint to the following below and re-run the above in order to test if the value was higher you would get the following result:

a.        clip_image037[1]