SQL Server | Dutch Data Dude

Passing command line settings to SQL Server Integration Services (SSIS) packages using dtexec on Linux

on March 1, 2019

This is my first blog post since I moved to Redmond. A lot of time has passed, sorry for that! I kept busy filling out forms, forms and some more forms. After that came more forms. I dream about forms, I breath forms. I am a form. Wait, let’s stop there.

Recently I had to figure out how to pass in settings to an SQL Server Integration Services (SSIS) package when calling it using dtexec from bash on Linux.

The dtexec utility is utility to execute a SSIS packages. The name goes back to the time that SSIS was called Data Transformation Services (DTS) – that is also the reason why SSIS packages carry the .dtsx extension. The dtexec utility is available for both Windows and Linux.

One of the interesting things you can do with dtexec is passing in values to variables (using the /Par(ameter) option or even change a connection string at runtime (using the /Conn[ection]). I had to do the latter. Here is where the fun started. The /Par and /Conn options (and maybe others) expect something like this: [name];[value]. For example:

dtexec /F myfile.dtsx /Conn "MasterSQL";"Data Source=myserver;User ID=myuser;Initial Catalog=master;Password=mypassword"

1	dtexec /F myfile.dtsx /Conn "MasterSQL";"Data Source=myserver;User ID=myuser;Initial Catalog=master;Password=mypassword"

Notice the ‘;’ between the name of the source connection and the connection string. This is all great, unless you are trying to call dtexec from the bash command line in Linux, because ; actually means something in bash: when bash sees ; it thinks the current command has ended and the next instruction follows. That is not what we want here.

I had to escape a number of items to get this to work correct in bash:

/CONN "MasterSQL"\;"\"Data Source=myserver;User ID=myuser;Initial Catalog=mydb;Password=mypassword\""

1	/CONN "MasterSQL"\;"\"Data Source=myserver;User ID=myuser;Initial Catalog=mydb;Password=mypassword\""

See what I did there? I had to escape the first ; (between ‘MasterSQL’ and ‘Data Source’) as well as escape the quotes that surround the data source definition itself and have it double quoted.

If you need to pass in a value to a string parameter you need to follow the same logic:

/PAR "$Project::myparam"\;"\"myvalue\""

1	/PAR "$Project::myparam"\;"\"myvalue\""

For a string variable you can pass in the value like this:

/SET "\\package.variables[myvariable].Value"\;"\"myvalue\""

1	/SET "\\package.variables[myvariable].Value"\;"\"myvalue\""

Hope this helps!

Farewell Tour

on September 18, 2018

by Jeroen ter Heerdt 0 Comments

DUTCH DATA DUDE
FAREWELL TOUR

A CHANGE IS GONNA COME
Na bijna 10 jaar waag ik de grote stap. Ik verhuis naar Amerika en ga voor Microsoft in Seattle aan de slag. Daarmee komt er een einde aan mijn werk als Dutch Data Dude in Nederland (en daarbuiten). Werk dat ik met veel passie heb gedaan en heb kunnen doen dankzij en met jullie. Om dit deel gepast af te sluiten doe ik een FAREWELL TOUR: ik klim nog een laatste keer op het podium. Bij jullie, voor jullie. Want jullie hebben mij in staat gesteld te worden wie ik wilde zijn: het geluid van Microsoft rondom data en Artificial Intelligence. Dutch Data Dude signs off.

DE TOUR

Tijdens de FAREWELL TOUR geef ik in korte tijd een flink aantal presentaties.
Dit organiseer ik samen met partners in October / November 2018. Het onderwerp van de presentaties is Business Intelligence, Big Data, Machine Learning of Artificial Intelligence. Soms staat mijn presentatie op zich, soms is het onderdeel van een groter geheel. Alles in samenspraak met de organiserende partner. Ik hoop je te mogen begroeten tijdens de FAREWELL TOUR.

DE LOCATIES

Hieronder staan de locaties die ik aandoe tijdens mijn FAREWELL TOUR. Voor publieke evenementen kun je je direct inschrijven via de aangegeven link.

Datum	Tijd	Plaats	Organisatie	Soort evenement	Inschrijven
17 October	10:00	Bussum	CraniumConnect	Voor leden	Website
17 October	17:00	Den Haag	TopBI	Intern	n.v.t.
18 October	17:00	Amsterdam	QNH	Intern	n.v.t.
23 October	16:00	Amsterdam	Datafied	Publiek	Inschrijven
25 October	18:00	Rotterdam	Motion10	Publiek	Inschrijven
29 October	16:30	Eindhoven	Valid	Publiek	Inschrijven
30 October	13:30	Veenendaal	HSO	Op uitnodiging	Website
30 October	16:00	Huizen	Sigma Data Consulting	Intern	n.v.t.
31 October	17:00	Vianen	Rubicon	Publiek	Inschrijven
2 November	09:00	Amsterdam	Breinwave	Op uitnodiging	n.v.t.
6 November	13:30	Veenendaal	Hillstar	Publiek	Inschrijven
7 November	19:00	Veenendaal	Infosupport	Publiek (beperkt aantal plaatsen)	Inschrijven
8 November	14:30	Hoofddorp	Macaw	Publiek	Inschrijven
12 November	13:30	Utrecht	Axians	Intern	n.v.t.
13 November	17:30	Utrecht	Caesar Experts	Publiek	Inschrijven
14 November	10:00	Bussum	CraniumConnect	Voor leden	Website
14 November	18:00	Vianen	Sogeti	Intern	n.v.t.
15 November	15:00	Amsterdam	Ireckonu	Op uitnodiging	n.v.t.
29 November	17:00	Den Bosch	Power BI Gebruikersgroep	Publiek	Inschrijven

SQL Server 2017: It’s here!

on October 2, 2017

by Jeroen ter Heerdt 0 Comments

Anxiously you have waited for this day. The day you could finally get your hands on the latest version of the best database platform available. Today is that day. SQL Server 2017 is available and it runs on Windows, Linux and Docker. Find more info here: https://www.microsoft.com/en-us/sql-server/sql-server-2017. What changed? So many things it is hard to mention them all. Have a look at the Ignite sessions to get a feel for what happened.

SQL Server news from connect start to resonate

on December 27, 2016

by Harry Tolsma 0 Comments

Hi all,

A month ago at the Connect event in New York Microsoft did some anouncements on SQL Server vNext and Azure Data Services.
It’s good to see the traction it’s getting!

I work with a lot of partners on implementing Microsoft stuff and the partners are very keen in getting the latest info on new capabilities.
Also they enlist in new online and in classroom courses on the new stuff. So let’s take a quick look:

Azure Data Lake store and analytics went live in Novmeber, This is a no limits data lake with on-demand analytics that instantly scales to your needs. With petabyte size files and trillions of objects and parallel processing!
R Server for Azure HD Insight for Largest R compatible parallel analytics ML library with terabyte-scale machine learning!
SQL Server on Linux public preview is released. Check in out on a linux box!
Moving selected Enterprise Edition capabilities to SQL Server Standard like in memory and Polybase.

And last but not least: connectivity to all top programming languages!

Power BI Refresh scenarios against data storage solutions explained

on June 7, 2016

by Jeroen ter Heerdt 0 Comments

A recurring theme with customers and partners is Power BI data refresh. More specifically, there is some confusion on what refresh scenario requires a Pro version and what can be done with the Free version of Power BI. I made the diagram below to help explain this. It shows the refresh scenarios against data storage solutions, such as SQL Azure, SQL in a virtual machine in Azure or SQL server on premises. I used these as examples, there are other options as well. I think the overall time carries over to other data storage solutions. The diagram shows the refresh that can be done using a Power BI Free account as orange and the refresh scenarios that need Power BI Pro as green lines. As shown in the diagram, if you want to refresh against on-premises sources or a database running in a VM in Azure you will need a gateway and Power BI Pro. This applies not only to the creator of the report and schedule but also to every consumer. If you use PAAS solutions for data storage in Azure such as SQL Azure, it becomes a bit more difficult and it is really dependent on the type of refresh required. If you need a refresh cycle higher than once a day (either max 8 times per 24 hours or live) you will need Power BI Pro. If you just want to refresh against such as SQL Azure and once a day is enough you can do that using Power BI Free. Again, the license requirement carries over from author to viewer; if the author of the report requires Pro, then the viewers also need Pro.

Power BI Refresh scenarios against data storage solutions

Hope this helps. If you have any questions or feedback, please comment below!

SQL Server op Linux? Ja dus!

on March 9, 2016

by Harry Tolsma 0 Comments

Als iemand een paar jaar terug tegen mij had gezegd dat Microsoft Multi platform zou gaan en dat ook SQL Server op Linux zou uitkomen, dan had ik hem voor gek verklaard. Echter sinds de komst van Satya Nadella als CEO en mensen als Scott Guthrie (CVP voor Cloud & Enterprise) waait er een frisse wind door Microsoft!

En als Microsoft employee voelde ik natuurlijk al wat aankomen, maar het is toch altijd weer top om het nieuws dan nu in de buitenwereld te zien landen: Gister heeft Microsoft de Linux versie van SQL Server geannonceerd in preview. Dit betreft voorlopig alleen het RDBMS en niet de overige SQL tools als SSRS en SSAS, maar een mooie start om nu ook op Linux de (volgens Gartner) de leading database te kunnen runnen!

Ik zou het leuk vinden als jullie na het testen van deze versie op het blog zouden willen reageren met de eerste indruk van de performance van deze preview versie.

De blogpost van Scott over de annoncering: http://blogs.microsoft.com/blog/2016/03/07/announcing-sql-server-on-linux/

SQL groeten,

Harry

Automatically building a Microsoft BI machine using PowerShell – Final post (post #14)

on January 19, 2016

by Jeroen ter Heerdt 0 Comments

This post is #14 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.

In this series:

Start of series – introduction and layout of subjects
Post #2 – Preparation: install files using Azure disk
Post #3 – Preparation: install files using Azure File Service
Post #4 –Preparation: logging infrastructure
Post #5 – Master script
Post #6 – Disabling Internet Explorer Enhanced Security Configuration
Post #7 – Active Directory setup
Post #8 – Configuring Password policy
Post #9 – Installing System Center Endpoint Protection
Post #10 – Installing SQL Server
Post #11 – Installing SharePoint Server
Post #12 – Installing PowerPivot for SharePoint
Post #13 – Configuring PowerPivot for SharePoint

Wow. This has been a long and wild ride. But you and I made it together. We now have the full recipe to automatically configure a Microsoft BI demo machine with PowerShell. Of course there is more to be done, such as configuring other Service Accounts and deploying demo content; this script however saves me a lot of time every time I need to stand up a new demo machine.

You can download the script on Github. Please note (again) that the code is provided as-is and you should use it at your own risk. It is probably still buggy but should give you a good starting point to adapt it to your needs.

I enjoyed the ride with you; hope I made your life a bit easier of the course of this series. Enjoy!

Automatically building a Microsoft BI machine using PowerShell – Configuring PowerPivot (post #13)

on January 12, 2016

by Jeroen ter Heerdt 0 Comments

This post is #13 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.

In this series so far:

Now that PowerPivot for SharePoint has been installed, we need to configure it. I split the configuration into two parts since we need a reboot in between and used MSDN for reference: http://msdn.microsoft.com/en-us/library/hh230903.aspx.

Step A: configuring SharePoint and deploying PowerPivot features

In Post #11 we talked about installing SharePoint, but the actual SharePoint provisioning was not done then. We will do it here in one go with installing PowerPivot features.

Function ConfigurePowerPivot
{
    Param(
        [Parameter(Mandatory=$true,HelpMessage="Passphrase required")]
        [ValidateNotNullOrEmpty()]
        $passphrase,
        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        $Password
    )
    Write-Host "Step 8: Configure PowerPivot"
    try {
    #Load Configure PowerPivot ps1
    $scriptPath = Split-Path -parent $global:script
    . ('C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\ConfigurePowerPivot.ps1')
    
    #Create a user for SharePoint DB connection
    #if required, remove the ad user
    Get-ADUser -Filter {Identity -eq '$global:spAccount'} | Remove-ADUser
    CreateServiceAccount -AccountName $global:spAccount -DisplayName "SharePoint Farm account" -Description "Account for SharePoint Farm" -Path $global:path -Password $Password
    $spAccountFQ = $global:domainpart+"\"+$global:spAccount
    $pwd = convertto-securestring $Password -asplaintext -force
    & "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\BIN\psconfig.exe" -cmd configdb -create -server $global:HostName -database 'SharePoint_Config' -user $spAccountFQ -password $Password -passphrase $passphrase -admincontentdatabase 'SharePoint_Admin' -cmd helpcollections -installall -cmd secureresources -cmd services -install -cmd installfeatures -cmd adminvs -provision -port 2000 -windowsauthprovider onlyusentlm -cmd applicationcontent -install -cmd quiet 
    Add-PSSnapin Microsoft.SharePoint.PowerShell
    Add-SPSolution -LiteralPath 'C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\powerpivotfarmsolution.wsp'
    Add-SPSolution -LiteralPath 'C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\PowerPivotFarm14Solution.wsp'
    Add-SPSolution -LiteralPath 'C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\powerpivotwebapplicationsolution.wsp'
    DeployFarmSolution $false
    DeployWebAppSolutionToCentralAdmin $false
    Install-SPFeature -path PowerPivotFarm -Force
    Install-SPFeature -path PowerPivotFarm -Force -CompatibilityLevel 14
    Install-SPFeature -path PowerPivotCA -Force
    InstallSiteCollectionFeatures
    
    Write-Host "PowerPivot Part 1 Configured. Computer needs to be restarted before PowerPivot configuration can continue." -ForegroundColor Green
    if ($global:DoAllTasks) {
        Set-Restart-AndResume $global:script "9"
        }

    }
    catch {
        Write-Host "Failed to configure PowerPivot. Error: $_.Exception.Message" -ForegroundColor Red
    }
}

Function ConfigurePowerPivot

{

Param(

[Parameter(Mandatory=$true,HelpMessage="Passphrase required")]

[ValidateNotNullOrEmpty()]

$passphrase,

[Parameter(Mandatory=$true)]

[ValidateNotNullOrEmpty()]

$Password

)

Write-Host "Step 8: Configure PowerPivot"

try {

#Load Configure PowerPivot ps1

$scriptPath = Split-Path -parent $global:script

. ('C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\ConfigurePowerPivot.ps1')

#Create a user for SharePoint DB connection

#if required, remove the ad user

Get-ADUser -Filter {Identity -eq '$global:spAccount'} | Remove-ADUser

CreateServiceAccount -AccountName $global:spAccount -DisplayName "SharePoint Farm account" -Description "Account for SharePoint Farm" -Path $global:path -Password $Password

$spAccountFQ = $global:domainpart+"\"+$global:spAccount

$pwd = convertto-securestring $Password -asplaintext -force

& "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\BIN\psconfig.exe" -cmd configdb -create -server $global:HostName -database 'SharePoint_Config' -user $spAccountFQ -password $Password -passphrase $passphrase -admincontentdatabase 'SharePoint_Admin' -cmd helpcollections -installall -cmd secureresources -cmd services -install -cmd installfeatures -cmd adminvs -provision -port 2000 -windowsauthprovider onlyusentlm -cmd applicationcontent -install -cmd quiet

Add-PSSnapin Microsoft.SharePoint.PowerShell

Add-SPSolution -LiteralPath 'C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\powerpivotfarmsolution.wsp'

Add-SPSolution -LiteralPath 'C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\PowerPivotFarm14Solution.wsp'

Add-SPSolution -LiteralPath 'C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\powerpivotwebapplicationsolution.wsp'

DeployFarmSolution $false

DeployWebAppSolutionToCentralAdmin $false

Install-SPFeature -path PowerPivotFarm -Force

Install-SPFeature -path PowerPivotFarm -Force -CompatibilityLevel 14

Install-SPFeature -path PowerPivotCA -Force

InstallSiteCollectionFeatures

Write-Host "PowerPivot Part 1 Configured. Computer needs to be restarted before PowerPivot configuration can continue." -ForegroundColor Green

if ($global:DoAllTasks) {

Set-Restart-AndResume $global:script "9"

}

catch {

Write-Host "Failed to configure PowerPivot. Error: $_.Exception.Message" -ForegroundColor Red

}

Step B: updating farm credentials and starting service applications

After the PowerPivot features have been deployed we need to configure Service Applications to get PowerPivot to work.

Function ConfigurePowerPivotPart2 {
    Param(
        [Parameter(Mandatory=$true,HelpMessage="Passphrase required")]
        [ValidateNotNullOrEmpty()]
        $passphrase,
        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        $Password
    )

    try {
     #Load Configure PowerPivot ps1
    $scriptPath = Split-Path -parent $global:script
    . ('C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\ConfigurePowerPivot.ps1')
    Add-PSSnapin Microsoft.SharePoint.PowerShell
    Write-Host "DEBUG: updating Farm Credentials"
    $spAccountFQ = $global:domainpart+"\"+$global:spAccount
    stsadm.exe -o updatefarmcredentials -userlogin $spAccountFQ -password $Password
    Write-Host "DEBUG: New-PowerpivotSystemServiceInstance"
    New-PowerPivotSystemServiceInstance -Provision:$true
    Write-Host "DEBUG: New-PowerPivotServiceApplication"
    New-PowerPivotServiceApplication -ServiceApplicationName 'PowerPivot Service Application' -DatabaseServerName $global:HostName -DatabaseName 'PowerPivotServiceApplication' -AddToDefaultProxyGroup:$true
    Write-Host "DEBUG: Set-PowerPivotSystemService"
    Set-PowerPivotSystemService -Confirm:$false
    
    Write-Host "DEBUG: Creating user DefAppPool"
    $appAccountName = "DefAppPool"
    $appAccountNameFQ = $global:domainpart+"\"+$appAccountName
    CreateServiceAccount -AccountName $appAccountName -DisplayName "Default Application Pool" -Description "Service Account for Default Application Pool" -Path $global:path -Password $Password
    Write-Host "DEBUG: CreateWebApplication"
    CreateWebApplication 'SharePoint - 80' $global:HostName 'Default Application Pool' $appAccountNameFQ $pwd $global:HostName 'DefaultWebApplication'
    Write-Host "DEBUG: DeployWebAppSolution"
    DeployWebAppSolution $global:httpHostName 2047 $false
    Write-Host "DEBUG: New-SPSite"
    New-SPSite -Url $global:httpHostName -OwnerEmail 'me@example.com' -OwnerAlias $global:currentUserName -Template 'PowerPivot#0' -Name  'PowerPivot Site'
    Write-Host "DEBUG: EnableSiteFeatures"
    EnableSiteFeatures $global:httpHostName $true
    Write-Host "DEBUG: StartService SPWindowsTokenServiceInstance"
    StartService "Microsoft.SharePoint.Administration.Claims.SPWindowsTokenServiceInstance"
    Write-Host "DEBUG: StartSecureStoreService"
    StartSecureStoreService
    Write-Host "DEBUG: CreateSecureStoreApplicationService"
    CreateSecureStoreApplicationService $global:HostName 'Secure Store Service'
    Write-Host "DEBUG: CreateSecureStoreApplicationServiceProxy"
    CreateSecureStoreApplicationServiceProxy 'Secure Store Service' 'Secure Store Proxy'
    Write-Host "DEBUG: UpdateSecureStoreMasterKey"
    UpdateSecureStoreMasterKey 'Secure Store Proxy' $passphrase 
    Write-Host "DEBUG: CreateUnattendedAccountForDataRefresh"
    CreateUnattendedAccountForDataRefresh $global:httpHostName 'PowerPivotUnattendedAccount' 'PowerPivot Unattended Account for Data Refresh' $spAccountFQ $pwd
    Write-Host "DEBUG: StartService ExcelServerWebServiceInstance"
    StartService "Microsoft.Office.Excel.Server.MossHost.ExcelServerWebServiceInstance"
    Write-Host "DEBUG: New-SPExcelServiceApplication"
    New-SPExcelServiceApplication -name 'ExcelServiceApp1' -Default -ApplicationPool 'SharePoint Web Services System' | Get-SPExcelServiceApplication | Set-SPExcelServiceApplication | iisreset Set-SPExcelFileLocation -ExternalDataAllowed 2 -WorkbookSizeMax 200 -WarnOnDataRefresh:$false -ExcelServiceApplication 'ExcelServiceApp1' -identity 'http://'
    Write-Host "DEBUG: AddExcelBIServer"
    AddExcelBIServer
    Write-Host "DEBUG: SetECSUsageTracker"
    SetECSUsageTracker 'ExcelServiceApp1'
        
    Write-Host "PowerPivot Configured" -ForegroundColor Green
    if ($global:DoAllTasks) {
        Set-Restart-AndResume $global:script "10"
        }

    }
    catch {
        Write-Host "Failed to configure PowerPivot. Error: $_.Exception.Message" -ForegroundColor Red
    }

}

Function ConfigurePowerPivotPart2 {

Param(

[Parameter(Mandatory=$true,HelpMessage="Passphrase required")]

[ValidateNotNullOrEmpty()]

$passphrase,

[Parameter(Mandatory=$true)]

[ValidateNotNullOrEmpty()]

$Password

)

try {

#Load Configure PowerPivot ps1

$scriptPath = Split-Path -parent $global:script

. ('C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\ConfigurePowerPivot.ps1')

Add-PSSnapin Microsoft.SharePoint.PowerShell

Write-Host "DEBUG: updating Farm Credentials"

$spAccountFQ = $global:domainpart+"\"+$global:spAccount

stsadm.exe -o updatefarmcredentials -userlogin $spAccountFQ -password $Password

Write-Host "DEBUG: New-PowerpivotSystemServiceInstance"

New-PowerPivotSystemServiceInstance -Provision:$true

Write-Host "DEBUG: New-PowerPivotServiceApplication"

New-PowerPivotServiceApplication -ServiceApplicationName 'PowerPivot Service Application' -DatabaseServerName $global:HostName -DatabaseName 'PowerPivotServiceApplication' -AddToDefaultProxyGroup:$true

Write-Host "DEBUG: Set-PowerPivotSystemService"

Set-PowerPivotSystemService -Confirm:$false

Write-Host "DEBUG: Creating user DefAppPool"

$appAccountName = "DefAppPool"

$appAccountNameFQ = $global:domainpart+"\"+$appAccountName

CreateServiceAccount -AccountName $appAccountName -DisplayName "Default Application Pool" -Description "Service Account for Default Application Pool" -Path $global:path -Password $Password

Write-Host "DEBUG: CreateWebApplication"

CreateWebApplication 'SharePoint - 80' $global:HostName 'Default Application Pool' $appAccountNameFQ $pwd $global:HostName 'DefaultWebApplication'

Write-Host "DEBUG: DeployWebAppSolution"

DeployWebAppSolution $global:httpHostName 2047 $false

Write-Host "DEBUG: New-SPSite"

New-SPSite -Url $global:httpHostName -OwnerEmail 'me@example.com' -OwnerAlias $global:currentUserName -Template 'PowerPivot#0' -Name 'PowerPivot Site'

Write-Host "DEBUG: EnableSiteFeatures"

EnableSiteFeatures $global:httpHostName $true

Write-Host "DEBUG: StartService SPWindowsTokenServiceInstance"

StartService "Microsoft.SharePoint.Administration.Claims.SPWindowsTokenServiceInstance"

Write-Host "DEBUG: StartSecureStoreService"

StartSecureStoreService

Write-Host "DEBUG: CreateSecureStoreApplicationService"

CreateSecureStoreApplicationService $global:HostName 'Secure Store Service'

Write-Host "DEBUG: CreateSecureStoreApplicationServiceProxy"

CreateSecureStoreApplicationServiceProxy 'Secure Store Service' 'Secure Store Proxy'

Write-Host "DEBUG: UpdateSecureStoreMasterKey"

UpdateSecureStoreMasterKey 'Secure Store Proxy' $passphrase

Write-Host "DEBUG: CreateUnattendedAccountForDataRefresh"

CreateUnattendedAccountForDataRefresh $global:httpHostName 'PowerPivotUnattendedAccount' 'PowerPivot Unattended Account for Data Refresh' $spAccountFQ $pwd

Write-Host "DEBUG: StartService ExcelServerWebServiceInstance"

StartService "Microsoft.Office.Excel.Server.MossHost.ExcelServerWebServiceInstance"

Write-Host "DEBUG: New-SPExcelServiceApplication"

New-SPExcelServiceApplication -name 'ExcelServiceApp1' -Default -ApplicationPool 'SharePoint Web Services System' | Get-SPExcelServiceApplication | Set-SPExcelServiceApplication | iisreset Set-SPExcelFileLocation -ExternalDataAllowed 2 -WorkbookSizeMax 200 -WarnOnDataRefresh:$false -ExcelServiceApplication 'ExcelServiceApp1' -identity 'http://'

Write-Host "DEBUG: AddExcelBIServer"

AddExcelBIServer

Write-Host "DEBUG: SetECSUsageTracker"

SetECSUsageTracker 'ExcelServiceApp1'

Write-Host "PowerPivot Configured" -ForegroundColor Green

if ($global:DoAllTasks) {

Set-Restart-AndResume $global:script "10"

}

catch {

Write-Host "Failed to configure PowerPivot. Error: $_.Exception.Message" -ForegroundColor Red

}

Now we have seen all the steps required to build a Microsoft BI demo machine! The next post will serve as a wrap up and present a download for the full script.

Automatically building a Microsoft BI machine using PowerShell – Installing PowerPivot for SharePoint (post #12)

on January 5, 2016

by Jeroen ter Heerdt 1 Comment

This post is #12 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.

In this series so far:

Ok, now that both SQL Server and SharePoint Server are installed, we just need to set up PowerPivot for SharePoint and configure it. Easy huh? Well, it turns out it is pretty difficult to get it right. Installation is not difficult (this post) but the configuration is harder (the next post). Here is how to install PowerPivot. I used MSDN for the info: http://msdn.microsoft.com/en-us/library/ee210645.aspx.

Installing PowerPivot involves mounting the SQL Server Installation Media and calling the setup with the right parameters.

Function InstallPowerPivot
{
Param(
        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        $Password
)
    Write-Log -Verbose  "Step 7: Install PowerPivot"
    #MOUNT SQL ISO
    $mountresult = Mount-DiskImage -ImagePath $global:pathToSQLISO -PassThru
    $driveLetter = ($mountresult | Get-Volume).DriveLetter
    $setupFile = $driveLetter+":\setup.exe"
    #Remove Service Account if it already existed
    Get-ADServiceAccount -Filter {Name -eq 'PP'} | Remove-ADServiceAccount
    $ppAccountName = "PP"
    $ppAccountNameFQ = $global:domainpart+"\"+$ppAccountName
    CreateServiceAccount -AccountName $ppAccountName -DisplayName "PowerPivot" -Description "Service Account for PowerPivot for SharePoint" -Path $global:path -Password $Password
    #do PP installation
    #trying with plain text pwd in call
    $process = Start-Process -NoNewWindow -Wait $setupFile -ArgumentList "/ACTION=INSTALL /IACCEPTSQLSERVERLICENSETERMS /Q /INSTANCENAME=POWERPIVOT /ERRORREPORTING=1 /SQMREPORTING=1 /ASSVCACCOUNT=$ppAccountNameFQ /ASSVCPASSWORD=$Password /ASSYSADMINACCOUNTS=$global:currentUserName /ROLE=SPI_AS_ExistingFarm"
    #SPI_AS_ExistingFarm
    
    #dismount
    Dismount-DiskImage -ImagePath $global:pathToSQLISO
    Write-Log -Verbose  "If above an error is shown please check out C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\Summary.txt"
    Write-Log -Verbose  "PowerPivot Installed"
    if ($global:DoAllTasks) {
        Set-Restart-AndResume $global:script "9"
        }
}

Function InstallPowerPivot

{

Param(

[Parameter(Mandatory=$true)]

[ValidateNotNullOrEmpty()]

$Password

)

Write-Log -Verbose "Step 7: Install PowerPivot"

#MOUNT SQL ISO

$mountresult = Mount-DiskImage -ImagePath $global:pathToSQLISO -PassThru

$driveLetter = ($mountresult | Get-Volume).DriveLetter

$setupFile = $driveLetter+":\setup.exe"

#Remove Service Account if it already existed

Get-ADServiceAccount -Filter {Name -eq 'PP'} | Remove-ADServiceAccount

$ppAccountName = "PP"

$ppAccountNameFQ = $global:domainpart+"\"+$ppAccountName

CreateServiceAccount -AccountName $ppAccountName -DisplayName "PowerPivot" -Description "Service Account for PowerPivot for SharePoint" -Path $global:path -Password $Password

#do PP installation

#trying with plain text pwd in call

$process = Start-Process -NoNewWindow -Wait $setupFile -ArgumentList "/ACTION=INSTALL /IACCEPTSQLSERVERLICENSETERMS /Q /INSTANCENAME=POWERPIVOT /ERRORREPORTING=1 /SQMREPORTING=1 /ASSVCACCOUNT=$ppAccountNameFQ /ASSVCPASSWORD=$Password /ASSYSADMINACCOUNTS=$global:currentUserName /ROLE=SPI_AS_ExistingFarm"

#SPI_AS_ExistingFarm

#dismount

Dismount-DiskImage -ImagePath $global:pathToSQLISO

Write-Log -Verbose "If above an error is shown please check out C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\Summary.txt"

Write-Log -Verbose "PowerPivot Installed"

if ($global:DoAllTasks) {

Set-Restart-AndResume $global:script "9"

}

Next time: configuring PowerPivot.

Automatically building a Microsoft BI machine using PowerShell – Installing SharePoint (post #11)

on December 29, 2015

by Jeroen ter Heerdt 0 Comments

This post is #11 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.

In this series so far:

Wow, so the last post was pretty intense, wasn’t it? I think we are ready for the next one: installing SharePoint. To build this script I used the following sources: http://social.technet.microsoft.com/wiki/contents/articles/14582.sharepoint-2013-install-prerequisites-offline-or-manually-on-windows-server-2012-a-comprehensive-guide.aspx#Installing_the_Roles_and_Features_for_SharePoint_2013_on_Windows_Server_2012_Offline_with_PowerShell and http://blogs.msdn.com/b/uksharepoint/archive/2013/03/18/scripted-installation-of-sharepoint-2013-and-office-web-apps-server-from-the-field-part-2.aspx.

Since this is again a quite lengthly script we will split it up in steps.

Step A: enabling IIS and other features

This step enables a whole load of features on Windows that are required by SharePoint, including IIS. If a restart is required, the script will reboot after the setup of the features. Some times your machine might reboot more than once to complete the setup of all these features.

Write-Log -Verbose  "Step 6: Install SharePoint"
Import-Module ServerManager
#Add .Net 4.5 features
Add-WindowsFeature NET-WCF-HTTP-Activation45,NET-WCF-TCP-Activation45,NET-WCF-Pipe-Activation45
#Add the rest of the needed features for IIS role
$result = Add-WindowsFeature Net-Framework-Features,Web-Server,Web-WebServer,Web-Common-Http,Web-Static-Content,Web-Default-Doc,Web-Dir-Browsing,Web-Http-Errors,Web-App-Dev,Web-Asp-Net,Web-Net-Ext,Web-ISAPI-Ext,Web-ISAPI-Filter,Web-Health,Web-Http-Logging,Web-Log-Libraries,Web-Request-Monitor,Web-Http-Tracing,Web-Security,Web-Basic-Auth,Web-Windows-Auth,Web-Filtering,Web-Digest-Auth,Web-Performance,Web-Stat-Compression,Web-Dyn-Compression,Web-Mgmt-Tools,Web-Mgmt-Console,Web-Mgmt-Compat,Web-Metabase,Application-Server,AS-Web-Support,AS-TCP-Port-Sharing,AS-WAS-Support, AS-HTTP-Activation,AS-TCP-Activation,AS-Named-Pipes,AS-Net-Framework,WAS,WAS-Process-Model,WAS-NET-Environment,WAS-Config-APIs,Web-Lgcy-Scripting,Windows-Identity-Foundation,Server-Media-Foundation,Xps-Viewer
if($result.RestartNeeded -eq "Yes")
{
    Set-Restart-AndResume $global:script "7"
}

Write-Log -Verbose "Step 6: Install SharePoint"

Import-Module ServerManager

#Add .Net 4.5 features

Add-WindowsFeature NET-WCF-HTTP-Activation45,NET-WCF-TCP-Activation45,NET-WCF-Pipe-Activation45

#Add the rest of the needed features for IIS role

$result = Add-WindowsFeature Net-Framework-Features,Web-Server,Web-WebServer,Web-Common-Http,Web-Static-Content,Web-Default-Doc,Web-Dir-Browsing,Web-Http-Errors,Web-App-Dev,Web-Asp-Net,Web-Net-Ext,Web-ISAPI-Ext,Web-ISAPI-Filter,Web-Health,Web-Http-Logging,Web-Log-Libraries,Web-Request-Monitor,Web-Http-Tracing,Web-Security,Web-Basic-Auth,Web-Windows-Auth,Web-Filtering,Web-Digest-Auth,Web-Performance,Web-Stat-Compression,Web-Dyn-Compression,Web-Mgmt-Tools,Web-Mgmt-Console,Web-Mgmt-Compat,Web-Metabase,Application-Server,AS-Web-Support,AS-TCP-Port-Sharing,AS-WAS-Support, AS-HTTP-Activation,AS-TCP-Activation,AS-Named-Pipes,AS-Net-Framework,WAS,WAS-Process-Model,WAS-NET-Environment,WAS-Config-APIs,Web-Lgcy-Scripting,Windows-Identity-Foundation,Server-Media-Foundation,Xps-Viewer

if($result.RestartNeeded -eq "Yes")

{

Set-Restart-AndResume $global:script "7"

}

Step B: Installing SharePoint Prerequisites

SharePoint itself has a number of prerequisites; in this still we will install them all.

#Mount SharePoint iso
$mountresult = Mount-DiskImage -ImagePath $global:pathToSharePointISO -PassThru
$driveLetter = ($mountresult | Get-Volume).DriveLetter
Write-Log -Verbose  "Installing SharePoint PreReqs...."
$setupFile = $driveLetter+":\prerequisiteinstaller.exe"
$process = Start-Process $setupFile -PassThru -Wait -ArgumentList "/unattended /SQLNCli:$global:SharePoint2013Path\PrerequisiteInstallerFiles\sqlncli.msi /IDFX:$global:SharePoint2013Path\PrerequisiteInstallerFiles\Windows6.1-KB974405-x64.msu /IDFX11:$global:SharePoint2013Path\PrerequisiteInstallerFiles\MicrosoftIdentityExtensions-64.msi /Sync:$global:SharePoint2013Path\PrerequisiteInstallerFiles\Synchronization.msi /AppFabric:$global:SharePoint2013Path\PrerequisiteInstallerFiles\WindowsServerAppFabricSetup_x64.exe /KB2671763:$global:SharePoint2013Path\PrerequisiteInstallerFiles\AppFabric1.1-RTM-KB2671763-x64-ENU.exe /MSIPCClient:$global:SharePoint2013Path\PrerequisiteInstallerFiles\setup_msipc_x64.msi /WCFDataServices:$global:SharePoint2013Path\PrerequisiteInstallerFiles\WcfDataServices.exe"

#Mount SharePoint iso

$mountresult = Mount-DiskImage -ImagePath $global:pathToSharePointISO -PassThru

$driveLetter = ($mountresult | Get-Volume).DriveLetter

Write-Log -Verbose "Installing SharePoint PreReqs...."

$setupFile = $driveLetter+":\prerequisiteinstaller.exe"

$process = Start-Process $setupFile -PassThru -Wait -ArgumentList "/unattended /SQLNCli:$global:SharePoint2013Path\PrerequisiteInstallerFiles\sqlncli.msi /IDFX:$global:SharePoint2013Path\PrerequisiteInstallerFiles\Windows6.1-KB974405-x64.msu /IDFX11:$global:SharePoint2013Path\PrerequisiteInstallerFiles\MicrosoftIdentityExtensions-64.msi /Sync:$global:SharePoint2013Path\PrerequisiteInstallerFiles\Synchronization.msi /AppFabric:$global:SharePoint2013Path\PrerequisiteInstallerFiles\WindowsServerAppFabricSetup_x64.exe /KB2671763:$global:SharePoint2013Path\PrerequisiteInstallerFiles\AppFabric1.1-RTM-KB2671763-x64-ENU.exe /MSIPCClient:$global:SharePoint2013Path\PrerequisiteInstallerFiles\setup_msipc_x64.msi /WCFDataServices:$global:SharePoint2013Path\PrerequisiteInstallerFiles\WcfDataServices.exe"

Step C: Installing SharePoint

SharePoint is installed in this step from the ISO that the script mounts.

if($process.ExitCode -eq 0) {
        #install sharepoint
        Write-Log -Verbose  "Installing SharePoint...."
        $path = $driveLetter+":\Setup.exe"
        $sharePointInstallProcess = Start-Process -Wait -PassThru $path -ArgumentList "/config $global:SharePoint2013Path\FarmSilentConfig.xml"
        switch($sharePointInstallProcess.ExitCode)
        {
            0 {
                Write-Log -Verbose  "SharePoint successfully installed"
                Write-Log -Verbose  "SharePoint Installed"
                if ($global:DoAllTasks) {
                    Set-Restart-AndResume $global:script "8"
                    }
            }
            default{
                Write-Log -Verbose  "An error has occured in installing SharePoint. Code: " $sharePointInstallProcess.ExitCode
            }
        }
    }
    else {
        if($process.ExitCode -eq 3010) {
            Write-Log -Verbose  "SharePoint prereqs install requires a reboot"
        }
        else {
            Write-Log -Verbose  "SharePoint prereqs not succesfully installed, please investigate.  Code: " $process.ExitCode
        }
    }

if($process.ExitCode -eq 0) {

#install sharepoint

Write-Log -Verbose "Installing SharePoint...."

$path = $driveLetter+":\Setup.exe"

$sharePointInstallProcess = Start-Process -Wait -PassThru $path -ArgumentList "/config $global:SharePoint2013Path\FarmSilentConfig.xml"

switch($sharePointInstallProcess.ExitCode)

{

0 {

Write-Log -Verbose "SharePoint successfully installed"

Write-Log -Verbose "SharePoint Installed"

if ($global:DoAllTasks) {

Set-Restart-AndResume $global:script "8"

}

default{

Write-Log -Verbose "An error has occured in installing SharePoint. Code: " $sharePointInstallProcess.ExitCode

}

else {

if($process.ExitCode -eq 3010) {

Write-Log -Verbose "SharePoint prereqs install requires a reboot"

}

else {

Write-Log -Verbose "SharePoint prereqs not succesfully installed, please investigate. Code: " $process.ExitCode

}

Step D: Cleaning up

This step simply unmounts the SharePoint installation media.

Dismount-DiskImage -ImagePath $global:pathToSharePointISO

1	Dismount-DiskImage -ImagePath $global:pathToSharePointISO

Pff, are we done yet? No! Next up: Installing PowerPivot for SharePoint.

Category Archives

Passing command line settings to SQL Server Integration Services (SSIS) packages using dtexec on Linux

Like this:

Farewell Tour

Like this:

SQL Server 2017: It’s here!

Like this:

SQL Server news from connect start to resonate

Like this:

Power BI Refresh scenarios against data storage solutions explained

Like this:

SQL Server op Linux? Ja dus!

Like this:

Automatically building a Microsoft BI machine using PowerShell – Final post (post #14)

Like this:

Automatically building a Microsoft BI machine using PowerShell – Configuring PowerPivot (post #13)

Like this:

Automatically building a Microsoft BI machine using PowerShell – Installing PowerPivot for SharePoint (post #12)

Like this:

Automatically building a Microsoft BI machine using PowerShell – Installing SharePoint (post #11)

Like this:

Category Archives

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: