Got an FTP Task and it works fine as long as a designate a particular file in the output directory. I merely want to FTP each file in the directory and afterwords I'll delete all files in the directory or move then to a sent directory. All files have filename and then the same dtatestamp suffix and are .txt files. I tried the ForEachLoop container but don't know how to designate the file name to ftp each time. Any ideas?

SSIS package "CreateFilesToFTP.dtsx" starting. Error: 0xC002918B at FTP for CLM Task, FTP Task: Unable to send files using "FTP Connection Manager". Task failed: FTP for CLM Task Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "CreateFilesToFTP.dtsx" finished: Failure. This same FTP for CLM Task worked when I designated one file explicity before so I know the connection is good.....

Hi, in the Variable Mapping tab of the ForEachLoop, select the variable you want to use from the drop down, and put the value 0 in "Index". The file name will then be passed into this variable. Then follow the instructions I gave last night, to pass it into the FTP task. Hope this helps, but let me know if your still stuck. PetePeter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/

Ok, I think there must be an issue with the FTP Connection Manager. Without seeing it, I'm only guessing, but SQL teats FTP connection details as "sensitive" so depending on the package protection level of your package, it is possible to loose the User Name and Password. Please can you double click on the FTP Connection Manager and a) check the user name and password are still there and b) click Test Connection. Could you also click on the control flow, and then look at the properties window. I am interested to know the setting of the "Package Protection Level" property. Thanks.Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/

Sorry, one more thought. On the FTP connection Manager, could you also please increase the "Chunk Size" property to 1000kbPeter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/

Set it to 1000 with same result - lots of red.

Hi, in you ForEachLoop, pass the fully qualified file name into a variable. Then in FTP task, set IsLocalPathVariable = True and then select your variable in the LocalPath dropdown.Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/

And did you test the connection, etc on the connection manager?Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/

Every time I open the FTP Connection Manager Editor everything is there but the Credentials Password...I reenter it again and click Test Connection and "Test Connect succeeded"

Ok, I'm running out of ideas on this one. Only two options left as far as I can see: 1) Set an expression on the connection managers connection string property to FTPAddress:21.UserName.Password_FTP 2)Re-write using a script task. You can use this thread for the base code http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/2b7cf6b5-d16d-44cd-954d-68e9ba98ee91Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/

So I ought to be abe to drop another FTP Task in the Editor not in the container to test right? Start freash and repeat the ability to send one file. But it's doing the same thing so I suppose it's a problem running here. Everytime I open the FTP Connection Manager Editor the password is blank. Is that normal? I'm actually using a local address 10.1.1.200 and server port 21 to test. It's like it's not retaining the password...

Yes, that's why I suggested option 1. If you set an expression on the connect sting propert of the connection manager, then it will evaluate this expression when it is run. This is meant for you to dynamically change things, but here, I am suggesting it as a workaround for this problem. I suspect that it is due to the package protection level of the package, but you did not give me this info, so I could not be sure. You can test it outside the container, but I would be tempted to test it inside, or you may spend ages troubleshooting the stand alone, just to find a new set of issues when you put it inside the container. Although, if they are both using the same connection manager, this should not (in theory) make a difference.Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/

I've got the following settings ForEachLoop Container Collection Enumerator: Foreach File Enumerator Folder: C:\OUTPUT Files: *.* Fully Qualified Variable Mappings Variable: User::NextFileToFTP Index: 0 FTP Task (inside the container above) Remote Parameters IsRemotePathVariable: False RemotePath: /comtest OverwriteFileAtDest: False Local Parameters IsLocalPathVariable: True LocalVariable: User::NextFileToFTP Operation Operation: Send files IsTransferAscii: False The C:\OUTPUT has six files sitting in it now. I rightclick in the container and select Execute Container and it turns yellow and then bam it's red. Not really seeing when the container is setting the User::NextFileToFTP variable Sorry to be dumb as a rock but i'm not understanding...

Thanks Peter, There will be six filename which will always have the same six different names except for the datetimestamp which I'm keeping in a user variable so the filename are like below: "StorProd1_" + @Timestamp + ".txt" "StorProd2_" + @Timestamp + ".txt" "StorProd3_" + @Timestamp + ".txt" "Prod4_" + @Timestamp + ".txt" "Prod5_" + @Timestamp + ".txt" "Prod6_" + @Timestamp + ".txt" They are all in an output folder C:\OUTPUT\ After successful FTP I'll delete all files in the directory. I figured the ForEachLoop could just loop through each file in the directory. I'm confused as to how I "pass a fully qualified file name into a variable. " I know how to do Foreachloops in VB.NET but it's probably too easy here and thus it's hard for me to figure out. In The ForEach Loop editor I've set the Enumerator Configuration to Folder: C:\OUTPUT\ And files: *.* and Retrieve file name to option Fully Qualified ********************************* What am i missing?

Your not being dumb, and there is nothing blatently wrong with your settings. The value of the variable is set at the beggining of each itteration of the loop. When the container goes red, what error do you get in the progress tab/output window?Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/

Thanks Peter!! That worked!! I really do appreciate you sticking with me on this. Harry

Pete The script task is actually working!! Yippie!! And the container runs the script task 6 times one for each file in the output directory - course it only sends the one file 6 times. Using the files(0) = "C:\CLM OUTPUT\TEST_STORE_GROUPS_20110210140511.txt" hardcoded. This one file gets transfered My variable is called NextFileToFTP with a scope of the ForEach Loop Container. I've only got one other user variable. Found the Dts.Variables collection but it seems to only take an index or item which only takes an index for the item. How do I change the line below to point to the loop changing NextFileToFTP variable. I even tried files(0) = "C:\CLM OUTPUT\ " & Dts.Variables("User::NextFileToFTP").Value.Tostring but not files get transfered....

Hi Light. Firstly, fantastic news that the script task is working! Secondly, make sure you pass your variable into the ReadOnlyVariables in the Script tab of the Script task. Third, try this code... Dim FileNameToUpload As String = Convert.ToString(Dts.Variables("User::NextFileToFTP").Value) Dim files(0) As String files(0) = FileNameToUpload ftp.SendFiles(files, "/", True, False) Let me know how you get on...!Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/