August 26, 2015 at 2:49 am
Hi Friends,
I am trying to execute a powershell script in ssis through execute process task.
[Execute Process Task] Error: In Executing "C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe" "C:\Users\unknown\Desktop\powershell script\ToShare\file_checksum_ps1.txt" at "", The process exit code was "1" while the expected was "0".
but I am getting the above error. Also screenshot has been attached. Please take a look at that.
What mistake am I doing?
any suggestions would be really appreciated
Thanks,
Charmer
August 26, 2015 at 3:48 am
Hi
Please try to set in "Arguments" something like that: -ExecutionPolicy ByPass -command ". 'D:\db_r\scripts\wipe.ps1'"and replace D:\db_r\scripts\wipe.ps1 with your path&filename.ps1
Regards
Mike
August 26, 2015 at 4:06 am
Hi Mike,
I am still getting the same issue. Well, I saved the powershell script in a text file. Does this need to be in .ps format? or could be any format? could that be an issue?
Thanks,
Charmer
August 26, 2015 at 4:26 am
Hi Charmer
Imho it will be better if you change the extension to ps1.
There is another issue with PowerShell and Ssis, when you call any Start-Process
in ps script, add -PassThru -Wait to force PowerShell to wait for a process to complete.
Regards
Mike
August 26, 2015 at 4:53 am
okay Mike,
tell me Where do I have to add that? let me try
Thanks,
Charmer
August 26, 2015 at 4:58 am
Small example from my ps script:
Start-Process -FilePath "C:\Program Files\7-Zip\7z.exe" -ArgumentList "-wG:\TEMP e dbcopy.tgz" -PassThru -Wait
Regards
Mike
August 26, 2015 at 5:43 am
Mike...thanks..it helped. executed successfully but not getting the output. when I tried running through ISE..it throws me this error....
cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for mor
e details.
for your reference, I have posted the ps1 script in here. and Mike FYI, I have no knowledge in power shell
param(
[Parameter(Position=0, Mandatory=$true)] [string]$FileName
)
Clear
function Get-FileHash {
<#
.SYNOPSIS
Calculates the hash on a given file based on the seleced hash algorithm.
.DESCRIPTION
Calculates the hash on a given file based on the seleced hash algorithm. Multiple hashing
algorithms can be used with this command.
.PARAMETER Path
File or files that will be scanned for hashes.
.PARAMETER Algorithm
The type of algorithm that will be used to determine the hash of a file or files.
Default hash algorithm used is SHA256. More then 1 algorithm type can be used.
Available hash algorithms:
MD5
SHA1
SHA256 (Default)
SHA384
SHA512
RIPEM160
.NOTES
Name: Get-FileHash
Author: Boe Prox
Created: 18 March 2013
Modified: 28 Jan 2014
1.1 - Fixed bug with incorrect hash when using multiple algorithms
.OUTPUTS
System.IO.FileInfo.Hash
.EXAMPLE
Get-FileHash -Path Test2.txt
Path SHA256
---- ------
C:\users\prox\desktop\TEST2.txt 5f8c58306e46b23ef45889494e991d6fc9244e5d78bc093f1712b0ce671acc15
Description
-----------
Displays the SHA256 hash for the text file.
.EXAMPLE
Get-FileHash -Path .\TEST2.txt -Algorithm MD5,SHA256,RIPEMD160 | Format-List
Path : C:\users\prox\desktop\TEST2.txt
MD5 : cb8e60205f5e8cae268af2b47a8e5a13
SHA256 : 5f8c58306e46b23ef45889494e991d6fc9244e5d78bc093f1712b0ce671acc15
RIPEMD160 : e64d1fa7b058e607319133b2aa4f69352a3fcbc3
Description
-----------
Displays MD5,SHA256 and RIPEMD160 hashes for the text file.
.EXAMPLE
Get-ChildItem -Filter *.exe | Get-FileHash -Algorithm MD5
Path MD5
---- ---
C:\users\prox\desktop\handle.exe 50c128c5b28237b3a01afbdf0e546245
C:\users\prox\desktop\PortQry.exe c6ac67f4076ca431acc575912c194245
C:\users\prox\desktop\procexp.exe b4caa7f3d726120e1b835d52fe358d3f
C:\users\prox\desktop\Procmon.exe 9c85f494132cc6027762d8ddf1dd5a12
C:\users\prox\desktop\PsExec.exe aeee996fd3484f28e5cd85fe26b6bdcd
C:\users\prox\desktop\pskill.exe b5891462c9ca5bddfe63d3bae3c14e0b
C:\users\prox\desktop\Tcpview.exe 485bc6763729511dcfd52ccb008f5c59
Description
-----------
Uses pipeline input from Get-ChildItem to get MD5 hashes of executables.
#>
[CmdletBinding()]
Param(
[Parameter(Position=0,Mandatory=$true, ValueFromPipelineByPropertyName=$true,ValueFromPipeline=$True)]
[Alias("PSPath","FullName")]
[string[]]$Path,
[Parameter(Position=1)]
[ValidateSet("MD5","SHA1","SHA256","SHA384","SHA512","RIPEMD160")]
[string[]]$Algorithm = "SHA256"
)
Process {
ForEach ($item in $Path) {
$item = (Resolve-Path $item).ProviderPath
If (-Not ([uri]$item).IsAbsoluteUri) {
Write-Verbose ("{0} is not a full path, using current directory: {1}" -f $item,$pwd)
$item = (Join-Path $pwd ($item -replace "\.\\",""))
}
If(Test-Path $item -Type Container) {
Write-Warning ("Cannot calculate hash for directory: {0}" -f $item)
Return
}
$object = New-Object PSObject -Property @{
Path = $item
}
#Open the Stream
$stream = ([IO.StreamReader]$item).BaseStream
foreach($Type in $Algorithm) {
[string]$hash = -join ([Security.Cryptography.HashAlgorithm]::Create( $Type ).ComputeHash( $stream ) |
ForEach { "{0:x2}" -f $_ })
$null = $stream.Seek(0,0)
#If multiple algorithms are used, then they will be added to existing object
$object = Add-Member -InputObject $Object -MemberType NoteProperty -Name $Type -Value $Hash -PassThru
}
$object.pstypenames.insert(0,'System.IO.FileInfo.Hash')
#Output an object with the hash, algorithm and path
Write-Output $object
#Close the stream
$stream.Close()
}
}
}
#Begin: Session Settings
asnp SqlServer* -ea 0
#End: Session Settings
$SQL_ServerName = '.'
#Construct an out-array to use for data export
$OutArray = @()
$RootDir = "C:\Users\unkonwn\Desktop\DB2Flat_CS_DateTime\"
$OutputFileName = "cbi_ssis_r4_0_8.csv"
#Loop over files in specified directory
$files = get-childitem $RootDir -recurse | Where-Object {$_.Name -match $FileName } #"Dim_Payer"} #where {! $_.PSIsContainer}
foreach ($file in $files)
{
# Create custom object
$myobj = "" | SELECT "FullName","LastWriteTime","Length","SHA512"
# Fill the object
$FileNameWithPath = $file.FullName
$myobj.FullName = $FileNameWithPath
$LastWriteTime = $file.LastWriteTime
$myobj.LastWriteTime = $LastWriteTime
$FileSize = $file.Length
$myobj.Length = $FileSize
$HashValue = Get-FileHash -Path $file.FullName -Algorithm SHA512 | select-object SHA512
$myobj.SHA512 = $HashValue #Get-FileHash -Path $file.FullName -Algorithm SHA512 | select-object SHA512
$DynamicSQL = "INSERT INTO dbo.PackageHashValue(FullName,LastWriteTime,FileSize,FileCheckSumValue,CollectedOn) VALUES('$FileNameWithPath','$LastWriteTime',$FileSize,'$HashValue',GETDATE())"
write-host $DynamicSQL
Invoke-Sqlcmd -OutputSqlErrors 1 -serverinstance "$SQL_ServerName" -database "SandBox" -Query $DynamicSQL
#Add the object to the out-array
$outarray += $myobj
#Wipe the object just to be sure
$myobj = $null
}
#After the loop, export the array to CSV
$outarray | export-csv -notypeinformation -path $OutputFileName
Thanks,
Charmer
August 26, 2015 at 6:30 am
Don't worry, I'm also not a Master Blaster with PowerShell, I used trivial batches with Process Task over a decade,
I just started using PS this year.
To run PowerShell scripts in ISE, you must first set the execution policy to Unrestricted because by default it’s set to Restricted.
Type in console:Set-ExecutionPolicy Unrestricted
and then run you script.
Regards
Mike
August 26, 2015 at 9:40 pm
Mike, I executed the script and it asked me YES or SUSPEND and I said YES. but it says access is denied.
For your reference, screens hot is attached. please take a look at that, Mike. pls advise me on this.
Thanks,
Charmer
August 27, 2015 at 1:21 am
Hi
Please do the right click on Powershell shortcut and choose 'Run as Administrator'.
Regards
Mike
August 27, 2015 at 3:51 am
Mike, I think my Network team has restricted the access of running PS . Even after I use admin account to run the PS, I am not able to do it.
Thanks,
Charmer
August 28, 2015 at 6:50 am
Charmer, You can do that with For Each Loop Container & Script Task instead of Process Task.
1. Add Each Loop Container:
- set Foreach File Enumerator in Collection
- add Expression for FileSpec "*"+ @[User::VarFileName]+"*.*"
- set "C:\Users\unkonwn\Desktop\DB2Flat_CS_DateTime\" as Folder, choose "Fully qualified" radio box and "Traverse subfolders" check box
- declare another string variable to pass parameter to Task Script (eg. VarRetFile) and put it in Variable Mappings
2. Drag&Drop Scrip Task into Each Loop Container (C# example):
- add namespaces: System.IO, System.Security.Cryptography and System.Text.
- retrieve file properites
String FName = Dts.Variables["VarRetFile"].Value.ToString();
FileInfo f = new FileInfo(FName); //File name and path
DateTime FCre = File.GetCreationTime(FName); //Write time
long FLen = f.Length; //File length
-hashing file (SHA512)
String Hashed = "";
SHA512Managed hashString = new SHA512Managed();
byte[] hashValue;
FileStream fs = File.Open(FName,FileMode.Open, FileAccess.Read, FileShare.None);
fs.Position = 0;
hashValue = hashString.ComputeHash(fs);
StringBuilder hex = new StringBuilder(hashValue.Length * 2);
foreach (byte x in hashValue)
{
Hashed += String.Format("{0:x2}", x);
}
fs.Close();
And you can easly insert it into DB and output file.
Best regards
Mike
August 28, 2015 at 7:11 am
Hi Charmer I had posted a link in your post on how to loop through files, It answers all these question , take a look.
http://www.enabledbusiness.com/blog look at the last two links it answers your question verbatim
August 30, 2015 at 9:36 pm
Thank you Mike, I will try this one.
But Mike , about the FileSpec Expression, Can you tell me how does it work? Just curious.
Thanks,
Charmer
August 30, 2015 at 10:40 pm
Thank you Mike, your coding has helped me to get things done. Actually why am I generating hash value is to check whether any changes has been made in the file or not. So if no changes, then no need to process the file's info. That's what I have been trying to do. You helped me. Thanks a lot.
Thanks,
Charmer
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply