[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Script task code not executing

Posted on 2009-02-12
3
Medium Priority
?
330 Views
Last Modified: 2013-11-10
Hi All,

My code inside the Script Task is not being executed.

The code below should download a file from a Ftp server. I have put the code in a script task and when I execute the the script task it succeeds but nothing gets downloaded. I am currently running the script task on its own. Is there something I am missing. Also when I change setting it never errors. When i configure a FTP Task with the same settings that downloads the zip file fine.

Thanks
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
 
Public Class ScriptMain
 
    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts object. Connections, variables, events,
    ' and logging features are available as static members of the Dts class.
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    ' 
    ' To open Code and Text Editor Help, press F1.
    ' To open Object Browser, press Ctrl+Alt+J.
 
    Public Sub Main()
 
 
        Try
 
            'Create the connection to the ftp server
 
            Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
 
            'Set the properties like username & password
 
            cm.Properties("ServerName").SetValue(cm, "ServerName")
 
            cm.Properties("ServerUserName").SetValue(cm, "ServerUserName")
 
            cm.Properties("ServerPassword").SetValue(cm, "password")
 
            cm.Properties("ServerPort").SetValue(cm, "21")
 
            cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout
 
            cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
 
            cm.Properties("Retries").SetValue(cm, "5")
 
            'create the FTP object that sends the files and pass it the connection created above.
 
            Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
 
            'Connects to the ftp server
 
            ftp.Connect()
 
 
            ftp.SetWorkingDirectory("/dir1/dir2/") 'set the remote directory
 
            Dim files(0) As String
            files(0) = "ftpfile.zip" 'eg. File1.trg
 
            'Get the file
            ftp.ReceiveFiles(files, "C:\localfolder", True, False)
 
                ftp.Close()
 
        Catch ex As Exception
 
            Dts.TaskResult = Dts.Results.Failure
 
        End Try
 
        Dts.TaskResult = Dts.Results.Success
 
    End Sub
 
End Class

Open in new window

0
Comment
Question by:cjJosephj
3 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23636712
I don't work with SSIS much, but in the script task to you have to tell it to execute MAIN()?

If you don't, maybe you have to explicitly call it.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
 
Public Class ScriptMain
 
call MAIN() 
    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts object. Connections, variables, events,
    ' and logging features are available as static members of the Dts class.
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    ' 
    ' To open Code and Text Editor Help, press F1.
    ' To open Object Browser, press Ctrl+Alt+J.
 
    Public Sub Main()
 
 
        Try
 
            'Create the connection to the ftp server
 
            Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
 
            'Set the properties like username & password
 
            cm.Properties("ServerName").SetValue(cm, "ServerName")
 
            cm.Properties("ServerUserName").SetValue(cm, "ServerUserName")
 
            cm.Properties("ServerPassword").SetValue(cm, "password")
 
            cm.Properties("ServerPort").SetValue(cm, "21")
 
            cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout
 
            cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
 
            cm.Properties("Retries").SetValue(cm, "5")
 
            'create the FTP object that sends the files and pass it the connection created above.
 
            Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
 
            'Connects to the ftp server
 
            ftp.Connect()
 
 
            ftp.SetWorkingDirectory("/dir1/dir2/") 'set the remote directory
 
            Dim files(0) As String
            files(0) = "ftpfile.zip" 'eg. File1.trg
 
            'Get the file
            ftp.ReceiveFiles(files, "C:\localfolder", True, False)
 
                ftp.Close()
 
        Catch ex As Exception
 
            Dts.TaskResult = Dts.Results.Failure
 
        End Try
 
        Dts.TaskResult = Dts.Results.Success
 
    End Sub
 
End Class

Open in new window

0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 23639273
I changed to values to my FTP site and got it to work. I did not change any of your code. It does work.
I removed the Try, Catch to test because it would not error as expected with it in there. But after I got it working by putting my site in, I put the Try Catch back and it works fine.
I also added MsgBox lines at different places to show me where the code was. For example:
ftp.Connect()
MsgBox("after ftp.Connect")
 Bottom line, nothing wrong with the code. I agree that it does not error because of the Try Catch, not sure why.
0
 

Accepted Solution

by:
cjJosephj earned 0 total points
ID: 23640114
Hi thanks for your reply, I have worked out why it was not working. I set the path of my properties  for ftp.SetWorkingDirectory to ("/folder/subfolder/")  which works fine using the ftptask. When I changed the path to ftp.SetWorkingDirectory("\\folder\subfolder\")  the files  downloaded fine using the script task.

Thanks
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question