Excel–Power Query–Automating SSIS job duration report

Following on from our query to get our SSIS Job duration information, I decided to start using Power Query and see if I could use it to facilitate the automation of my reporting requirements.

Detailed below is how and what I archived.

Example

·         We are going to use our SSISDB to see if there have been any changes in how long it takes our entire job to complete.

·         NOTE: We always use a Parent package which runs all our child packages. So we are going to run the query using our Parent Package.

·         As with our Example we are going to view our Job which runs the SCOM Hourly collection

o   The parent package is called: SCOM Parent Package.dtsx

·         NOTE 2:The SSISDB is part of SQL Server 2012, so this query will only apply for you are using SQL Server 2012

Download and Install Power Query

You can download and install currently the latest version of Power Query from here:

http://www.microsoft.com/en-za/download/details.aspx?id=39379

Creating your Power Query to get information from SQL Server

Here we are first going to create our query and ensure that we can get down the required data.

1.       Create a new Excel document

2.       Ensure that you can see your Power Query in the ribbon

a.      

3.       Now as per our example we are going to get our data from SQL Server so complete the following steps below.

a.       Click on From Database and then select From SQL Server Database

b.     

c.       Now in the Microsoft SQL Database put in the connection to your Server and Database

                                                               i.      As seen below with our example:

                                                             ii.     

                                                            iii.      NOTE: Change the Server to you SQL Server Instance where you have got the SSISDB installed and running.

d.      Then click on the arrow next to SQL statement

                                                               i.      NOTE: This is because we want to put in a specific SQL Query to get back out SSIS job information

e.  Then for our example and to view the duration of the packages in our SSIS Project we put in the following TSQL statement

      Select

        Distinct (EX.executable_id)

       ,executable_name

       ,(EXS.execution_duration/1000)asDurationInSeconds

       ,CONVERT(TIME,dateadd(ms,EXS.execution_duration,’01-01-1900′))asDurationInTime

       ,convert(Date,EXS.start_time)asExecutionDate

       ,SUBSTRING(convert(varchar(23),EXS.start_time),12,2)asStartHour

                           ,Ex.package_name

 

  FROM[internal].[executables]asEXwith (nolock)

       Innerjoininternal.executable_statisticsasEXSwith (nolock)

              onEX.executable_id=EXS.executable_id

  whereEx.package_name=‘SCOM Parent Package.dtsx’

f.        Then click Ok.

g.       You will then get prompted if you want to Encrypt the connection

                                                               i.      Due to us running this within our domain we removed the tick

                                                             ii.     

h.      Once run you should then see the following as shown below:

i.        

j.        What I also did was to change the Name of the query.

                                                               i.      If you look on the right hand side under the Query Settings, where it says Name, as with our example we changed it to the following:

1.      

k.       Then click on Apply and Close

                                                               i.     

4.       You will now see the following table created:

a.      

b.      NOTE: You will see that your table name has the same name as your Power Query name.

5.       Now save your Excel Workbook so that you have all your settings changed.

Creating the Invoke function in order to pass details to your query

In this section is where we will convert our query to use the Invoke Function so that we can then pass details to our query

1.       Right click on your Workbook Queries and select Edit

a.      

2.       Then in the Query Editor Ribbon click on View and then click on Advanced Editor

a.      

3.       This will then open the Advanced Editor.

4.       Now what we are going to do is to add some text which will allow us to create the Invoke Function.

a.       NOTE: I am very new to b so there might be a much more elegant or better way of doing this. So if you have any suggestions please let me know.

b.      Originally it looks like the following:

                                                               i.     

                                                             ii.      NOTE: The query does carry on, on the right hand side.

c.       We then put in the following:

                                                               i.      At the start we put in the following which will create our Invoke Function

let SSISJobDetails = (SSISJobDetails as text)=>

1.       NOTE: We have defined a name for when we call our Invoke Function as said that we are accepting Text

                                                             ii.      We then put this at the bottom to close off our Invoke Function

in

   SSISJobDetails

                                                            iii.      It now looks like the following:

                                                           iv.     

d.      Then click Done.

e.      NOTE: We are not quite finished yet, but we want to ensure that the Invoke Function will work first. Baby steps here J

5.       Once done it should then come back and look like the following as shown below.

a.      

b.      NOTE: This is what we want to see.

6.       Now let’s test to ensure that everything is correct.

a.       Click on Invoke and you should see the following:

b.     

c.       Put in anything into the text box, and then click Ok.

                                                               i.      NOTE: Currently we have not put in our expected variable into our Invoke Function which we will do in the next steps.

d.      Now you should see the output of the query:

e.      On the right hand side under Query settings in the Applied Steps you will see the following:

                                                               i.     

f.        Click on the delete button next to InvokedSSISJobDetails

                                                               i.      NOTE: The reason that we are deleting this is because we do not want this dataset as it currently is.

g.       Once done it will go back to the same screen as in step 5 above.

7.       Now we are going to change our actual query so that what we pass from the Invoke Function will then be passed to the query

a.       Then in the Query Editor Ribbon click on View and then click on Advanced Editor

                                                               i.     

b.      Now because we want to change our Package_Name, this is right at the end of our query so we need to scroll right to the end.

c.       Now change it from what it currently is:

where Ex.package_name = ‘SCOM Parent Package.dtsx'”])

d.      To the following:

where Ex.package_name = ‘”&SSISJobDetails&”‘”])

e.      NOTE: We are using the nabme under our Applied Steps.

f.        Then click Done.

8.       Now to test click on the Invoke button

a.       Now what we will first test is out our existing SSIS Package name so we put in the following:

                                                               i.     

b.      Then click Ok.

c.       You will then get the following as shown below:

                                                               i.     

                                                             ii.      Click on Edit Permission

d.      This will then bring up the Native Database Query

                                                               i.     

                                                             ii.      Click on Run

e.      Once completed you will now see your output as we had in step 3h above

f.       

9.       Once again on the right hand side under Query settings in the Applied Steps you will see the following:

                                                               i.     

b.      Click on the delete button next to InvokedSSISJobDetails

                                                               i.      NOTE: The reason that we are deleting this is because we do not want this dataset as it currently is.

10.   Now click on the Invoke button but this time put in either another SSIS package that you have, or in my example I put in the letter “A”

a.       NOTE: I did this to ensure that it indeed passing the data from the Invoke Function into our query

b.     

c.       I then clicked Ok and got the following:

                                                               i.      NOTE: You might get the Permissions required to run, if you do just b

d.     

e.      This is what we expected to see, because it did indeed pass the value through to our query

11.   Now close down the Query Editor and it will prompt you if you want to keep your changes.

a.       Click on Keep

b.     

12.   Once again on the right hand side under Query settings in the Applied Steps you will see the following:

                                                               i.     

b.      Click on the delete button next to InvokedSSISJobDetails

                                                               i.      NOTE: The reason that we are deleting this is because we do not want this dataset as it currently is.

13.   Now when you are back in your Excel Workbook you will see that your sheet looks like the following:

a.      

b.      This is to be expected due to creating our Invoke Function. And due to their being no data from this, we have no data to display.

14.   When back in your Excel Workbook click on Save to save all your changes

Creating your Parameter to be passed to your Power Query

In the next steps we are going to be passing our parameter into our Power Query so that the user can simply put in the package name that they want to view.

1.       Create a new sheet in your workbook.

2.       With our example and in order just to know which sheets represented our Parameter we renamed our sheet to: Input Parameter

a.      

3.       Next we created the following in our sheet and put it into a table

a.      

b.      So once completed it looked like the following:

                                                               i.     

c.       NOTE: The reason that we did not leave any spaces in our Column name is so that there is less formatting in our Power Query. I read this a few times when learning how to complete all the steps.

4.       Another important thing to note is that your parameter that is part of your query MUST have the same column name within your query

a.       If you do not have it then when trying to add the data to your table in the next steps it will keep on failing

b.      So with our example our Parameter had our PackageName, in our Input Parameter Sheet, as well as the data returned from the query

5.       Next click on Power Query and then click on From Table

a.      

b.      This will then open your Query Editor

c.       And you will see the following:

d.     

6.       What we did was to rename our query as per our example to the following:

a.      

7.       Now in your query right click in SSISPackageName and select Insert Custom Column

a.      

b.      Where it says New Column Name you can change it from Custom to something more descriptive.

                                                               i.      As with our Example we changed it to the following:

                                                             ii.     

c.       Now where it says Custom column formula we put in the following:

SSISJobDetails([SSISPackageName])

                                                               i.     

d.      Click Ok.

e.      You will then get prompted about data privacy

                                                               i.      NOTE: This is to ensure that your data is kept private

                                                             ii.     

f.        ClickContinue

g.       This will then open the Privacy Levels window

                                                               i.      You can then select your Privacy Level

1.      

                                                             ii.      We selected Organizational for all our connections

h.      Then click Save.

8.       Now you will see the following in your Power Query Window

a.      

9.       Click on the arrows button in your SSISQueryDetails column and select Expand as shown below

a.      

b.      Then click Ok.

10.   You should now see all that data from your query as shown below:

a.      

b.      NOTE: You can then change and modify anything as you would with any normal Power Query dataset.

c.       We configured our DurationInSeconds column to Number

d.      And then our ExecutionDate to Date/Time

11.   Then click Apply and Close.

12.   This will then create a new sheet with your data.

a.       NOTE: For our Load settings we also selected to Load Data to Model

                                                               i.      This is so that we can store the data as efficiently as possible. As well as that data being stored in Power Pivot.

b.     

13.   Now you have your data stored in your Excel Workbook.

Creating our Report and Chart

Next we will be creating our report and chart to show the actual information.

1.       First on your sheet where you have your output from your Power Query, we renamed this to something more descriptive.

a.       EG: Output PQ

2.       Next click on any column in your Power Query dataset in your Excel workbook.

a.      

3.       Then in the ribbon on the far right hand side under TABLE TOOLS click on DESIGN

a.      

4.       Now under Tools click on Summarize with Pivot Table

a.      

b.      This will then open the Create Pivot Table and you will see as with our example below that it will have selected our Table

c.      

d.      Then click Ok.

5.       Once again we first renamed our sheet to be called: Report PQ

6.       Then what we did was to create our report with the following shown below from our query.

a.      

7.       We then created a chart so that it would be easier to display and view the data

8.       As with our data we got the following chart

a.      

9.       Now the consumer of the report can go and see why the time has been increasing.

10.   What I then did was to hide some of the sheets so that it is easier for the end user to use. This is shown below.

a.      

11.   The final test will be to change your SSIS Package Name on the Input Parameter sheet.

a.       Then click Refresh All under the DATA Ribbon

                                                               i.     

b.      And then see if your report changes accordingly.