Automating running PowerShell script which required Administrator elevation via SSIS

UPDATE (08 AUG 2016): I have updated this to use a SQL Server Agent Job instead of the Task Scheduler which proved to not be reliable.

What we are going to explain below is the following via an example.

Example – We are required to run a PowerShell script which will snapshot a database using Xtreme IO from EMC. In order for this to run it has to be run as Administrator due to it having to drop and re-create the disk drives in the Operating System layer.

So in order to complete this we have to complete the following below but use a simple PowerShell script which will get all the FileNames from a Folder.

  • Create a BAT file which can execute the PowerShell script.
  • Create a Scheduled Task which in turn will call the BAT file.
  • Create an SSIS Package which will call the Scheduled Task

NOTE: The reason that we did this above, was to ensure that we could automate the snapshotting of the database using SSIS. This would enable us to get a snapshot of the data on a required schedule.

Changing the UAC

Below is a quick step to disable the UAC, as I think that this might affect the later steps when the BAT file is trying to run the PowerShell script.

NOTE: You will need to be an Administrator on the Server to turn this off.

  1. Go into the Control Panel.
  2. Then search for UAC.
  3. Then click on the Change User Account Control settings as shown below:
  4. Change it to Never Notify as shown below:
  5. Then click
    Ok
  6. It will ask you one last time if you want to make the change to the Computer.
    1. Click on Yes

Creating the BAT File which can execute the PowerShell script

In the steps below we are going to explain how we create the BAT file which executes the PowerShell script. And also show that at first we can run the PowerShell script manually to ensure that it works.

Testing the PowerShell Script

  1. The first thing to test is to ensure that the PowerShell script actually runs and completes as it should.
  2. Click on Start and then type in PowerShell and from the search results right click on Windows PowerShell ISE and select Run as Administrator
  3. Now when the PowerShell ISE opens you can see from the Window at the top that you are running as Administrator
  4. If you want to follow our example you can then copy and paste the following PowerShell script

    Get-ChildItem
    -Path
    ‘C:\Program Files\Microsoft SQL Server\MSSQL11.PRODBI\MSSQL’
    -Recurse
    |
    Export-Csv
    -Path
    ‘C:\Program Files\Program Files.csv’
    -Encoding
    ascii
    -NoTypeInformation

    1. What we are doing above is to get the File Names for all the files and folders under Program Files and then our SQL Server MSSQL Folder.
    2. And then export it to a CSV.
    3. As with our example once run you should see the CSV file as shown below:
  5. Now we know that our script runs we can now save it to a location on our Server.
  6. As with our example we saved it to a new folder on the C:Drive
  7. We will now use this in the next steps to create the BAT File.

Creating the BAT file which will run the PowerShell script.

In the steps below we will be creating the BAT file which will run the PowerShell script.

  1. Open up Notepad or Notepad++ and copy in the following, which will be explained below:
    1. c:\windows\system32\WindowsPowerShell\v1.0\powershell.exe
      -ExecutionPolicy ByPass -command
      “. ‘C:\XIOSnapshot\Export-MSSQL-Files.ps1′”
    2. What we are doing above is to first put in the full location of PowerShell highlighted in GREEN
    3. Next we are putting in some parameters for PowerShell, which is to bypass the execution policy and the command that is to be run highlighted in PURPLE
    4. And then the final part is where we are calling the PowerShell script which we created above. It is highlighted in RED
      1. NOTE: In order to run the command, we have to put it all in double quotes, as well as it has to have a period in order for it to be executed.
    5. Now we can save this BAT File in the same location with a name.
    6. As per our example we saved it with the following:
  2. Now what we will need to do is to run the BAT file from the command line to test that it all works.
  3. You can now open command prompt and run the bat file as we have shown with our example below:
    1. NOTE: You can see that we are not logged into the Command prompt as Administrator.
    2. We can also see that it completed successfully.
    3. And finally if we look on the file system we can see the file got created.
  4. One final thing to note, is that this will run when we execute the BAT file as we have administrator privileges.

Using SQL Server Agent Job to run BAT Files

Below is the process which will actually enable you to run your BAT files under the correct CMD.EXE Context

  1. In order to get this to work, we had to use a combination of SQL Server Agent steps and SSIS steps, which is explained below
    1. First we then needed to use our BAT File to run the PowerShell script which would snap the data.
      1. We configured this step in the SQL Server Agent Job properties as shown below:
      2. As you can see from above we change the Type to be “Operation system (CmdExec)”
        1. The reason for this is that this runs the CMD.EXE in the correct context.
      3. And then we put in our BAT file which calls the PowerShell script which we created earlier.
    2. Next we then ran the BAT file which modified the folder permissions.
      1. We configured this step in the SQL Server Agent Job properties as shown below:
      2. As you can see from above we change the Type to be “Operation system (CmdExec)”
        1. The reason for this is that this runs the CMD.EXE in the correct context.
  2. You can then schedule your SQL Server Agent job and as I tested it did run successfully.

SSIS – Creating a process which will check a condition, if it is not ready wait and try again.

So we had a requirement where we wanted to create a waiting for loop, so that if it was not ready it would wait, try again, and if still not ready wait and try again until it got to the Max Number of tries. We also did not want it to error whilst waiting.

Example:

  • Check to see if the database is online.
  • Wait for 1 minute to retry.
  • Try 10 times before failing
  1. The first that we need to do, is to create some Variables which will help with the process.
  2. From the above this is what they are for:
    1. Max_Count – This is how many times you want to retry
    2. State – This is our check to see if the databases are online or not.
    3. SuccessfulRun – This is used to see if it ran successfully
    4. Value_Counter – This is used as the counter.
  3. Then below are the steps.
    1. The first thing is to assign a value to our Max_Count Variable.
    2. This is done using an Execute SQL Task and assigned it with the following below:
      1. As you can see above in the SQLStatement we have set our Max_Count to 10
      2. Then in the ResultSet we have mapped our results to our Variable
    3. Next is where we configured our For Loop Container with the details shown below:
      1. As you can see above we have assigned it with our Value_Counter Variable.
      2. You will note that in the EvalExpression we have got our Max_Count variable as well as the SuccessfulRun
        not
        equal to zero. This will be explained later how this value is derived.
        1. NOTE: This is also required so that it will exit when it is actually
          successful.
    4. Next in our process within the For Loop is where we start with our Waiting Period as shown below using the Execute SQL Task
      1. All that we are doing here is using a TSQL
        statement to wait for 1 minute.
      2. NOTE: The reason we do this at the start, is so that if it has to loop through, it will wait on the second
        loop before checking.
    5. Next is where we have our condition (which based on our example is to check if the database is online) as shown below:
      1. What our SQLStatement is doing above is checking to see the State of our database.
        1. And if it is online it will have a state of equal to zero
      2. Then in the ResultSet we have mapped our results to our Variable
    6. Next is where we have our Script Task which we use to populate the SuccessfulRun variable as shown below:
      1. We went into Edit the script with the following below:
      2. As you can see above we also assigned the same variable
        SuccessfulRun to the ReadWriteVariables
      3. NOTE: We did use the Microsoft Visual C# 2012 as the script
        language.
      4. From the above all that we added was to give the Variable SuccessfulRun = 1
    7. And then we set our database
      back into Multi User Mode after it was successfully back online as shown below:
    8. The final thing that we had to do, which actually ensure that it loops through without
      erroring is to put in a precedence
      constraint
      between the Check if Database is Online and Check to see if Successful as shown below:
      1. We then configured it with the following for the precedence
        constraint as shown below:
  4. So once this is all done it looks like the following:
  5. You can now run this, potentially
    changing your condition (Check if Database is Online) that you want to check for. As well as you can also change how long you want to wait for before
    checking
    again.

Automating running PowerShell script which required Administrator elevation via SSIS

What we are going to explain below is the following via an example.

Example – We are required to run a PowerShell script which will run with elevated privileges.

So in order to complete this we have to complete the following below but use a simple PowerShell script which will get all the FileNames from a Folder.

  • Create a BAT file which can execute the PowerShell script.
  • Create a Scheduled Task which in turn will call the BAT file.
  • Create an SSIS Package which will call the Scheduled Task

NOTE: The reason that we did this above, was to ensure that we could automate the snapshotting of the database using SSIS. This would enable us to get a snapshot of the data on a required schedule.

Changing the UAC

Below is a quick step to disable the UAC, as I think that this might affect the later steps when the BAT file is trying to run the PowerShell script.

NOTE: You will need to be an Administrator on the Server to turn this off.

  1. Go into the Control Panel.
  2. Then search for UAC.
  3. Then click on the Change User Account Control settings as shown below:
  4. Change it to Never Notify as shown below:
  5. Then click
    Ok
  6. It will ask you one last time if you want to make the change to the Computer.
    1. Click on Yes

Creating the BAT File which can execute the PowerShell script

In the steps below we are going to explain how we create the BAT file which executes the PowerShell script. And also show that at first we can run the PowerShell script manually to ensure that it works.

Testing the PowerShell Script

  1. The first thing to test is to ensure that the PowerShell script actually runs and completes as it should.
  2. Click on Start and then type in PowerShell and from the search results right click on Windows PowerShell ISE and select Run as Administrator
  3. Now when the PowerShell ISE opens you can see from the Window at the top that you are running as Administrator
  4. If you want to follow our example you can then copy and paste the following PowerShell script

    Get-ChildItem
    -Path
    ‘C:\Program Files\Microsoft SQL Server\MSSQL11.PRODBI\MSSQL’
    -Recurse
    |
    Export-Csv
    -Path
    ‘C:\Program Files\Program Files.csv’
    -Encoding
    ascii
    -NoTypeInformation

    1. What we are doing above is to get the File Names for all the files and folders under Program Files and then our SQL Server MSSQL Folder.
    2. And then export it to a CSV.
    3. As with our example once run you should see the CSV file as shown below:
  5. Now we know that our script runs we can now save it to a location on our Server.
  6. As with our example we saved it to a new folder on the C:Drive
  7. We will now use this in the next steps to create the BAT File.

Creating the BAT file which will run the PowerShell script.

In the steps below we will be creating the BAT file which will run the PowerShell script.

  1. Open up Notepad or Notepad++ and copy in the following, which will be explained below:
    1. c:\windows\system32\WindowsPowerShell\v1.0\powershell.exe
      -ExecutionPolicy ByPass -command
      “. ‘C:\XIOSnapshot\Export-MSSQL-Files.ps1′”
    2. What we are doing above is to first put in the full location of PowerShell highlighted in GREEN
    3. Next we are putting in some parameters for PowerShell, which is to bypass the execution policy and the command that is to be run highlighted in PURPLE
    4. And then the final part is where we are calling the PowerShell script which we created above. It is highlighted in RED
      1. NOTE: In order to run the command, we have to put it all in double quotes, as well as it has to have a period in order for it to be executed.
    5. Now we can save this BAT File in the same location with a name.
    6. As per our example we saved it with the following:
  2. Now what we will need to do is to run the BAT file from the command line to test that it all works.
  3. You can now open command prompt and run the bat file as we have shown with our example below:
    1. NOTE: You can see that we are not logged into the Command prompt as Administrator.
    2. We can also see that it completed successfully.
    3. And finally if we look on the file system we can see the file got created.
  4. One final thing to note, is that this will run when we execute the BAT file as we have administrator privileges.

Creating the Scheduled Task which will call the BAT File

Now we are going to create the Scheduled Task which will call the BAT File.

  1. Go into Server Manager, then click on Tools and select Task Scheduler
  2. Once this opens on the right hand side click on Create Task…
  3. The first thing that you have to give your task is a name
    1. As with our example we are going to give it the name as shown below:
    2. Now a VERY important step
      is under Security options make sure to select the Tick box as shown below:
      1. NOTE: With this it means that it will run with the highest privileges and will run as Administrator context
    3. Next if you are going to run this via SQL Server Agent, you are going to have to put in the same domain account
      context that SQL Server Agent Runs Under.
      1. NOTE: When you click ok for the creation of the Scheduled Task it will prompt for the password.
  4. You can leave Triggers blank, because we are going to execute this via SSIS.
  5. Click on Actions
    1. Then click New
    2. Then configure the New Action as shown below, with an explanation afterwards.
      1. Here we are just running our BAT File.
    3. Click Ok.
  6. You can now click Ok for this Scheduled Task to be created.
  7. NOTE: When you click ok for the creation of the Scheduled Task it will prompt for the password.
  8. The final step is to then test the scheduled task by running it within Task Scheduler.
    1. Click on Run
    2. You will briefly get the command prompt Window popping up, which you will not see when running via SSIS.
    3. To confirm this you can view the Last Run Result in Task Scheduler as shown below:
    4. As well as the file on the file system:
  9. Now we are done with the Task Scheduler, calling the BAT file, which is then calling the PowerShell script.

Automating the running of the Scheduled Task in SSIS

In the steps below we are going to show how to automate the running of the Scheduled Task within SSIS

  1. Either create a new SSIS Project or create a new SSIS Package.
  2. Then drag in the Execute Process Task into the Control Flow.
  3. Double click to go into the Properties.
    1. We gave it the following name:
    2. Then click on Process to put in the details to call the Scheduled Task
    3. Now what you will see below is the details to run the Scheduled Task which will be explained afterwards:
      1. From the above under
        Executable, this is where the Scheduled Task exe is saved on our Windows
        Server 2012.
      2. Then under Arguments is where we specified for the Scheduled Task to firstly run (/run) and then the Task Name (/TN) to run
      3. Then click
        Ok.
    4. You should then see the following in your SSIS Package:
  4. Now to test that it all works, execute your SSIS
    Package.
    1. It should then run successfully and you should see the following:
    2. NOTE: That the SSIS will come back immediately saying that was successful if the command is correct.
    3. So if you have something else that is reliant on this step I would suggest putting in something to delay it going onto the next step.
      1. I did this by using the WAITFOR DELAY in SQL Server.
    4. Now we can see the file being updated as shown below:
  5. The final step is to then deploy this to your SSIS
    Server and create the scheduled task and test it.
  6. NOTE: If you have configured the Scheduled
    Task with the same
    domain
    account as your SQL Server Agent
    account it will succeed.
    1. NOTE: This domain account should not have a password that will change.

BI-NSIGHT – Power BI Desktop (Date Hierarchy) – Power BI(Timeline Slicer Visual, Pin Excel Range to Dashboard, Power BI User Groups, Weekly Service Update, Visual Studio Application Insights Content Pack) – Microsoft BI Reporting Road Map – SQL Server 2016 SSIS Updates

With the Pass conference completed last week there has been a lot of information about SQL Server 2016 and the future does indeed look bright for BI within the Microsoft BI stack. And I personally think that in the future they will be leading in most of the BI areas.

I am not going to go into all the details as this has been covered in a whole host of other blogs that I follow. And I am sure that a lot of the people reading my blog have already found out all the new amazing news.

So here we go with all updates from the past week.

Power BI Desktop – Date Hierarchy

The above screenshot was taken from Jen Underwood, which is showing how in the future version of Power BI Desktop it will have the ability to be able to create the Date Hierarchy for you. I am sure it will be in a not too distance release.

Power BI – Timeline Slicer Visual

As promised as Pass last week here is another great Custom Visual available in Power BI.

I can see this being used a lot, as I have used it in Excel in the past and it does allow users the ability to slice their data by Month to Date, Year to Date, Quarter to Date etc…

You can find all the custom Visualizations here: Power BI Custom Visualizations

Power BI – Pin Excel Range to Dashboard

Another feature that will be coming to the Power BI Service is the ability to Pin an Excel Range into your Power BI dashboard.

I think that this will be really useful, because Excel does some things really well. And often it can show you a lot more information, which can easily be digested instead of trying to replicate it Power BI.

Power BI – User Groups

This is another great incentive, as I think as the momentum grows with Power BI this will be a great way to network with like-minded people.

As well as learn from other people who I have no doubt will have some amazing idea’s and experience to share.

Here are the details if you are interested to Sign up or see if there are people in your area: Power BI User Groups are here!

Power BI – Weekly Service Update

There were some interesting updates this week, which is that you now get a guide in what you want to do in Power BI.

As well as now individuals can also sign up for Power BI. I do think that this is a very clever move. As there are a lot of people who potentially might use it at work, and then want to use it for their personal projects. Along with this you can get the general public to start using this service. And often this can attract a larger crowd than the amount of people that will be exposed to the Power BI service. Which in turn could get Power BI into a company!

And finally is the duplication of an existing report. Which often can help when you want it to be very similar and do not want to have to re-create it all from scratch!

You can find out all the details here: Power BI Weekly Service Update

Power BI – Visual Studio Application Insights Content Pack

This week’s Content Pack is about Visual Studio applications and can give you insight into your applications that you have created and can show you potentially where you have issues.

You can find out how to use the content pack and more details here: Explore your Application Insights data with Power BI

Microsoft BI Reporting Road Map

As has been blogged quite extensively it is the first time since I have started my career in BI, that there has actually been a roadmap for BI from Microsoft.

I have to say it is great that we now have this visibility, because it means we can plan for what is coming. And incorporate some of the new changes into our existing and to be delivered projects. Which means we will be in a position to show the people in our business something that is new and fresh.

And the way that I see it, people like to see things change. Not everyone in the business, but at times even if the charts just change slightly or there is something additional it can mean that there is great adoption. It also shows that it is not something that been developed and never looked at again!

I do feel that they are focusing a lot on SQL Server Analysis Services Tabular. And for good reason, this product is playing catch up. It is also being used in Power BI, which we all really love and are using more often. And I can see that we are also starting to get the best of both worlds. And by that I mean we are getting a lot of the functionality from SQL Server Analysis Services Multidimensional, as well as from TSQL. Which means that we can leverage the best of both.

You can find out all the information about the BI Reporting Roadmap here: Microsoft Business Intelligence – our reporting roadmap

SQL Server 2016 – Integration Services Update

The link below are all the updates from Wolfgang Strasser (w|t) with regards to all the great updates that are coming to SSIS 2016.

I am looking forward to see how the Package Control Flow Templates, as the way I see, this will mean that you can leverage creating the template once, and then reuse it again and again. So for example if you create a Package Control Flow Template for a Slowly Changing Dimension Type 2. You can then use this in your framework for all your other developers.

You can read his blog post here: SQL Server 2016 Integration Services (SSIS) –Summary of SQL Pass Summit 2015 session

BI-NSIGHT – SQL Server 2016 CTP 3.0 (SSAS, SSRS, SSIS) – Power BI (Chiclet Visual, SparkPost Content Pack, Weekly Service Update, Personal Gateway Update, Tiles in SharePoint)

I expected this week to be a really interesting week with SQL Pass happening. As I was sure to see some really good and interesting updates from Microsoft and it sure is living up to this.

There has been a lot of information on Twitter and on other blogs, so here is my take on the developments.

SQL Server 2016 CTP 3.0 (SQL Server Database Engine, SQL Server Analysis Services, SQL Server Reporting Services, SQL Server Integration Services)

There was a whole host up dates with SQL Server 2016 CTP 3.0, which is great to see, as well as some announcements of what we can expect in subsequent releases.

I am just going to highlight below what I think is relevant in the BI space. But there will be links below where you can find the related blog posts, which have more information from the Microsoft teams.

SSAS

With regards to SSAS, it is good to see how much effort and work is going into the Tabular model. Which is what I thought would be the case.

I think that it is really great to see that they have changed the underlying structure from XMLA to JSON. The way that I see it, this is how they have implemented Power BI in terms of having the SSAS database sitting in memory in Azure. And without a doubt I am sure that they have learnt a lot, and from this they can then leverage this and bring it into the On Premise product. We all know how fast it is online!

The MDX Support for Direct Query is also a great update. I can see a lot of people leveraging this, and when you partner this with APS you can pretty much start to enable real-time analytics. Which can be a real game changer.

All the other updates that are coming into SSAS have mostly been completed either in Power BI Desktop or in Excel 2016. So it is great to see this in the Server product which will go a long way to ensure that it can scale and perform for enterprise workloads.

SSRS

I have eagerly been waiting to see what was going to happen in the SSRS space. And whilst I had seen some of the now released information it is great to see it being released to the general public. As well as how well it has been received.

The pinning of SSRS reports into Power BI is a really smart move. And the ability to also refresh this report in Power BI is pure Genius. What this means now is you can leverage both of your On Premise and cloud investments. And to the users this will be seamless.

What I also really like is that you can often create really interesting SSRS reports, and the executives and high level managers do not need to see the details. They just want an overview. And now by leveraging this all into Power BI, it becomes their one stop shop!

SSIS

There does not seem to have been a lot of love for SSIS, and to be honest it is a stable and really good product.

But what I did see is the Control Flow Template, and I am hoping that this is something similar to what you can currently do with BIML. What that is how I perceived it to be. And I am hoping that you can create different control flow templates for different control flows. So for example you could create a control flow template for a SCD Type 2. And then once you have it designed the way that you want, any other developers can then utilize it. This would go a long way in enterprises where you want to standardize the way of doing things.

You can read about all of the above here:

Power BI – Chiclet Visual Slicer

The one thing that I have been struggling with in Power BI was how to get a slicer to work, so that it looked good.

And low and behold there is a new visualization which can how do this. And to have it with images also is really smart. As people love to click on Images.

Another great announcement was from James Phillips that Microsoft would be releasing a new visualization every month, indefinitely. This is really great and I am sure that we will see some really interesting and useful visualizations in the future.

You can read all about it here: Visual Awesomeness Unlocked: The Chiclet Slicer

Power BI – SparkPost Content Pack

This week there is another interesting and great Content Pack. This time for SparkPost. Which you can now use to monitor your Email campaigns.

You can read about it here: Monitor Your SparkPost data with Power BI

Power BI – Weekly Service Update

Not only was there a host of announcements at SQL Pass, there was the weekly Power BI Service update.

Once again I am going to quickly highlight what there is in this week’s update.

They have made quite a few improvements with regards to the way we can share the dashboards in Power BI. All of these updates make it a lot easier to share the dashboard and to enable people to see how good Power BI is. The additions are (Sharing the Dashboards with AD Groups, People Picker and Sharing with a large number of Email addresses)

Along with this is the ability to start passing parameters into the URL. I have no doubt that passing URL parameters will keep on increasing and giving additional flexibility in the Power BI service.

You can read about it here: Power BI Weekly Service Update

Power BI – Personal Gateway Update

There was an update late last week for the Power BI Personal Gateway and it is mostly around bug fixes and performance improvements. Which is great to see because I do know that often we want it to run as smoothly and quickly as possible

You can find more information here: New version of Personal Gateway is now live!

Power BI – Tiles in SharePoint

And finally the guys from DevScope have now created a Power BI Tile for SharePoint.

I think that this will work really well, because it will give the ability to showcase all the work done in your Power BI reports, as well as not having to re-create reports over and over again.

If you want to find more details and pricing, you can find it here: Power BI Tiles for SharePoint

SSIS – Using SSIS Variable within a SQL Script with Temp Tables – Error at Data Flow Task : No Column information was returned by the SQL Command

Below details what I was trying to do within SSIS and how I got it to work.

 

Overview

  1. I was using a SQL Script which contained some temp
    tables which was used in the result set.
  2. When I then put this into an OLE DB Source I would get the error shown below:

 

  1. I then tried to see if it would work when I created a SQL Command from Variable.
    1. And this would result in the same error as above.
  2. If I did click on OK, I would then the following in the Columns window as shown below:
  3. So below is a solution which I found to work to get the data from a SQL Query that uses temp tables.

 

Example Data

For our solution below we are going to use the following sample data.

  • The first
    variable that we are going to be using will be for a DayNumber
    • NOTE: This is because we want to go back in time, so we want to go back and loop through the past 10 days.
  • The second
    variable that we are going to be using is our actual SQL Query.
    • So this will be very simply put with the following below:
    • NOTE: This is a very simple
      query and I want to just use this as an example. I am certain in most of your scenarios it will be a much more complex query that is giving you the above error within SSIS.

 

Solution

  1. The first thing that you will need to do, is to create an ADO.NET Source.
  2. Next you will need to ensure that you have two
    variables created
    1. The first
      variable that you want to pass into your SQL
      Statement
    2. The second
      variable is your SQL query.
  3. As with our example above we created the following variables within SSIS
    1. From the above you will see that we have created some additional
      variables as explained below:
      1. DayNumber is the day number variable that we want to pass to our SQL Query.
      2. StartNumber is the starting variable in our For Loop Container
      3. EndNumber is the ending variable in our For Loop Container
      4. Query_GetDayNumber is the query which is going to extract our Day Number and put this into a variable.
      5. Query_ExtractData is the SQL Query where we are going to extract our data including passing the variable.
  4. Next we will create and configure our For Loop Container with the following:
    1. NOTE: This is so that we can then know when to exit our of our For Loop Container
    2. NOTE II: Typically, you will have your StartNumber and EndNumber
      populated by an Execute SQL Task so that it can always be dynamic.
  5. Next we will configure our variable for the Query_GetDayNumber so that it gets populated with the correct details as it loops
    through each time.
    1. Click on Variables, then where we have the Query_GetDayNumber
      click on the Ellipses button on the right
      hand side under
      Expression
    2. This will then open the Expression
      Builder
      Window
    3. Now as with our example, we know that our
      number will always start at 1 and the DayNumber will always be the same as the StartNumber.
      1. NOTE: There will be situations where you will need to run an actual SQL Query against your data to extract the required information.
    4. And we put in the following:
      1. NOTE: The reason for converting it to a string is because our StartNumber
        variable is defined as an Int32.
      2. You can then click on the Evaluate
        Expression to make sure that it is working.
    5. Then click Ok.
  6. Next we will need to configure our variable for the Query_ExtractData, so that we can then pass our above variable within our SQL Query by doing the following:
    1. Click on Variables, then where we have the Query_ ExtractData
      click on the Ellipses button on the right
      hand side under
      Expression
    2. Now we will put in our SQL Query from above, along with the variable as shown below:
    3. NOTE: If you have a look at the above
      syntax you will see that we have put our variable
      DayNumber into our Expression.
      1. You can then click on the Evaluate Expression to make sure that it is working.
    4. Then click
      Ok.
  7. Next create an Execute SQL Task and configure it with the following below so that it will be used to populate our DayNumber
    variable.
    1. Go into the Properties and configure the General
      page with the following below:
    2. NOTE: A few quick things to note on the above:
      1. We have set the Result Set to Single Row
        1. This is to allow our variable to be returned into a result set.
      2. Our SQLSourceType has been set to Variable.
        1. This is so that every time the For Loop Container runs and loops through it will then get the new value.
      3. SourceVariable
        1. This has been selected from the drop down and will be our query which we created earlier.
        2. Which on each execution of the For Loop Container will go and get the values we require.
    3. Then click on the Result Set and put in the following:
    4. Then click
      Ok.
    5. Now drag this into your For Loop Container
  8. Next drag in a Data Flow Task and rename it to Extract Data
  9. Then double
    click and go into your Data Flow Task
  10. Now Drag in an ADO NET Source.
    1. Then ensure that you have not selected the ADO NET Source and have clicked on the sheet within SSIS, and go into the properties.
    2. Next to Expressions
      click on the Ellipses
      Button
    3. Under Property click on the Drop Down and select the following as shown below
    4. Then click on the Ellipses next to Expression.
    5. Now in the Expression Window put in the following variable as shown below:
      1. NOTE: From the above you will see that this is our Variable query that we created and configured in step 6 above.
    6. Click on the Evaluate
      Expression and you will then see the actual query below in the Evaluated value:
      1. NOTE: In the above it has gotten the number 12 from the default
        value in our Variable for DayNumber.
    7. Then click Ok.
    8. Now go back into your ADO NET Source.
      1. Ensure that you have got the correct connection to your data source.
      2. Then under Data access mode
        change this to SQL Command
      3. Then you should see your query populated below as with our example:
      4. NOTE: It got this query
        information from our Expression, which in turn got the information from our variable.
    9. You can then click on Columns to see that the query
      runs and gets the required
      information.
    10. Then click Ok.
  11. Then the final step is to then link it to your destination table.
    1. NOTE: You can use either an OLE DB Destination or an ADO NET Destination.
  12. Next make sure that you drag your Data flow task into your For Loop Container
    1. Then ensure that you place the Success
      Precedence
      Constraint
      between your Execute SQL Task and your Data Flow Task as shown below.
  13. Now you can run your SSIS package and it should work
    successfully.

This post was published to myfriendjoobs at 1:57:59 PM 8/5/2015

SSIS – Using SSIS Variable within a SQL Script with Temp Tables – Error at Data Flow Task : No Column information was returned by the SQL Command

 

 

 

BI-NSIGHT Power BI Sweet IQ , Summer Release – SSIS Feature Pack for Azure – Excel 2016 New Chart Options (Treemap, Sunburst, Histogram, Box & Whisker, Waterfall)

Well this week there were some updates and fortunately not too many so where we go!

Power BI – Sweet IQ

This week, there was yet another release of a content pack for Power BI, this time it was relating to a company called Sweet IQ.

They position themselves as provider that can leverage your local search and increase your visibility on the web. Whilst looking at the blog post, I have to say that this is once again a great way for customers to get quick and easy access to their data. And enable the customers to interact and see how it is performing.

You can read about the post from Microsoft here: Analyze and Monitor your SweetIQ Data with Power BI

Summer Release of Power BI

I am not going to go into too much detail around this, because all the information that I could find was on the blog post from Microsoft. But it does bode well for the next release of the Power BI Designer.

And for me I think this is where I would start at looking the Power BI Designer over say using Excel as a source or using the web based version to author reports.

As you can see from the above screenshot, it appears that there is going to be a whole host of things that you can customize within the Power BI Designer. And this will make their existing reports, which look really good, go from good, to be a standout. Especially since from what I can see above, I have not seen this in other products of similar nature.

You can read the blog post from Microsoft here: We’re on for a great Designer Summer…

SSIS Feature Pack for Azure

As you can see from the above screenshot, these are the items that are available in the SSIS Feature pack for Azure.

I personally think that this is great, as in the past, it has been a bit of trickery to get data from On Premise into Azure. Personally I know of a few people who have had some struggles. As well as a lot of people who do know and understand SSIS, really well. So this would fit in with their existing skills.

I also did see that the Varigence have created a webinar for this here: Moving ETL Loads to Azure Part1 Webinar Content

I have personally not tested it yet, but I am sure that it will be a lot easier to get data into Azure!

You can read up all about it on MSDN here: Azure Feature Pack

As well as downloading the SSIS Feature Pack here: Microsoft SQL Server 2014 Integration Services Feature Pack for Azure | Microsoft SQL Server 2012 Integration Services Feature Pack for Azure

Excel 2016 – New Charting Options (Treemap, Sunburst, Histogram, Box & Whisker, Waterfall)

Every week or so, I check to see if there are any Office 2016 updates, and this week there was!

So one of the things that I have been looking for since watching the Ignite video’s is for the new chart types. And finally this week, we have the new chart types in Excel 2016!

The one of the things that I can currently note, and I do hope that this will not be the case once the final product is released, is that it says that it cannot be used with a Pivot Table. I might not totally understand the dynamics behind how the chart is created. But how awesome would it be if we could use it with our data from Power Pivot, or our SSAS Cubes!

I have also noticed that they have changed the multi select button on your slicer to look a little more attractive as shown below.

There might be a few other changes, but currently I have not seen them or found them out!

That is it for this week!