• SQL Server

    Powershell Create of the SSISDB Catalog

    So when management gets this [insert explicative] idea to strip away my MSDN enterprise access, and I need to create an SSISDB catalog on my dev server- what the hell am I to do? Well, maybe not simple… I put on my PowerShell hack through hat. Shall we?

    In your favorite SQL instance (we’ll just use localhost for my examples) start with making sure you have CLR ENABLED first:

    EXEC sp_configure 'clr enabled', 1;  
    RECONFIGURE;  
    GO

    Do this in a query window in SSMS of course.

    Now the PowerShell:

    # Load the IntegrationServices Assembly  
    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")  
    
    # Store the IntegrationServices Assembly namespace to avoid typing it every time  
    $ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"  
    
    Write-Host "Connecting to server ..."  
    
    # Create a connection to the server  
    $sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"  
    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString  
    
    # Create the Integration Services object  
    $integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection  
    
    # Provision a new SSIS Catalog  
    $catalog = New-Object $ISNamespace".Catalog" ($integrationServices, "SSISDB", "P@assword1")  
    $catalog.Create()

    The last part in “Provision a new SSIS Catalog” make sure you do a good password.

    And that’s it…. So why am I doing this with PowerShell? No “DIS” on PowerShell, but it seems that the version I install SQL Server 2016 has this thing about connecting to Azure SSIS with creating a SSISDB Catalog, and since I no longer have access to MSDN (you knew MSDN was in this somewhere) I had to hack my way around getting a catalog created… Now yes, I could have done the Azure SQL SSISDB connect bah bah (I do have an enterprise Azure accounts), but you what? This same management does not want to spend money on Azure… YA! An Azure SQL SSISDB has a cost.

    So there you go… Got to love this jumping around not having the tools to get stuff done.

    ~ScottGeek

     

     

     

     

  • Microsoft

    Powershell 6.x Profiles

    Because I can never remember this:

    All Users, All Hosts$PsHome\Profile.ps1
    All Users, Current Host$PsHome\Microsoft.PowerShell_profile.ps1
    Current User, All Hosts$Home\Documents\PowerShell\Profile.ps1
    Current user, Current Host$Home\Documents\PowerShell\Microsoft.PowerShell_profile.ps
    $ProfileCurrent User, Current Host
    $Profile.CurrentUserCurrentHostCurrent User, Current Host (Yeah the same as above)
    $Profile.CurrentUserAllHostsCurrent User, All Hosts
    $Profile.AllUsersCurrentHostAll Users, Current Host
    $Profile.AllUsersAllHostsAll Users, All Hosts
  • Microsoft

    Powershell 6 Core Like Install

    As I re-tool (or is it update) my Powershell, I came across the simple one liner to do the install… nice:

    iex "& { $(irm https://aka.ms/install-powershell.ps1) } -UseMSI"

    Btw some neat changes in 6.2 PS…

    Some interesting settings:

    Enable-ExperimentalFeature -Name PSCommandNotFoundSuggestion
    
    Enable-ExperimentalFeature -Name PSTempDrive
    
    Enable-ExperimentalFeature -Name PSUseAbbreviationExpansion

    Read More:

    What’s New in PowerShell Core 6.2

    What’s New in PowerShell Core 6.2

    New features and changes released in PowerShell Core 6.2

    Source: docs.microsoft.com/en-us/powershell/scripting/whats-new/what-s-new-in-powershell-core-62?view=powershell-6