locked
using powershell to automate SSIS package deployment RRS feed

  • Question

  • I have a script that gets all the SSIS packages in a SSIS directory like this (using a file that dtutil dumps too"

    $ExecDisplayString = """DTUTIL /FDI SQL;""""" + $SSISDir.Substring(1) +  """"" /SourceServer " + $ServerName + " > ..\..\..\..\remote.txt"""
    #dtutil /FDi SQL;"DataWarehouseLoad/Quality_Assurance/" /SourceS udwsqldev02
    write-host $ExecDisplayString 
    Invoke-Expression $ExecDisplayString
    [String]$outputfile =cmd /C "$ExecDisplayString"   
    write-host "display :" + $ExecDisplayString 	
    
    
    Get-Content ..\..\..\..\remote.txt | ForEach-Object{
        $splitUp = $_ -split "\s+"
        $package = $splitUp[4]
        $date = $splitUp[0]
        if($date.Length -lt 1){$date="no"}
        if(IsNumeric($date.Substring(0,1)))
        {
    
         $ExecDeleteString = """DTUTIL /SQL """"" + $SSISDir.Substring(1) + "/" + $package + """""  /QUIET /DELETE /SourceServer " + $ServerName + """"
         Invoke-Expression $ExecDeleteString
         [String]$outputfile =cmd /C "$ExecDeleteString"   
         #write-host "DELETE:" + $outputfile
        } 
    }
    

    however dumping this to a file ..\..\..\..\remote.txt is done to keep it out of the SVN filesystem Id like to be able to | pipe this cammand into my logic instead of using 

    Get-Content ..\..\..\..\remote.txt

    however when I try and pipe the dtutil command

    DTUTIL /FDI SQL;"DataWarehouseLoad/Quality_Assurance" /SourceServer UDWSQLDEV02 I get nothing . infact if I run it in the powershell script it is blank too. Any idea how aI use this output without a file

    here is what i am trying currently

    $ExecDisplayString = """DTUTIL /FDI SQL;""""" + $SSISDir.Substring(1) +  """"" /SourceServer " + $ServerName + "  2>&1  """
    /SourceS udwsqldev02
    write-host $ExecDisplayString 
    Invoke-Expression $ExecDisplayString
    
    [String]$outputfile =cmd /C "$ExecDisplayString"   | ForEach-Object{
        $splitUp = $_ -split "\s+"
        $package = $splitUp[4]
        $date = $splitUp[0]
        if($date.Length -lt 1){$date="no"}
        if(IsNumeric($date.Substring(0,1)))
        {
         $ExecDeleteString = """DTUTIL /SQL """"" + $SSISDir.Substring(1) + "/" + $package + """""  /QUIET /DELETE /SourceServer " + $ServerName + """"
         Invoke-Expression $ExecDeleteString
         [String]$outputfile =cmd /C "$ExecDeleteString"   
         #write-host "DELETE:" + $outputfile
        } 
    }

    and here is the entire script

    # ====================================================================================================
    #
    # ====================================================================================================
    param 
    (
      [string] $ServerName,
      [string] $SSISDir,
      [string] $IsEncrypted
    )
    
    
    #start of functions################################################################################
    function SSISCreateFolder ( [String]$CURFolder, [String]$CURFolder2)
    {
        write-host ""
        write-host "------ CREATE FOLDER " $CURFolder "------"
        write-host "---------------------------------------"
        $ExecString= "DTUTIL"
        
        $PR="/Q /SourceServer " + $ServerName + " /FE SQL;\" + $CURFolder
        write-host $PR
        [String]$outputfile =cmd /C "$ExecString $PR" 
        write-host $outputfile
    
        if($outputfile.Contains(“The specified folder does not exist”) )
        { 
            $PR="/SourceServer " + $ServerName + " /FC SQL;\" + $CURFolder2
            [String]$outputfile =cmd /C "$ExecString $PR" 
            write-host $outputfile
        }
     }
    
     #Is-Numeris added 9/20/2017
     function IsNumeric ($Value)
    {
        return $Value -match "^[\d\.]+$"
    }
    
    
    #end of functions################################################################################
    
    
    #add folder if it does not exist
    $SSISFolder = ""
    $SSISFolder2 = ""
    $SSISDir.split("/") | foreach {
        $curString = $_
        if ($curString -ne "")
        {
            if ($SSISFolder -ne "")
            {
                $SSISFolder = $SSISFolder + "\" + $curString
                $SSISFolder2 = $SSISFolder2 + ";" + $curString
                SSISCreateFolder $SSISFolder $SSISFolder2
                $SSISFolder2 = $SSISFolder
            }
            else
            {
                $SSISFolder = $SSISFolder + $curString
                $SSISFolder2 = $SSISFolder2 + $curString
                $TMPStr="\;"+$SSISFolder2
                SSISCreateFolder $SSISFolder $TMPStr
            }
            write-host "---->" + $SSISFolder2
            
        }        
    }
    
    
    
    
    #get list of dtsx packges from the file system 
    $files=get-childitem . *.dtsx|where-object {!($_.psiscontainer)}
    $PackagePass="ISA@SSIS.Package"
    
    ##############################################################################################################################
    $ExecDisplayString = """DTUTIL /FDI SQL;""""" + $SSISDir.Substring(1) +  """"" /SourceServer " + $ServerName + "  2>&1  """
    write-host $ExecDisplayString 
    Invoke-Expression $ExecDisplayString
    
    exit
    
    [String]$outputfile =cmd /C "$ExecDisplayString"   | ForEach-Object{
        $splitUp = $_ -split "\s+"
        $package = $splitUp[4]
        $date = $splitUp[0]
        if($date.Length -lt 1){$date="no"}
        if(IsNumeric($date.Substring(0,1)))
        {
         $ExecDeleteString = """DTUTIL /SQL """"" + $SSISDir.Substring(1) + "/" + $package + """""  /QUIET /DELETE /SourceServer " + $ServerName + """"
         Invoke-Expression $ExecDeleteString
         [String]$outputfile =cmd /C "$ExecDeleteString"   
         #write-host "DELETE:" + $outputfile
        } 
    }
    
    
    ###############################################################################################################################
    
    foreach ($file in $files) {
    	$filename = $file.ToString()
    	$DestName = $filename.Replace(".dtsx","")
    	$ExecString = """dtutil /FILE ./" + $file + " /DestServer " + $ServerName
    
    
        if ($IsEncrypted -eq "Y")
        {
            $ExecString = $ExecString + " /Dec " + $PackagePass
        }
    	$ExecString = $ExecString + " /QUIET /COPY SQL;" + $SSISDir +"/" + $DestName + """"	
    
    	Invoke-Expression $ExecString   
        [String]$outputfile =cmd /C "$ExecString" 
        #write-host "ADD:" + $outputfile 
    }
    
    

    Friday, September 22, 2017 3:33 PM

Answers

  • The output from dsutil is multiple lines.  Nothing is stripped out.  CMD utilities output mulilpe line like any other Windows command.

    Why are you casting to a string?  THat doesn't make any sense.

    $outputfile =cmd /C $ExecDisplayString

    Avoid quoting everything that does not move.


    \_(ツ)_/

    • Marked as answer by rm99 Wednesday, September 27, 2017 10:47 PM
    Wednesday, September 27, 2017 10:18 PM

All replies

  • Can you create a clear and simple question.  What is it you are trying to do.  We cannot provide deep consulting or review large scripts.  That is not within the scope of this forum.

    Try just asking a simple question.


    \_(ツ)_/

    Friday, September 22, 2017 8:32 PM
  • ok ill boil it down, 

    right now I run an external OS command and redirect the output using > to a file then read the file using GetContent:

    write-host $ExecDisplayString 
    Invoke-Expression $ExecDisplayString
    [String]$outputfile =cmd /C "$ExecDisplayString"   
    write-host "display :" + $ExecDisplayString 	
    
    
    Get-Content ..\..\..\..\remote.txt | ForEach-Object{
        $splitUp = $_ -split "\s+"
        $package = $splitUp[4]
        $date = $splitUp[0]

    what i would like to do is somehow pipe the output directly into the the powershell scripts   ForEach-Object{

    look I have and avoid having to read from a file. Is there a way to use the $outputfile string I have to read it lijne by line ? Get-Content doesnt work for that.

    • Proposed as answer by Yipper Wednesday, May 23, 2018 2:21 PM
    • Unproposed as answer by jrv Wednesday, May 23, 2018 3:02 PM
    Monday, September 25, 2017 2:05 PM
  • SSIS packages do not run in PowerShell.  They run under SQS.  You cannot get output.  PS command just triggers the package in SQS.

    Also: Get-Content reads a file line by line.  Enumerate the output to get the lines.


    \_(ツ)_/

    Monday, September 25, 2017 6:57 PM
  • Hi jrv

    here is what I find when i do this 

    [String]$outputfile =cmd /C "$ExecDisplayString"   the string has the output from the dtutil command ...but with all the CRLF stripped out so I cannot send it to the ForEach-Object 

    is there a way to get those CRLFs back that I do get when I redirect the commands output to a file in the OS?

    Wednesday, September 27, 2017 10:13 PM
  • The output from dsutil is multiple lines.  Nothing is stripped out.  CMD utilities output mulilpe line like any other Windows command.

    Why are you casting to a string?  THat doesn't make any sense.

    $outputfile =cmd /C $ExecDisplayString

    Avoid quoting everything that does not move.


    \_(ツ)_/

    • Marked as answer by rm99 Wednesday, September 27, 2017 10:47 PM
    Wednesday, September 27, 2017 10:18 PM
  • that helped but I needed to join the array and stuff in the crlf like this 

    $outputfile =cmd /C "$ExecDisplayString"  
    $outputfile = [string]::join("`n",$outputfile) 

    now that I have this string with CRLFs how do I feed it to a ForEach-Object ?

    I have tried:

    Get-Content $outputfile  | ForEach-Object{
        $splitUp = $_ -split "\s+"
        $package = $splitUp[4]
        $date = $splitUp[0]
        if($date.Length -lt 1){$date="no"}
        if(IsNumeric($date.Substring(0,1)))
        {

    and 

    $outputfile  | ForEach-Object{
        $splitUp = $_ -split "\s+"
        $package = $splitUp[4]
        $date = $splitUp[0]
        if($date.Length -lt 1){$date="no"}
        if(IsNumeric($date.Substring(0,1)))
        {



    • Edited by rm99 Wednesday, September 27, 2017 10:32 PM spelling2
    Wednesday, September 27, 2017 10:32 PM
  • ok this worked

    $outputfile =cmd /C "$ExecDisplayString"   

    foreach ($line in $outputfile) {
        $splitUp = $line -split "\s+"
        $package = $splitUp[4]
        $date = $splitUp[0]
        if($date.Length -lt 1){$date="no"}
        if(IsNumeric($date.Substring(0,1)))
        {

    Wednesday, September 27, 2017 10:47 PM