SSIS ForEach file on FTP site

Question

SSIS ForEach file on FTP site

Michael Lato

Ten Centuries

Points: 1278
More actions
September 29, 2008 at 9:02 am

#123877

Hello all,
Please forgive this "newbie" question. I'm currently migrating from DTS to SSIS and my google skills aren't finding the answer. I'm sure it'll be easy for the experts to answer!
I have an FTP site that receives multiple files per day. Once a day I need to connect to the site, pull all the files, and delete the files from the FTP site once received. In order to prevent deletion of a file that I haven't copied I want to use the ForEach container to copy one file, then delete it.
However, I have no idea how to link the ForEach container to an FTP site. Am I doing something incredibly stupid here?
Regards,
Michael Lato
Regards,
Michael Lato

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Sneh Agrawal Right there with Babe Points: 770 More actions · Answer 1

Even i am facing the same issue. I gave the FTP path as the collection directory. The package runs but the progress tab shows that the dirsctory is empty.

"Warning: The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty. "

Shelley Humphrys SSC Enthusiast Points: 145 More actions · Answer 2

I've done this, but slightly differently.

I first created an FTP task to pull across all the files.

Then I created a ForEach loop to simply loop through all the filenames collected in my local directory (using the Foreach File Enumerator) and created a 2nd FTP task within the ForEach loop to delete the remote file stored in my file name variable.

There's probably more cleverer ways of doing it but I'm quite new to this too and it seems to work!

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

Here is what you have to do:

1. Pull the list of remote FTP files and store in package variable (check following script how to pull the list).

2. Insert ForEach container and iterate over the list pulled in the previous step.

3. Insert in the ForEach container FTP task, which downloads current iterated file.

4. Insert a step after the download, with another FTP task which removes the remote file.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

bdukkipati SSC Journeyman Points: 81 More actions · Answer 4

Hi,

I not able to implement this solution. Do you or can you please give me a sample code?

Thx,

Babu

kevinthepure SSC Enthusiast Points: 107 More actions · Answer 5

Yes it isn't suggested in the context of the task, but you can put *.* in the sourcefile to ftp ALL files down regardless of name.

Holly Kilpatrick Ten Centuries Points: 1195 More actions · Answer 6

I have the same problem. I need to put my FTP receive task inside a Foreach File loop, and I can't figure out how to specify the path on the FTP site in the Foreach File collection folder under "enumerator configuration."

I don't really understand the CozyRoc solution given here, but I don't have the CozyRoc component.

Is there no way to configure the Foreach File path to find files in an FTP folder? Is it limited to network folders? This would be a great method if I could set the path in the Foreach File collection.

Holly Kilpatrick Ten Centuries Points: 1195 More actions · Answer 7

Right after I posted the above, I found a suggestion on Microsoft Connect from 2008:

"There is currently no way to enumerate files in an FTP folder using SSIS. Even though we can place an FTP path in the Foreach loop container folder enumeration path, enumeration fails."

The reply from Microsoft was:

"Thanks for sending us your feedback.

This is a great idea but we will not be able to address this in SQL Server 2008 release. Its possible that we might provide this functionality in the next major release.

We'll update you in a few months on our progress on this workitem.

Thanks,

SSIS Team. "

So I guess we can't do it. If anyone knows differently, please reply!

COZYROC One Orange Chip Points: 28499 More actions · Answer 8

Holly Kilpatrick (5/28/2010)
Right after I posted the above, I found a suggestion on Microsoft Connect from 2008:
"There is currently no way to enumerate files in an FTP folder using SSIS. Even though we can place an FTP path in the Foreach loop container folder enumeration path, enumeration fails."
The reply from Microsoft was:
"Thanks for sending us your feedback.
This is a great idea but we will not be able to address this in SQL Server 2008 release. Its possible that we might provide this functionality in the next major release.
We'll update you in a few months on our progress on this workitem.
Thanks,
SSIS Team. "
So I guess we can't do it. If anyone knows differently, please reply!

Holly,

That is actually not true. You can enumerate the list of remote files. However you have to implement script for this. The posted CozyRoc's link above includes a sample script, which can help you.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Chris Honcoop SSC Journeyman Points: 79 More actions · Answer 9

I was just trying to do this today - I found two ways to do this (I don't think you need the CozyRoc components for the example CozyRoc gave) - both are script based:

Before creating the scripts:

1. Create a variable with type Object - in my example below its called "FTPFileList"

2. Create your FTP Connection and test to make sure its working - In my example its called "FTP Connection"

Methods (I prefer B personally as you don't need to hardcode any connection or variable names and actually I take it further and join the filenames in the dataflow to a list in a database of Files I've already processed and loop through only the non-processed names but that is beyond this example)

A. The way CozyRoc is doing it - using a script and pumping to a variable (Create a variable with type Object since its going to push an array). The CozyRoc code might be 2008 or use some custom code because with 2005 scripts in the Control Flow can't see the connections so things like "Dts.Connections(Me.FtpConnection)" don't work. You have to spell them out by name.

Below is my code, my script is named "Get Filenames From FTP Site" (you'll see I reference it in the errors and information just to make troubleshooting easier - if you have a lot of filenames or for some reason don't want your information log filled up you can comment out the for each).

Public Sub Main()

Dim result As Integer

Dim conMan As ConnectionManager

Dim ftp_client As FtpClientConnection

Dim sFolderNames() As String

Dim sFileNames() As String

conMan = Dts.Connections( "FTP Connection")

ftp_client = New FtpClientConnection(conMan.AcquireConnection(Nothing))

Try

ftp_client.Connect()

ftp_client.GetListing(sFolderNames, sFileNames)

Dts.Variables("FTPFileList").Value = sFileNames

For Each sCurrentFileName As String In sFileNames

Dts.Events.FireInformation(0, "Get Filenames From FTP Site", "File Available:" + sCurrentFileName, String.Empty, 0, True)

Next sCurrentFileName

result = Dts.Results.Success

Catch ex As Exception

result = Dts.Results.Failure

Dts.Events.FireError(0, "Get Filenames From FTP Site", ex.Message, String.Empty, 0)

Finally

ftp_client.Close()

End Try

Dts.TaskResult = result

End Sub

B. Use a dataflow task with a script source to push the filenames into a variable.

Steps:

1. Drag Script object into data flow and choose source

2. On the Inputs and Outputs setup your output, in my example I name the output "Files" with a single column of "FileName"

3. On the Connection Managers section choose your connection and assign it a name (I used "FTPConnection")

4. Edit the script - here is mine (you might notice I have some code to exclude files starting with "Margin_".. if you want *.* you can remove that if block and only have the With):

Public Class ScriptMain

Inherits UserComponent

Private conMan As IDTSConnectionManager90

Private ftpClient As FTPClientConnection90

Private folderNames() As String

Private fileNames() As String

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

conMan = Me.Connections.FTPConnection

ftpClient = CType(conMan.AcquireConnection(Nothing), FTPClientConnection90)

End Sub

Public Overrides Sub PreExecute()

ftpClient.Connect()

ftpClient.GetListing(folderNames, fileNames)

End Sub

Public Overrides Sub CreateNewOutputRows()

For Each fileName As String In fileNames

If Not fileName.StartsWith("Margin_", StringComparison.OrdinalIgnoreCase) Then

With Me.FilesBuffer

.AddRow()

.FileName = fileName

End With

End If

Next fileName

5. Add a recordset destination and assign it to your object variable, take the output from the script source to the recordset.

After that for either method A or B you just For Each over the list and do whatever you need to do 🙂

Hope that helps,

Coop

lijnenjoost Right there with Babe Points: 767 More actions · Answer 10

Here is an example of creating a Foreach Loop FTP file enumerator:

http://microsoft-ssis.blogspot.com/2011/08/foreach-ftp-file-enumerator.html

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 11

This is just another option. You could easily have all these operations being performed as part of a simple bat file containing the FTP commands and have SSIS execute the bat file instead.

Jayanth Kurup[/url]

lijnenjoost Right there with Babe Points: 767 More actions · Answer 12

Jayanth_Kurup (8/7/2011)
This is just another option. You could easily have all these operations being performed as part of a simple bat file containing the FTP commands and have SSIS execute the bat file instead.

If you have read it you know all roads lead to Rome... 😉

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 13

They sure do 😀 , but its always fun trying to find a short cut.

Jayanth Kurup[/url]

lijnenjoost Right there with Babe Points: 767 More actions · Answer 14

Here is a Foreach FTP File Enumerator 2008 & 2012

http://microsoft-ssis.blogspot.com/2012/01/custom-ssis-component-foreach-ftp-file.html

2012 version: