0

I have to implement a solution where I have to deploy a SSIS project (xy.ispac) from one machine to another. So far I've managed to copy-cut-paste the following stuff from all around the internet:

# Variables
$ServerName = "target"
$SSISCatalog = "SSISDB" # sort of constant
$CatalogPwd = "catalog_password"

$ProjectFilePath = "D:\Projects_to_depoly\Project_1.ispac"

$ProjectName = "Project_name"
$FolderName = "Data_collector"

# 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=$ServerName;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

$integrationServices = New-Object "$ISNamespace.IntegrationServices" $sqlConnection

$catalog = $integrationServices.Catalogs[$SSISCatalog]

# Create the Integration Services object if it does not exist
if (!$catalog) {
    # Provision a new SSIS Catalog
    Write-Host "Creating SSIS Catalog ..."
    $catalog = New-Object "$ISNamespace.Catalog" ($integrationServices, $SSISCatalog, $CatalogPwd)
    $catalog.Create()
}

$folder = $catalog.Folders[$FolderName]

if (!$folder)
{
    #Create a folder in SSISDB
    Write-Host "Creating Folder ..."
    $folder = New-Object "$ISNamespace.CatalogFolder" ($catalog, $FolderName, $FolderName)            
    $folder.Create()  
}

# Read the project file, and deploy it to the folder
Write-Host "Deploying Project ..."
[byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
$folder.DeployProject($ProjectName, $projectFile)

This seemed to be working surprisingly well on the development machine - test server pair. However, the live environment will be a bit different, the machine doing the deployment job (deployment server, or DS from now on) and the SQL Server (DB for short) the project is to be deployed are in different domains and since SSIS requires windows authentication, I'm going to need to run the above code locally on DS but using credentials of a user on the DB.

And that's the point where I fail. The only thing that worked is to start the Powershell command line interface using runas /netonly /user:thatdomain\anuserthere powershell, enter the password, and paste the script unaltered into it. Alas, this is not an option, since there's no way to pass the password to runas (at least once with /savecred) and user interactivity is not possible anyway (the whole thing has to be automated).

I've tried the following:

  • Simply unning the script on DS, the line $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString would use the credentials from DS which is not recognized by DB, and New-Object does not have a -Credential arg that I could pass to
  • Putting everything into an Invoke-Command with -Credential requires using -Computername as well. I guess it would be possible to use the local as 'remote' (using . as Computername) but it still complains about access being denied. I'm scanning through about_Remote_Troubleshooting, so far without any success.

Any hints on how to overcome this issue?

6
  • Which step it is failing? Is it failing while connecting remote target machine ISCatalog or it is failing while Deploy project? Commented Sep 26, 2016 at 15:13
  • It fails at creating the integration services object from the sql connection: $integrationServices = New-Object "$ISNamespace.IntegrationServices" $sqlConnection. Commented Sep 26, 2016 at 19:35
  • From this server can you try executing to different instance(Server) of SSIS Catalog? Looks like some access issue... Commented Sep 26, 2016 at 19:37
  • I cannot, there're no other instances available. But I guess, I'm slowly making progress. While it's the mentioned line that throws error message, the reason for that is actually the previous line where the sql connection is made from the connection string. Now, if I could convince it somehow to use the other domain\user credentials for creating that connection, I would be back on track. I guess. Commented Sep 27, 2016 at 9:20
  • I am guessing that the code is correct, but some access issue on the server, that is what i am trying to troubleshoot.. Commented Sep 27, 2016 at 9:26

1 Answer 1

0

A solution might be to use a sql user (with the right access rights) instead of an AD used. Something like this should work. (Check also the answer to correct the connection string)

Sign up to request clarification or add additional context in comments.

1 Comment

Not really. Deploying SSIS packages requires windows authentication. Using SQL user (login) is not possible here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.