rtumaykin / ssis-build Goto Github PK

Hi, I have made the changes specified in issue #25 but am still getting the same error in VS 2019. Can you suggest a fix for this please?

The @project.manifest file, adds DB connection information upon creation of a connection manager but doesn't get updated to the latest connection settings that have been applied afterwards, after a successful build.

Not sure if this limited to SSIS or if this can be fixed at your end. This doesn't work even in Visual Studio build through devenv

@rtumaykin @fmms

How do I make sure that the passwords that have been marked as sensitive in the ETL during development at project level, are not exposed during build?

When we build a SSIS package through "SSISBuild" in PowerShell through it's PowerShell module New-SsisDeploymentPackage, we see that the project level sensitive passwords are exposed into plain text during the build, thereby rendering the password secrecy useless.
The ETL is protected with EncryptSensitiveWithPassword.

Comment please!! @rtumaykin @fmms

this is the error when I try to build a SSIS 2017 project with EncryptSensitiveWithPassword Protection Level:

PS C:\Users\Administrator> . $ssisbuild $prj -Configuration "Development" -Password "aaa"
SSIS Build Engine
Copyright (c) 2017 Roman Tumaykin

Executing SSIS Build with the following arguments:
Project File: c:\Users\administrator\source\repos\SSIS\SSIS\SSIS.dtproj
-Password: (hidden)
-Configuration: Development

Project parameters:
Starting build. Loading project files from c:\Users\administrator\source\repos\SSIS\SSIS\SSIS.dtproj.
ERROR: Specified initialization vector (IV) does not match the block size for this algorithm.

I am trying to do a basic command to test the package.

I am trying to run the following command:

New-SsisDeploymentPackage .\SampleSSISProject\SampleSSISProject.dtproj -OutputFolder ".\build" -Configuration "Development"

But I get the following error:

New-SsisDeploymentPackage : Value cannot be null.
Parameter name: source
At line:1 char:1
+ New-SsisDeploymentPackage .\SampleSSISProject\SampleSSISProject.dtpro ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [New-SsisDeploymentPackage], ArgumentNullException
    + FullyQualifiedErrorId : System.ArgumentNullException,SsisBuild.Core.Builder.SsisBuildPowershell

Assume we have expression to property:
<property dataType="System.String" expressionType="Notify" name="password" Sensitive="1"></property>

When i am building my project with ssisbuild, this expression is removed. Possible place where this is happen:

But this behaviour is different what VisualStudio does! Visual studio didn't remove expressions to sensitive properties even with protectionLevel = DontSaveSensitive. So this package became broken, and when you try to use it on SSISDB, you package will not assign this parameter.

When using the ssisdeploy.exe tool, I have one particular project (SSIS 2016, built using VS2017) where non-required parameters change into required parameters when deploying to any environment.

• In development (VS2017) the parameters are non-required

• When I import the affected ispac into VS, the parameters show up as non-required
  

• When I do a manual deployment (or via isdeploymentwizard) the parameters are non-required
  

• As soon as I deploy using ssisdeploy, the parameters are suddenly "required"

I can provide the ispac-file with which this happens, but as it's an in-company project, I'd rather not post it publicly in Github :-).

When attempting to install the SSISBuild package direct from NuGet.org I encountered an error via the PowerShell command Install-Package

The package filed to install due to a file "already existing" specifically 'Microsoft.Management.Infrastructure.dll'

While interrogating the NuGet Package itself that was pulled from the NuGet.org site I can see that this file lives in the 'tools' directory.

The same is true of the file 'System.Management.Automation.dll'

When trying to run the example, I ran into the .user file issue. I "fixed" that by opening the file in VS and building it once there, though that really shouldn't throw an error if it's not present or should be included in the example if it's necessary.

However, even after fixing that, I get the following error when running build.ps1 using the given parameters:
Specified initialization vector (IV) does not match the block size for this algorithm.

That shows up in the New-SSISDeploymentPackage line (37) in the build script.

From what I can see that seems to be because the expected string length doesn't match that actually passed through, but not sure how to go about adjusting that for the example.

After deploying to server, non-unicode text parameters of packages, became unreadable.

This week the DevOps release pipline, simple Nuget task that installs SSISBuild now fails with "bad signature". Totally lost since only occurs on 1 of 3 servers.
Has anyone else run across this?

(node:9248) UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 1): Error: write EPROTO :error:04091068:rsa routines:INT_RSA_VERIFY:bad signature:openssl\crypto\rsa\rsa_sign.c:278:
:error:1408D07B:SSL routines:ssl3_get_key_exchange:bad signature:openssl\ssl\s3_clnt.c:2032:

On one machine the .ispac from a project is built ok. On another (and similar) machine I get "ERROR: Invalid configuration name: Development" with the same project and same command.

I can build packages like this through the VS IDE without needing to populate "correct" values for the parameters. I should be able to build packages through the tool. I noticed that this wasn't a valid option for the encryption settings on build, though.

In package, I set protection level different, then in project.
There is error during build in Visual Studio 2015:
------ Build started: Project: SendMail (SQL Server 2014), Configuration: Development ------
Build started: SQL Server Integration Services project: Incremental ...
Starting project consistency check ...
Error : Project consistency check failed. The following inconsistencies were detected:
Mail.dtsx has a different ProtectionLevel than the project.
Build complete -- 1 errors, 0 warnings
========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped ==========

But if I build using ssis-build, everything is OK, build successful.

When trying to issue a build through Azure Devops Pipeline I get this error message:

When building from Visual Studio everything works fine, all the files are the same.

This is the powershell script in the pipeline:

Get-ChildItem -Filter *.dtproj -Recurse | Where-Object { -Not ($_.FullName -match "obj") } | ForEach-Object { &"$($env:BUILD_SOURCESDIRECTORY)\SSISBuild.2.1.2\tools\ssisbuild.exe" $_.FullName -Configuration $($env:TestConfig) -ProtectionLevel DontSaveSensitive if ($LASTEXITCODE -ne 0) { throw "Build failed."; } }

Hi Roman,

first of all: thanks for taking the time for writing this wonderful tool.

While giving it a test drive and comparing against the outputs of the Visual Studio Build, I noticed that the dtproj.user file is required - even if there are no parameters to be loaded from it.

Would it be possible to make this a non-mandatory file?

Thanks,
Peter

Hi Roman,

While giving this tool a test drive and comparing against the outputs of the Visual Studio Build, I noticed that the @Project.manifest file versioning differs from that provided by the Visual Studio build.

For each .dtsx file in the @Project.manifest - the original tool enters the following into the parameters:
<SSIS:Property SSIS:Name="VersionMajor">Always One (1)</SSIS:Property>
<SSIS:Property SSIS:Name="VersionMinor">Always Zero (0)</SSIS:Property>
<SSIS:Property SSIS:Name="VersionBuild">DTS:VersionBuild from the DTSX file</SSIS:Property>
<SSIS:Property SSIS:Name="VersionComments">
</SSIS:Property>
<SSIS:Property SSIS:Name="VersionGUID">DTS:VersionGUID from the DTSX file</SSIS:Property>

The parent (first) Versions on the Project leve in the @Project.manifest file remain with zeros in every parameter (this already works as expected):
<SSIS:Property SSIS:Name="VersionMajor">0</SSIS:Property>
<SSIS:Property SSIS:Name="VersionMinor">0</SSIS:Property>
<SSIS:Property SSIS:Name="VersionBuild">0</SSIS:Property>
<SSIS:Property SSIS:Name="VersionComments">
</SSIS:Property>

Would it be possible to make this behave in this way - to align the behavior with the Visual Studio 2015 (SSDT 17.2 build number: 14.0.61707.300 - latest version at the time of writing)?

Thanks,
Peter

SSISBuild downloaded with nuget command

nuget install SSISBuild -ExcludeVersion

command:

ssisbuild.exe StageSSIS.dtproj -Configuration Dev1-SQL -ProtectionLevel DontSaveSensitive

results:

SSIS Build Engine
Copyright (c) 2017 Roman Tumaykin

Executing SSIS Build with the following arguments:
Project File: C:\git\BusinessIntelligenceProjects\StageSSIS\StageSSIS.dtproj
-ProtectionLevel: DontSaveSensitive
-Configuration: Dev1-SQL

Project parameters:
-------------------------------------------------------------------------------
Starting build. Loading project files from C:\git\BusinessIntelligenceProjects\StageSSIS\StageSSIS.dtproj.
ERROR: Failed to determine protection level. DTS:ProtectionLevel attribute was not found.. (Path: )

the project file does not have namespace prefix DTS:

<SSIS:Project SSIS:ProtectionLevel="EncryptSensitiveWithPassword" xmlns:SSIS="www.microsoft.com/SqlServer/SSIS">
...

Since rtumaykin is not maintaining this version,

should we perhaps start merging all the changes and submitting PRs to a Fork?

Cheers,
Peter

I'm exploring decrypting dtsx files using the same logic in the aforementioned method found below. I'm using pwsh.exe and I can only decrypt mostly successfully if I use [System.Security.Cryptography.PaddingMode]::ISO10126 otherwise I get StreamReader errors about padding ( e.g. Write-Error: Exception calling "ReadToEnd" with "0" argument(s): "Padding is invalid and cannot be removed." ).

Below is the script function I built that mostly mirrors the aforementioned method. I can only figure at this point either I have some weird old 2008/2012 dtsx file or I've got some incompatibility with .NET.

 [CmdletBinding()]
 param (
     [Parameter()]
     [string]
     $Path = '.\tripledes-cbc.dtsx',
     # Password
     [Parameter()]
     [securestring]
     $Password = (Read-Host -AsSecureString -Prompt 'Enter Password to Decrypt the dtsx file')
 )

$xml = [xml](Get-Content $Path -Raw)
$encDt = $xml.EncryptedData
$p = [pscredential]::new( 'Dummy' , $Password).GetNetworkCredential().Password
$salt = [System.Convert]::FromBase64String( $encDt.Salt )
$iv = [System.Convert]::FromBase64String( $encDt.IV )
$cipDt = [System.Convert]::FromBase64String( $encDt.CipherData.CipherValue )


try {
    $tdes = [System.Security.Cryptography.TripleDES]::Create()
    $tdes.IV = $iv
    $tdes.Padding = [System.Security.Cryptography.PaddingMode]::ISO10126
    $tdes.Mode = [System.Security.Cryptography.CipherMode]::CBC
    $pdb = [System.Security.Cryptography.PasswordDeriveBytes]::new( $p , $salt)
    $tdes.Key = $pdb.CryptDeriveKey('TripleDES' , 'SHA1', 192 , $tdes.IV )
    $memStrm = New-Object System.IO.MemoryStream -ArgumentList (,$cipDt)
    $cryStrm = New-Object System.Security.Cryptography.CryptoStream -ArgumentList @( 
        $memStrm
        $tdes.CreateDecryptor()
        [System.Security.Cryptography.CryptoStreamMode]::Read 
    )
    $strmRdr = New-Object System.IO.StreamReader -ArgumentList @( $cryStrm, [System.Text.Encoding]::UTF8 )
    $strmRdr.ReadToEnd()
}
catch {
    Write-Error $_
}
finally {
    if($aes){$aes.Dispose()}
    if($memStrm){$memStrm.Dispose()}
    if($cryStrm){$cryStrm.Dispose()}
    if($strmRdr){$strmRdr.Dispose()}
}

ServerInstanceUserID is a mandatory parameter (DeployArguments: line 63), and that precludes deployment with IntegratedSecurity (Deployer: line 96)