FTP task limitations

Hi,

I'm thinking about using the FTP task in an integration i am developing, but before i do, i need to get an idea that what i want to do is possible.

With the FTP task could i get a list of directories on the FTP server, and compare the folders with a result set returned from a SQL query. If any of the directories on the FTP site then i want to download each of the relevent directories and there contents to the local machine.

Could someone give me an idea as to if this is possible via the task, or if indeed a script task would be better?

Many thanks,

Grant

/n software has a task library for SSIS.
Their FTP task supports remote directory list retrieval along with many other features which built-in SSIS FTP task doesn't have.
Here is the URL:

http://www.nsoftware.com/ssis/

Regards,
Yitzhak

|||The problem with nsoftware SSIS components is that they do not work...
Sadly it's beta - I was really hoping to have visual components for FTP and Email tasks.

So if those components dont work for you - you probably need to do some scripting in .Net|||

We are using /n software SSIS Tasks (which are currently in beta) without any problems.
Actually, I am using the very feature you need - directory listing with the goal to FTP the latest file (based on the date and time)in the remote FTP directory. The file name is unknown upfront.
Their tech.support is responding through e-mails in timely manner.

/n software folks said that they are planning to release production release relatively soon - in November...

Regards,
Yitzhak

|||I suppose you could use an Execute Process task and a Perl script to perform the actions you desire... But then you are still reaching outside of native SSIS functionality.|||Thank you one and all for your suggestions. I'll look into the /n software component and see if that can solve at least part of my problem. To be honest i figured i may have to implement this part as a scripted task anyway. I've got no problems in writing some VB.Net code to do the job.
Many thanks for all your help.

Cheers,

Grant|||

Sorry to bump a LONG dead thread, but I was reading this and thought that I might as well answer since I too had been searching for something of this variety...

I used a script task to look for the folder listings as below:

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

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, "myServer")

cm.Properties("ServerUserName").SetValue(cm, "myUserName")

cm.Properties("ServerPassword").SetValue(cm, "myPassword")

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, "1")

'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("..")

ftp.SetWorkingDirectory("MyFolder.MySubFolder.MySubSubFolder")

Dim folderNames() As String

Dim fileNames() As String

ftp.GetListing(folderNames, fileNames)

Dim maxname As String = ""

For Each filename As String In fileNames

' whatever operation you need to do to find the correct file...

Next

Dim files(0) As String

files(0) = maxname

ftp.ReceiveFiles(files, "C:\temp", True, True)

' Close the ftp connection

ftp.Close()

'Set the filename you retreive for use in data flow

Dts.Variables.Item("FILENAME").Value = maxname

Catch ex As Exception

Dts.TaskResult = Dts.Results.Failure

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

|||You may check our library SSIS+. I'm talking in particular about SSH Execute, which is secure shell command execute task. You can send a command to retrieve a list of files based on your criteria. After this you can use our secure SFTP task to download the file(s).

Regards,
Ivan

FTP task limitations

Hi,

I'm thinking about using the FTP task in an integration i am developing, but before i do, i need to get an idea that what i want to do is possible.

With the FTP task could i get a list of directories on the FTP server, and compare the folders with a result set returned from a SQL query. If any of the directories on the FTP site then i want to download each of the relevent directories and there contents to the local machine.

Could someone give me an idea as to if this is possible via the task, or if indeed a script task would be better?

Many thanks,

Grant

/n software has a task library for SSIS.
Their FTP task supports remote directory list retrieval along with many other features which built-in SSIS FTP task doesn't have.
Here is the URL:

http://www.nsoftware.com/ssis/

Regards,
Yitzhak

|||The problem with nsoftware SSIS components is that they do not work...
Sadly it's beta - I was really hoping to have visual components for FTP and Email tasks.

So if those components dont work for you - you probably need to do some scripting in .Net|||

We are using /n software SSIS Tasks (which are currently in beta) without any problems.
Actually, I am using the very feature you need - directory listing with the goal to FTP the latest file (based on the date and time)in the remote FTP directory. The file name is unknown upfront.
Their tech.support is responding through e-mails in timely manner.

/n software folks said that they are planning to release production release relatively soon - in November...

Regards,
Yitzhak

|||I suppose you could use an Execute Process task and a Perl script to perform the actions you desire... But then you are still reaching outside of native SSIS functionality.|||Thank you one and all for your suggestions. I'll look into the /n software component and see if that can solve at least part of my problem. To be honest i figured i may have to implement this part as a scripted task anyway. I've got no problems in writing some VB.Net code to do the job.
Many thanks for all your help.

Cheers,

Grant|||

Sorry to bump a LONG dead thread, but I was reading this and thought that I might as well answer since I too had been searching for something of this variety...

I used a script task to look for the folder listings as below:

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

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, "myServer")

cm.Properties("ServerUserName").SetValue(cm, "myUserName")

cm.Properties("ServerPassword").SetValue(cm, "myPassword")

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, "1")

'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("..")

ftp.SetWorkingDirectory("MyFolder.MySubFolder.MySubSubFolder")

Dim folderNames() As String

Dim fileNames() As String

ftp.GetListing(folderNames, fileNames)

Dim maxname As String = ""

For Each filename As String In fileNames

' whatever operation you need to do to find the correct file...

Next

Dim files(0) As String

files(0) = maxname

ftp.ReceiveFiles(files, "C:\temp", True, True)

' Close the ftp connection

ftp.Close()

'Set the filename you retreive for use in data flow

Dts.Variables.Item("FILENAME").Value = maxname

Catch ex As Exception

Dts.TaskResult = Dts.Results.Failure

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

|||You may check our library SSIS+. I'm talking in particular about SSH Execute, which is secure shell command execute task. You can send a command to retrieve a list of files based on your criteria. After this you can use our secure SFTP task to download the file(s).

Regards,
Ivan

FTP task limitations

Hi,

I'm thinking about using the FTP task in an integration i am developing, but before i do, i need to get an idea that what i want to do is possible.

With the FTP task could i get a list of directories on the FTP server, and compare the folders with a result set returned from a SQL query. If any of the directories on the FTP site then i want to download each of the relevent directories and there contents to the local machine.

Could someone give me an idea as to if this is possible via the task, or if indeed a script task would be better?

Many thanks,

Grant

/n software has a task library for SSIS.
Their FTP task supports remote directory list retrieval along with many other features which built-in SSIS FTP task doesn't have.
Here is the URL:

http://www.nsoftware.com/ssis/

Regards,
Yitzhak

|||The problem with nsoftware SSIS components is that they do not work...
Sadly it's beta - I was really hoping to have visual components for FTP and Email tasks.

So if those components dont work for you - you probably need to do some scripting in .Net|||

We are using /n software SSIS Tasks (which are currently in beta) without any problems.
Actually, I am using the very feature you need - directory listing with the goal to FTP the latest file (based on the date and time)in the remote FTP directory. The file name is unknown upfront.
Their tech.support is responding through e-mails in timely manner.

/n software folks said that they are planning to release production release relatively soon - in November...

Regards,
Yitzhak

|||I suppose you could use an Execute Process task and a Perl script to perform the actions you desire... But then you are still reaching outside of native SSIS functionality.|||Thank you one and all for your suggestions. I'll look into the /n software component and see if that can solve at least part of my problem. To be honest i figured i may have to implement this part as a scripted task anyway. I've got no problems in writing some VB.Net code to do the job.
Many thanks for all your help.

Cheers,

Grant|||

Sorry to bump a LONG dead thread, but I was reading this and thought that I might as well answer since I too had been searching for something of this variety...

I used a script task to look for the folder listings as below:

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

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, "myServer")

cm.Properties("ServerUserName").SetValue(cm, "myUserName")

cm.Properties("ServerPassword").SetValue(cm, "myPassword")

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, "1")

'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("..")

ftp.SetWorkingDirectory("MyFolder.MySubFolder.MySubSubFolder")

Dim folderNames() As String

Dim fileNames() As String

ftp.GetListing(folderNames, fileNames)

Dim maxname As String = ""

For Each filename As String In fileNames

' whatever operation you need to do to find the correct file...

Next

Dim files(0) As String

files(0) = maxname

ftp.ReceiveFiles(files, "C:\temp", True, True)

' Close the ftp connection

ftp.Close()

'Set the filename you retreive for use in data flow

Dts.Variables.Item("FILENAME").Value = maxname

Catch ex As Exception

Dts.TaskResult = Dts.Results.Failure

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

|||You may check our library SSIS+. I'm talking in particular about SSH Execute, which is secure shell command execute task. You can send a command to retrieve a list of files based on your criteria. After this you can use our secure SFTP task to download the file(s).

Regards,
Ivan

From PL/SQL to SQL Server

I need your help, I have a candidate (yes I am a Recruiter but new to IT) who has 15 years experience of developing in oracle. Particularly PL/SQL. He wants to move over to SQL Server and most clients will not look at assisting in the training required. For those out there those have changed from one to the other please could you tell me how you went about it and how difficult it was?
Thank you in advance

It's pretty easy, the principles are the same, I suggest he downloads SQL express (which is free) and gets a book on the subject, and he will be going in no time. Things to focus on:

1. Stored Procedures

2. JOINS (INNER and OUTER)

3. Interfacing with the database from the client side (probably using .NET - again .NET express versions are available to download for free).

HTH

|||

Thank you for your help

i didn't think anyone was going to answer me.

sql