SSIS ForEach file on FTP site

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » SSIS ForEach file on FTP site

16 posts, Page 1 of 2

1 2 »»

SSIS ForEach file on FTP site

Rate Topic

Display Mode

Topic Options

Author

Message

Michael Lato

Posted Monday, September 29, 2008 9:02 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, May 06, 2013 12:07 PM
Points: 431, Visits: 583

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

Post #577774

Sneh Agrawal

Posted Monday, October 27, 2008 3:48 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 26, 2011 10:38 AM
Points: 518, Visits: 196

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. "

Post #592499

Shelley Humphrys

Posted Tuesday, October 28, 2008 5:56 AM

Valued Member

Group: General Forum Members
Last Login: Friday, September 07, 2012 2:26 AM
Points: 65, Visits: 29

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!

Post #592774

CozyRoc

Posted Tuesday, October 28, 2008 6:43 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:51 AM
Points: 1,106, Visits: 2,111

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/

Post #592821

bdukkipati

Posted Tuesday, March 03, 2009 11:03 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, February 27, 2011 7:42 AM
Points: 3, Visits: 24

Hi,

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

Thx,
Babu

Post #667849

kevinthepure

Posted Wednesday, May 20, 2009 2:54 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 06, 2013 12:12 PM
Points: 3, Visits: 44

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.

Post #720933

Holly Kilpatrick

Posted Friday, May 28, 2010 11:25 AM

Valued Member

Group: General Forum Members
Last Login: Friday, October 28, 2011 2:02 PM
Points: 71, Visits: 306

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.

Post #929850

Holly Kilpatrick

Posted Friday, May 28, 2010 11:30 AM

Valued Member

Group: General Forum Members
Last Login: Friday, October 28, 2011 2:02 PM
Points: 71, Visits: 306

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!

Post #929852

CozyRoc

Posted Friday, May 28, 2010 11:35 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:51 AM
Points: 1,106, Visits: 2,111

Holly Kilpatrick (5/28/2010)

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/

Post #929856

Chris Honcoop

Posted Wednesday, June 16, 2010 7:17 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, June 17, 2010 10:13 AM
Points: 1, Visits: 5

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 Smile

Hope that helps,
Coop

Post #938610

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions