VBScript / PowerShell – Automating FTP / SFTP using WinSCP .Net Assembly COM Server

April 9, 2019

When you want to automate WinSCP using a VBScript it is not so straight forward ? Sad smile

First of all you can find the script here :

https://winscp.net/eng/docs/library_session_listdirectory#vbscript

But when you look carefully you will notice that this is not really a VBS Script but a WSH Script ?

What is the difference ? See here

WSH  is a very COM scripting technique, that has been replaced over time by .NET and PowerShell.

But is still available even on Windows 10  Winking smile

 

Prerequisites :

Download the WinSCPnet.dll Assembly from here

And register the .Net Assembly to expose the COM component.

‘ 32 Bit => %WINDIR%\Microsoft.NET\Framework\v2.0.50727\RegAsm.exe WinSCPnet.dll /codebase /tlb:WinSCPnet32.tlb
‘ 64 bit => %WINDIR%\Microsoft.NET\Framework64\v4.0.30319\RegAsm.exe WinSCPnet.dll /codebase /tlb:WinSCPnet64.tlb

PS :

Keep in mind that you also need the WinSCP.exe (from the same version as the WinSCPnet.dll !

So make sure you download both of them to guarantee the compatibility between the 2

 

Solution :

This is the original WSH Code

<job>                                                               
<reference object="WinSCP.Session"/>
<script language="VBScript">
Option Explicit
 
' Setup session options
Dim sessionOptions
Set sessionOptions = CreateObject("WinSCP.SessionOptions")
With sessionOptions
    .Protocol = Protocol_Sftp
    .HostName = "example.com"
    .UserName = "user"
    .Password = "mypassword"
    .SshHostKeyFingerprint = "ssh-rsa 1024 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
End With
 
Dim session
Set session = WScript.CreateObject("WinSCP.Session")
 
' Connect
session.Open sessionOptions
 
' Upload files
Dim transferOptions
Set transferOptions = WScript.CreateObject("WinSCP.TransferOptions")
transferOptions.TransferMode = TransferMode_Binary
 
Dim transferResult
Set transferResult = session.PutFiles("d:\toupload\*", "/home/user/", False, transferOptions)
 
' Throw on any error
transferResult.Check
 
' Print results
Dim transfer
For Each transfer In transferResult.Transfers
    WScript.Echo "Upload of " & transfer.FileName & " succeeded"
Next
 
' Disconnect, clean up
session.Dispose
 
</script>
</job>

 

Convert WHS into a real VBS Script like this :

Option Explicit
 
' Setup session options
Const Protocol_Ftp = 2 ' 0 = SFTP

Dim sessionOptions

Set sessionOptions = CreateObject("WinSCP.SessionOptions")

With sessionOptions
    .Protocol = Protocol_Ftp
    .HostName = "xxx.xx.xxx.xx"
    .UserName = "xxxxxxx"
    .Password = "xxxxx"
    '.SshHostKeyFingerprint = "ssh-rsa 2048 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
End With
 
Dim session
Set session = CreateObject("WinSCP.Session")
 
' Connect
session.Open sessionOptions
 
Dim directoryInfo
Set directoryInfo = session.ListDirectory("/")
 
Dim fileInfo
For Each fileInfo In directoryInfo.Files

' To avoid Variable uses an Automation type not supported in VBScript 800A01CA Error
' https://support.microsoft.com/en-us/help/306916/prb-vbscript-type-mismatch-error-when-field-type-is-adnumeric

    WScript.Echo fileInfo.Name  & " with size " & CDbl(fileInfo.Length) & _
        ", permissions " & fileInfo.FilePermissions & _
        " and last modification at " & fileInfo.LastWriteTime
Next
 
' Disconnect, clean up
session.Dispose

 

If you want to use FTP using TLS encryption you need to use these Session Options

Option Explicit
 
' Setup session options
Const Protocol_Ftp = 2
Const Protocol_SFtp = 0

Const Implicit = 0
Const ExplicitTls = 3
Const ExplicitSsl = 2

Dim sessionOptions

Set sessionOptions = CreateObject("WinSCP.SessionOptions")

With sessionOptions
    .HostName = "xxxxxxxxxx"
    .UserName = "xxxxxxxxxx"
    .Password = "xxxxxx"

    .Protocol = Protocol_Ftp
    .FTPSecure =  ExplicitSsl 
    .TlsHostCertificateFingerprint = "xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"

    ' .Protocol = Protocol_SFtp 
    ' .FTPSecure =  ExplicitTls 
    '.SshHostKeyFingerprint = "xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"

End With
Option Explicit
 
' Setup session options
Const Protocol_Ftp = 2
Const Protocol_SFtp = 0

Const Implicit = 0
Const ExplicitTls = 3
Const ExplicitSsl = 2

Dim sessionOptions

Set sessionOptions = CreateObject("WinSCP.SessionOptions")

With sessionOptions
    .HostName = "xxxxxxxxxx"
    .UserName = "xxxxxxxxxx"
    .Password = "xxxxxx"

    .Protocol = Protocol_Ftp
    .FTPSecure =  ExplicitSsl 
    .TlsHostCertificateFingerprint = "xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"

    ' .Protocol = Protocol_SFtp 
    ' .FTPSecure =  ExplicitTls 
    '.SshHostKeyFingerprint = "c4:15:73:e2:3a:c9:7b:cd:9e:a4:f1:ed:00:d2:ff:d7:56:fb:58:58"

End With

To get access to the Protocol and KeyFingerPrint in WinSCP

Go to Sessions – Server/Protocol Information

image

Copy the FingerPrint Key to the keyboard to use in your script

image

Bonus :

The beauty of the WinSCPnet.dll Assembly is that it has 2 interfaces being a DotNet Assembly that is COM Compatible.

As shown above you can use it in any COM Compatible Client (like VBS Script or AutoIT) Smile

1. .Net Assembly – PowerShell

But as well in PowerShell or any other DotNet Compatible Client (like C# or VB .Net or AutoIT !)

Here is how to generate the script from within WinSCP GUI.

Go to Generate Session URL/Code

image

Select .Net Assembly Code / PowerShell

image

Copy to clipboard and you’re off …

2. Scripting : Batch – Commandline and more …

image

For the Batch you need to download the commandline version WinSCP.com

Or you can run the the /console commandline option using the WinSCP.exe

image

ISSUES :

1. File Encoding Windows to UNIX

I had issues sending files from a Windows server to a UNIX server.

All looked OK when inspecting the source files… But the receiving party alarmed me that the files where corrupt Confused smile

There are 3 strange characters preceding the data on the other end ?

image

After investigation these 3 character are an indication that file is a UTF-8 encoding including a BOM

clip_image001

While UNIX don’t these encoded files, but wants ANSI or ASCIIF encoded files.

You can fix it 2 ways :

A. GUI

image

Select Preference of the DESITINATION Site and SET the “remove BOM and EOF marksoption

B. Scripting

The scripting engine of WinSCP has not means of setting this option automatically Sad smile

PowerShell script to save the files before sending as ASCII encoded files

Use this script to inspect the encoding of the files

CLS

Get-Content -Encoding Byte -TotalCount 100 "YourFile.txt" |% {Write-Host ("{0:x2} " -f $_) -NoNewline}; Write-Host

If you see these 3 Bytes : ef bb bf appearing at the beginning of the file  you have a UTF-8 encoded file

image

Save the file using ASCII encoding fixes the issue :

Get-Content "YourFile.txt" | out-file -encoding ASCII "NewFile.txt

 

AutoIT script to save the file as ANSI encoding:

If you open the file in Binary Mode – 16

You can see the same 3 characters appearing

<!– HTML generated using hilite.me –>

$file = FileOpenDialog("Select test file", @DesktopDir, "CSV files (*.csv*)")

 

$hFileOpen = FileOpen ($file,16)
$out = FileRead ($hFileOpen)

 

image

 

To fix it save the file as ANSI Mode – 512

<!– HTML generated using hilite.me –>

$file = FileOpenDialog("Select test file", @DesktopDir, "CSV files (*.csv*)")

 

$hFileOpen = FileOpen ($file,512) ; ANSI Encoding
$out = FileRead ($hFileOpen)

 

2. Event ID Error 8 / 11 Crypt32

When running the GUI I did not have any issues but when running the cmdline options I could not get connected ?

It took me a long time to figger out what the problem was. In the event viewer I saw a lot of Crypt32 Errors ?

image

The reason was that the server did not have Internet Access …

image

Once I fixed it I could log connect … this means that the GUI behaves differently from the cmdline option.

The GUI could connect using port 22 or 21 while the cmdline option needed to have regular internet access to download so Certificate information

Once this was done I could disable the regular internet access again.

 

Conclusion :

Basically the WinSCP options are unlimited on a windows platform.

You just have to choose the right scripting environment, GUI / Command Line / COM / .Net

Test it against your FTP server or SFTP Server

 

Happy Scripting Smile

Enjoy

Advertisements

PowerShell – Using Windows Script Component

November 3, 2014

Windows Script Component (WSC) goes a long way back. This technique is almost 15 years old, dating from Windows 2000.

But you can still use it, even by the latest Powershell version. Smile

Find here more information about what WSC is.

In short WSC gives you the possibility to create COM objects on the fly. Which you can register as normal COM objects exposing you methods and properties.

image

As you can see the WSC files show up with different Icon. If you right click it you see extra shortcut menu’s.

image

Register will register you COM object in the registry if you filled up the WSC file with the proper XML sections. The same as you would use the regsvr32.exe command line tool.

Or you can just run functions as is, without registering.

WSC supports VBScript / JScript and PerlScript.

Let’s give an example on how to access the object from within PS.

1. Create a simple WSC file.

image

<?xml version=”1.0″?>
<component id=”mywsc”>
<public>
<method name=”test”/>
</public>

</component>

 

This simple example will display a Message Box with a text as parameter, when invoked using PS.

image

You need to use the .Net [system.runtime.interopservices.marshal]::BindToMoniker to connect to the WSC object.

As you can see I use the following prefix syntax “script:…” in order to call the WSC object on the fly.

This will avoid you to register the COM object on your system. Which makes it extremely flexible. You can now adjust the WSC code on the fly and run it. As well as copy it on multiple machines without any admin overhead.

The InvokeMember has 5 parameters, which explain themselves :

1. “Test” is the name of the WSC VBScript function to call.

2. $im holds the BindingFlags method

3. Not used

4. Variable that holds the references to the WSC Object

5. Used to send across the Parameter(s) to the WSC function.

Here is the full code with some helper calls in between.

 

CLS

$test = [system.runtime.interopservices.marshal]::BindToMoniker("script:C:\_\Apps\_PowerShell\_Advanced Examples\_WSC Examples\Test.wsc")

$isObj = [system.runtime.interopservices.marshal]::IsComObject($test)

write-host $isObj

Write-host " "

Write-host "Get-Type" 

$test.GetType()

$param = "Hello World ! -&gt; From PowerShell to WSC"

$im = [System.Reflection.BindingFlags]::InvokeMethod

$test.GetType().psobject.BaseObject.InvokeMember("test", $im, $null, $test, $param)

$ret1 = [System.Runtime.InteropServices.Marshal]::GetIDispatchForObject($test)

$ret2 = [System.Runtime.InteropServices.Marshal]::GetIUnknownForObject($test)

$uObj = [System.Runtime.InteropServices.Marshal]::GetUniqueObjectForIUnknown($ret2)

$Obj = [System.Runtime.InteropServices.Marshal]::GetObjectForIUnknown($ret2) # refers back to $WSC COM object returns the GUID

write-host " "

#[System.Runtime.InteropServices.Marshal]::Release($test)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($test)

write-host "5. "  $ret1 $ret2 $uObj $obj

write-host " "

[System.Runtime.InteropServices.Marshal]::AreComObjectsAvailableForCleanup()

write-host " "

[System.Runtime.InteropServices.Marshal]::GetLastWin32Error()

rv test, im, param, isObj, ret1, ret2, uobj, obj

[System.GC]::Collect()

Enjoy!


QlikView – Creating Macro’s using VBScript

September 29, 2014

Qlikview is heaven for scripters like us Smile

It has an API that can be accessed by any COM compatible client.

And on the other hand it supports the VBScript language for writing Macro’s.

This will get you started.

First add the Macro to QV => Tools -> EDIT Module

image

Go to => Settings -> Document Properties -> Triggers

clip_image002

Add your Macro Function

clip_image004

Open the document again and test it.

clip_image006

See help file to know where you can trigger macro’s. There are several objects to which you can attach Macros

This is a Button example

clip_image008

clip_image010

Now it’s up to your imagination what you are going to automate in QlikView.

We could also call PowerShell scripts if you like.

If you are interested stay tuned Winking smile