Post a reply

Options
Add an Attachment

If you do not want to add an Attachment to your Post, please leave the Fields blank.

(maximum 10 MB; please compress large files; only common media, archive, text and programming file formats are allowed)

Options

Topic review

xtreemnet

ok. Here is my script task code:


#Region "Imports"
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports WinSCP
#End Region

'ScriptMain is the entry point class of the script. Do not change the name, attributes,
'or parent of this class.
<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

#Region "Help: Using Integration Services variables and parameters in a script"
'To use a variable in this script, first ensure that the variable has been added to
'either the list contained in the ReadOnlyVariables property or the list contained in
'the ReadWriteVariables property of this script task, according to whether or not your
'code needs to write to the variable. To add the variable, save this script, close this instance of
'Visual Studio, and update the ReadOnlyVariables and
'ReadWriteVariables properties in the Script Transformation Editor window.
'To use a parameter in this script, follow the same steps. Parameters are always read-only.

'Example of reading from a variable:
' startTime = Dts.Variables("System::StartTime").Value

'Example of writing to a variable:
' Dts.Variables("User::myStringVariable").Value = "new value"

'Example of reading from a package parameter:
' batchId = Dts.Variables("$Package::batchId").Value

'Example of reading from a project parameter:
' batchId = Dts.Variables("$Project::batchId").Value

'Example of reading from a sensitive project parameter:
' batchId = Dts.Variables("$Project::batchId").GetSensitiveValue()
#End Region

#Region "Help: Firing Integration Services events from a script"
'This script task can fire events for logging purposes.

'Example of firing an error event:
' Dts.Events.FireError(18, "Process Values", "Bad value", "", 0)

'Example of firing an information event:
' Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, fireAgain)

'Example of firing a warning event:
' Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0)
#End Region

#Region "Help: Using Integration Services connection managers in a script"
'Some types of connection managers can be used in this script task. See the topic
'"Working with Connection Managers Programatically" for details.

'Example of using an ADO.Net connection manager:
' Dim rawConnection As Object = Dts.Connections("Sales DB").AcquireConnection(Dts.Transaction)
' Dim myADONETConnection As SqlConnection = CType(rawConnection, SqlConnection)
' <Use the connection in some code here, then release the connection>
' Dts.Connections("Sales DB").ReleaseConnection(rawConnection)

'Example of using a File connection manager
' Dim rawConnection As Object = Dts.Connections("Prices.zip").AcquireConnection(Dts.Transaction)
' Dim filePath As String = CType(rawConnection, String)
' <Use the connection in some code here, then release the connection>
' Dts.Connections("Prices.zip").ReleaseConnection(rawConnection)
#End Region

'This method is called when this script task executes in the control flow.
'Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'To open Help, press F1.

Public Sub Main()
'
' Add your code here
'
Dim strHost As String = Dts.Variables("User::SSHServer").Value
Dim strUserName As String = Dts.Variables("User::SSHUserName").Value
Dim strPvtKeyPath As String = Dts.Variables("User::SSHPrivateKey").Value
Dim strHostKeyFingerprint As String = Dts.Variables("User::SSHRSAFingerprint").Value
Dim strSourceFiles As String = Dts.Variables("User::DirectoryToZip").Value
Dim strDestinationPath As String = Dts.Variables("User::RemoteFilePath").Value
Dim strExtension As String = Dts.Variables("User::Extension").Value
Dim strFileSpec As String = Dts.Variables("User::strFileSpec").Value

Try
' Setup session options
Dim sessionOptions As New SessionOptions

Catch e As Exception
Console.WriteLine("Error: {0}", e)
'Return 1
End Try
Dts.TaskResult = ScriptResults.Success
End Sub

#Region "ScriptResults declaration"
'This enum provides a convenient shorthand within the scope of this class for setting the
'result of the script.

'This code was generated automatically.
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTS ExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTS ExecResult.Failure
End Enum

#End Region

End Class


when I run this task I get the following error:


Exception has been thrown by the target of an invocation.


at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[]providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()


If I comment out or remove the the below statement

Dim sessionOptions As New SessionOptions

The script task runs ok.
martin

xtreemnet wrote:

it fails.

Means what?
Which of the errors are you getting in this case?
Please include a complete callstack of the exception (if any).
xtreemnet

If I put the break point at the below line it does not fail. I could step through all the Dim statement
Dim strHost As String = Dts.Variables("User::SSHServer").Value

As soon as it reaches the statement :

Dim sessionOptions As New SessionOptions

it fails.
martin

And if you put the breakpoint at the very first line?
Dim strHost As String = Dts.Variables("User::SSHServer").Value
xtreemnet

Sorry, for not mentioning the first line.

Its the one below from the code:

Dim sessionOptions As New SessionOptions
martin

Re: SessionOption error

xtreemnet wrote:

I can Import the dll in my VB .net code but for some reason its not accepting the first line.

What first line?
xtreemnet

Removed the .net assembly
Used NuGet to install. When I am saving the VB .net code and saving the script task I get the below error:

TITLE: Microsoft Visual Studio
------------------------------

Cannot show the editor for this task.

------------------------------
ADDITIONAL INFORMATION:

'.', hexadecimal value 0x00, is an invalid character. Line 2, position 13. (mscorlib)

------------------------------
BUTTONS:

OK
------------------------------
xtreemnet

Removed the package "winscp576automation"
Added just "winscp576". Still no luck. With break point the VST opens and closes immediatley with following error:

Exception has been thrown by the target of an invocation.

at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers,

CultureInfo culture, String[] namedParams)
xtreemnet

SessionOption error

Hi,

I have successfully installed winscpnet.dll on my windows 7 machine with VST 2010 shell.
I have written a script task in VB .net that uploads files on SFTP.
Its working fine on my machine.

I am trying to create the same project on our Test server which is Windows 2012 server 64 bit. VST 2010 shell is installed on the server. WinSCPnet.dll was installed using powershell as below:

Set-location "c:\winscp576automation"
[System.Reflection.Assembly]::Load("System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")
$publish = New-Object System.EnterpriseServices.Internal.Publish
$publish.GacInstall("c:\winscp576automation\WinSCPnet.dll")

I can Import the dll in my VB .net code but for some reason its not accepting the first line. The task just fails with the most unhelpful error. If I try to set up a break point in the code it doesn't enter the VST app.

Dim sessionOptions As New SessionOptions


Below is full code:

#Region "Imports"
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports WinSCP
#End Region

'This method is called when this script task executes in the control flow.
'Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'To open Help, press F1.

Public Sub Main()
'
' Add your code here
'
Dim strHost As String = Dts.Variables("User::SSHServer").Value
Dim strUserName As String = Dts.Variables("User::SSHUserName").Value
Dim strPvtKeyPath As String = Dts.Variables("User::SSHPrivateKey").Value
Dim strHostKeyFingerprint As String = Dts.Variables("User::SSHRSAFingerprint").Value
Dim strSourceFiles As String = Dts.Variables("User::DirectoryToZip").Value
Dim strDestinationPath As String = Dts.Variables("User::RemoteFilePath").Value
Dim strExtension As String = Dts.Variables("User::Extension").Value
Dim strFileSpec As String = Dts.Variables("User::strFileSpec").Value

Try
' Setup session options
Dim sessionOptions As New SessionOptions
With sessionOptions
.Protocol = Protocol.Sftp
.HostName = strHost
.UserName = strUserName
.SshPrivateKeyPath = strPvtKeyPath
.SshHostKeyFingerprint = strHostKeyFingerprint
End With
Catch e As Exception
Console.WriteLine("Error: {0}", e)
'Return 1
End Try
Dts.TaskResult = ScriptResults.Success
End Sub