Deploy SSIS with Powershell - Missing DMF

user_12814878 0
Viewed 1556

I'm trying to write a PowerShell script to deploy and SSIS project. Here is my code.

# Variables
$SSISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
$TargetServerName = "FBISQL003DV"
$TargetFolderName = "DEV"
$ProjectFilePath = "C:\Users\Brian.Ciampa\source\Workspaces\TFICM2\TFCIM\DEV\CIM\SSIS\BC_TEST\bin\Development\BC_TEST.ispac"
$ProjectName = "BC_TEST"

# Load the IntegrationServices assembly.  This is located at C:\windows\assembly.
$loadStatus = [System.Reflection.Assembly]::Load("Microsoft.SQLServer.Management.IntegrationServices, "+
    "Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL")

# Create a connection to the server
$sqlConnectionString = `
    "Data Source=" + $TargetServerName + ";Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

# Create the Integration Services object
$integrationServices = New-Object $SSISNamespace".IntegrationServices" $sqlConnection

# Get the Integration Services catalog
$catalog = $integrationServices.Catalogs["SSISDB"]

# Create the target folder
$folder = New-Object $SSISNamespace".CatalogFolder" ($catalog, $TargetFolderName,
    "Folder description")
$folder.Create()

Write-Host "Deploying " $ProjectName " project ..."

# Read the project file and deploy it
[byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
$folder.DeployProject($ProjectName, $projectFile)

Write-Host "Done."

However, I keep getting this message:

New-Object : Could not load file or assembly 'Microsoft.SqlServer.Dmf.Common, Version=13.0.0.0, Culture=neutral, 
PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
At C:\Users\Brian.Ciampa\desktop\SSIS_Deploy.ps1:20 char:24
+ ... nServices = New-Object $SSISNamespace".IntegrationServices" $sqlConne ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [New-Object], FileNotFoundException
    + FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.PowerShell.Commands.NewObjectCommand

The Microsoft.SqlServer.Dmf.dll and the Microsoft.SqlServer.Dmf.Common.dll files are located in the folder. What do I need to do?

Thanks! Brian

ssis powershell
2 Answers

Try loading the dll using Add-Type like so:

Add-Type -AssemblyName "Microsoft.SqlServer.Dmf.Common, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

It will check in the current directory for the dll, unless it's already been successfully loaded previously.

If you still get not found, the version of Microsoft.SqlServer.Dmf.Common.dll in the folder is not the dll needed by the IntegrationServices assembly.

To check the signature of the dll in the current folder, you can run:

([System.Reflection.Assembly]::LoadFile((Get-Item ".\Microsoft.SqlServer.Dmf.Common.dll").FullName)).FullName

Notes to help locate matching dlls.

12.0 = SQL Server 2014
13.0 = SQL Server 2016
14.0 = SQL Server 2017

Alternate Option:

In the case below, I'll initially load IntegrationServices "Version 14.0.0..." so that the dll doesn't need to be manually loaded from file or added to GAC.

C:\>attrib /s gacutil.exe
A            C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6.1 Tools\x64\gacutil.exe
A            C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6.1 Tools\gacutil.exe
A            C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\x64\gacutil.exe
A            C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\gacutil.exe
A            C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools\x64\gacutil.exe
A            C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools\gacutil.exe

C:\>cd "C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\x64"

C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\x64>gacutil /l | find "IntegrationServices,"
  Microsoft.SqlServer.Management.IntegrationServices, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL
  Microsoft.SqlServer.Management.IntegrationServices, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL
  Microsoft.SqlServer.Management.IntegrationServices, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL
  Microsoft.SqlServer.Management.IntegrationServices, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL

C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\x64>gacutil /l | find "Dmf.Common,"
  Microsoft.SqlServer.Dmf.Common, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL

C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\x64>gacutil /l | find "Dmf,"
  Microsoft.SqlServer.Dmf, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL
  Microsoft.SqlServer.Dmf, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL

C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\x64>
user_84083350

I had a similar issue with deploying an SSIS project with PowerShell. Eventually, I ended up deploying the project with a T-SQL script:

DECLARE @ProjectBinary as varbinary(max)
DECLARE @operation_id as bigint
Set @ProjectBinary = (SELECT * FROM OPENROWSET(BULK 'C:\Users\Brian.Ciampa\source\Workspaces\TFICM2\TFCIM\DEV\CIM\SSIS\BC_TEST\bin\Development\BC_TEST.ispac', SINGLE_BLOB) as BinaryData)    

Exec catalog.deploy_project @folder_name = 'DEV', @project_name = 'BC_TEST', @Project_Stream = @ProjectBinary, @operation_id = @operation_id out

Later I wrapped it into a stored procedure and triggered it with the Invoke-Sqlcmd cmdlet from PowerShell. Worked for me.

user_99030400
Related