SQL Server 2016 using SQL Authentication deploy SSIS package in MSDB by using powershell

Question

I am using the following code to deploy the SSIS package using windows authentication in SQL Server 2016. When I tried to run it under SQL Authentication it always gives the following error. And the user account which I am trying to use having sysadmin privileges.

Script using windows authentication. In this code I modified connection string explain in this link https://learn.microsoft.com/en-us/sql/integration-services/ssis-quickstart-deploy-powershell?view=sql-server-ver15 for SQL authentication.

 [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null

    #this allows the debug messages to be shown
    $DebugPreference = "Continue"

    # Retrieves a 2012 Integration Services CatalogFolder object
    # Creates one if not found
    Function Get-CatalogFolder
    {
        param
        (
            [string] $folderName
        ,   [string] $folderDescription
        ,   [string] $serverName = "localhost\dev2012"
        )

        $connectionString = [String]::Format("Data Source={0};Initial Catalog=msdb;Integrated Security=SSPI;", $serverName)

        $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)

        $integrationServices = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices($connection)
        # The one, the only SSISDB catalog
        $catalog = $integrationServices.Catalogs["SSISDB"]

        $catalogFolder = $catalog.Folders[$folderName]

        if (-not $catalogFolder)
        {
            Write-Debug([System.string]::Format("Creating folder {0}", $folderName))
            $catalogFolder = New-Object Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder($catalog, $folderName, $folderDescription)
            $catalogFolder.Create()
        }

        return $catalogFolder
    }

    # Deploy an ispac file into the SSISDB catalog
    Function Deploy-Project
    {
        param
        (
            [string] $projectPath
        ,   [string] $projectName
        ,   $catalogFolder
        )

        # test to ensure file exists
        if (-not $projectPath -or  -not (Test-Path $projectPath))
        {
            Write-Debug("File not found $projectPath")
            return
        }

        Write-Debug($catalogFolder.Name)
        Write-Debug("Deploying $projectPath")

        # read the data into a byte array
        [byte[]] $projectStream = [System.IO.File]::ReadAllBytes($projectPath)

        # $ProjectName MUST match the value in the .ispac file
        # else you will see 
        # Failed to deploy the project. Fix the problems and try again later.:The specified project name, test, does not match the project name in the deployment file.
        $projectName = "sample"

        $project = $catalogFolder.DeployProject($projectName, $projectStream)
    }

Error Message

Exception calling "Create" with "0" argument(s): "Operation 'Create' on object 'CatalogFolder[@Name='TestFolder']'
failed during execution."
At SCRIPT4.PS1:31 char:9
+         $catalogFolder.Create()
+         ~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SfcCRUDOperationFailedException


Exception calling "DeployProject" with "2" argument(s): "The operation cannot be started by an account that uses SQL
Server Authentication. Start the operation with an account that uses Windows Authentication."
  SCRIPT4.PS1:65 char:5
+     $project = $catalogFolder.DeployProject($projectName, $projectStr ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException

So in summary

In SQL Server 2016 using SQL Authentication deploy SSIS package in MSDB by using powershell is possible ??

That's why I don't understand your post. The exception message clearly answers your question. — Gert Arnold, Nov 15 '19 at 10:54
ok, but in the link I reference Say(from docs.microsoft) , SQL Authentication work. So got bit confused. Attaching again here , if you missed https://learn.microsoft.com/en-us/sql/integration-services/ssis-quickstart-deploy-powershell?view=sql-server-ver15 — Vipin, Nov 15 '19 at 12:47

score -1 · Answer 1 · answered Oct 18 '19 at 12:24

-1

Change your connection string to

Data Source={0};Initial Catalog=msdb;uid=sqlUser;pwd=sqlPassword;

answered Oct 18 '19 at 12:24

Goose

546
3
7

This did not work for me. i tried already initial catalog=master or initial catalog=ssisdb or initial catalog=msdb. Same error will be coming. Just for you information i am doing it in onpermise sql server. Not on cloud – Vipin Oct 21 '19 at 03:49

SQL Server 2016 using SQL Authentication deploy SSIS package in MSDB by using powershell

1 Answers1