FTP Task in SSIS

Hello,

I m having an FTP Task in SSIS project, from where I have downloaded .csv file.

And also having a Processed Folder in which the processed .csv files are kept.

Main task is to download file from FTP and check , if the date of downloaded file is newer than file in processed folder, then only process that file. Else skip the processing

Can anyone help me out how to process about.

plz reply as early as possible.

Thanks & Kind regards ....

Aparna

I do not know if this is the best way to do this, but I would personally use the Foreach Loop container and the Script task. Loop over the folder into which the FTP task is dropping files, storing the file name in a package variable.

Then, inside a Script task contained within the Foreach Loop, use the System.IO.File class to compare the files retrieved by the FTP task with the matching files in the processed folder. Based on the comparison, set a package variable flag ("Newer"/"Older" or true/false or the like) in your script.

Then, back in your package's control flow, edit the properties of your precedence constraint(s) to execute the later tasks only if the flag variable is set to the correct value.

Does this help?

sql

FTP Task corrupts file

I have an ftp task that grabs files from a remote dir ( *.csv ). However, it seams that the ftp task is corrupting some of my files. Has anyone else seen this? Is there something I can do? (These are grabbed as binary)

For example, here is the original on the remote server:

25316,<ACTUAL>,296,917,48,10,1,2006-03-29,UPLOADER
25319,<ACTUAL>,63060,106,64,10,1,2006-03-29,UPLOADER
25300,<ACTUAL>,63060,206,64,10,1,2006-03-29,UPLOADER
29743,<ACTUAL>,56060,106,96,10,1,2006-03-29,UPLOADER
29744,<ACTUAL>,56060,206,96,10,1,2006-03-29,UPLOADER
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER

But here is the result on the local machine. You can see that the last 4 lines are duplicated (plus the last character of the preceding line):

25316,<ACTUAL>,296,917,48,10,1,2006-03-29,UPLOADER
25319,<ACTUAL>,63060,106,64,10,1,2006-03-29,UPLOADER
25300,<ACTUAL>,63060,206,64,10,1,2006-03-29,UPLOADER
29743,<ACTUAL>,56060,106,96,10,1,2006-03-29,UPLOADER
29744,<ACTUAL>,56060,206,96,10,1,2006-03-29,UPLOADER
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER
R
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER

Looks like the task does not delete the existing file, so if you had a bigger file before (probably downloaded with previous package run), the task overwrites the start of the file with new information, but leaves the data at the end of the file. You can workaround this by deleting the file(s) at the destination before downloading new files.

I'll enter a bug on this.

|||

Makes sense. I will use your recommendation of deleting files before downloading the new ones.

Thanks!

|||

I'm having a simlar problem.

These are the first 10 records of a CSV uploaded from an IIS server to a z/OS machine.

119447,20060907,15360,008-0347-00,04,10854
119457,20060907,15360,008-0393-00,04,10854
119447,20060907,7680,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,10854
119457,20060808,15360,008-0393-00,04,10854
119450,20060808,7680,008-0386-00,04,10854
119442,20060808,15360,008-0340-00,04,10854
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31,12443

These are the output records produced in z/OS.

119447,20060907,7680,008-0347-00,04,10854
119447,20060907,15360,008-0347-00,04,
119447,20060907,15360,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,
119453,20060907,7680,008-0390-00,04,10854
119457,20060907,15360,008-0393-00,04,
119457,20060907,15360,008-0393-00,04,10854
119442,20060808,15360,008-0340-00,04
119442,20060808,15360,008-0340-00,04,10854
119450,20060808,7680,008-0386-00,04,
119450,20060808,7680,008-0386-00,04,10854
119457,20060808,15360,008-0393-00,04,
119457,20060808,15360,008-0393-00,04,10854
120101,20060808,25600,019-0061-00,31
120101,20060808,25600,019-0061-00,31,10912
167747,20060808,15000,031-0052-00,31
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31

It appears that the input stream is not being handled correctly. I'm creating a file of 80 byte records. Trying to create a VarBlocked file gives same result.

Any suggestions?

|||

wndii wrote:

I'm having a simlar problem.

These are the first 10 records of a CSV uploaded from an IIS server to a z/OS machine.

119447,20060907,15360,008-0347-00,04,10854
119457,20060907,15360,008-0393-00,04,10854
119447,20060907,7680,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,10854
119457,20060808,15360,008-0393-00,04,10854
119450,20060808,7680,008-0386-00,04,10854
119442,20060808,15360,008-0340-00,04,10854
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31,12443

These are the output records produced in z/OS.

119447,20060907,7680,008-0347-00,04,10854
119447,20060907,15360,008-0347-00,04,
119447,20060907,15360,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,
119453,20060907,7680,008-0390-00,04,10854
119457,20060907,15360,008-0393-00,04,
119457,20060907,15360,008-0393-00,04,10854
119442,20060808,15360,008-0340-00,04
119442,20060808,15360,008-0340-00,04,10854
119450,20060808,7680,008-0386-00,04,
119450,20060808,7680,008-0386-00,04,10854
119457,20060808,15360,008-0393-00,04,
119457,20060808,15360,008-0393-00,04,10854
120101,20060808,25600,019-0061-00,31
120101,20060808,25600,019-0061-00,31,10912
167747,20060808,15000,031-0052-00,31
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31

It appears that the input stream is not being handled correctly. I'm creating a file of 80 byte records. Trying to create a VarBlocked file gives same result.

Any suggestions?

Is this the same issue? Are you using an FTP task? I'm not seeing the whole process here.|||

Is this enough? I am told that server is IIS. It certainly appears to me we are running WS_FTP (Ipswitch). Does that run under IIS? I've tried SITE TRAIL and SITE mode but server doesn't accept them.

Also, CSV file is being created using SAS, in case that's pertinent.

I appreciate your help on this.

220 securetxr.emersonclimate.com X2 WS_FTP Server 5.0.3 (1359104614)
EZA1701I >>> USER ZOSTEST
331 Password required
EZA1701I >>> PASS
230 user logged in
EZA1460I Command:
EZA1736I ASCII
EZA1701I >>> TYPE A
200 Type set to ASCII.
EZA1460I Command:
EZA1736I get poeta.csv //DD:FTPU1
EZA1701I >>> PORT 155,*****************
200 command successful
EZA1701I >>> RETR poeta.csv
150 Opening ASCII data connection for poeta.csv (67678 bytes)
226 transfer complete
EZA1617I 67678 bytes transferred in 0.100 seconds. Transfer rate 676.78 Kbytes/sec.
EZA1460I Command:
EZA1736I QUIT
EZA1701I >>> QUIT
221-Good-Bye

|||

wndii wrote:

Is this enough? I am told that server is IIS. It certainly appears to me we are running WS_FTP (Ipswitch). Does that run under IIS? I've tried SITE TRAIL and SITE mode but server doesn't accept them.

Also, CSV file is being created using SAS, in case that's pertinent.

I appreciate your help on this.

220 securetxr.emersonclimate.com X2 WS_FTP Server 5.0.3 (1359104614)
EZA1701I >>> USER ZOSTEST
331 Password required
EZA1701I >>> PASS
230 user logged in
EZA1460I Command:
EZA1736I ASCII
EZA1701I >>> TYPE A
200 Type set to ASCII.
EZA1460I Command:
EZA1736I get poeta.csv //DD:FTPU1
EZA1701I >>> PORT 155,*****************
200 command successful
EZA1701I >>> RETR poeta.csv
150 Opening ASCII data connection for poeta.csv (67678 bytes)
226 transfer complete
EZA1617I 67678 bytes transferred in 0.100 seconds. Transfer rate 676.78 Kbytes/sec.
EZA1460I Command:
EZA1736I QUIT
EZA1701I >>> QUIT
221-Good-Bye

That's all well and good, but are you having a SQL Server Integration Services issue with respect to their FTP Task, or are you just generally having an FTP issue?sql

FTP Task corrupts file

I have an ftp task that grabs files from a remote dir ( *.csv ). However, it seams that the ftp task is corrupting some of my files. Has anyone else seen this? Is there something I can do? (These are grabbed as binary)

For example, here is the original on the remote server:

25316,<ACTUAL>,296,917,48,10,1,2006-03-29,UPLOADER
25319,<ACTUAL>,63060,106,64,10,1,2006-03-29,UPLOADER
25300,<ACTUAL>,63060,206,64,10,1,2006-03-29,UPLOADER
29743,<ACTUAL>,56060,106,96,10,1,2006-03-29,UPLOADER
29744,<ACTUAL>,56060,206,96,10,1,2006-03-29,UPLOADER
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER

But here is the result on the local machine. You can see that the last 4 lines are duplicated (plus the last character of the preceding line):

25316,<ACTUAL>,296,917,48,10,1,2006-03-29,UPLOADER
25319,<ACTUAL>,63060,106,64,10,1,2006-03-29,UPLOADER
25300,<ACTUAL>,63060,206,64,10,1,2006-03-29,UPLOADER
29743,<ACTUAL>,56060,106,96,10,1,2006-03-29,UPLOADER
29744,<ACTUAL>,56060,206,96,10,1,2006-03-29,UPLOADER
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER
R
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER

Looks like the task does not delete the existing file, so if you had a bigger file before (probably downloaded with previous package run), the task overwrites the start of the file with new information, but leaves the data at the end of the file. You can workaround this by deleting the file(s) at the destination before downloading new files.

I'll enter a bug on this.

|||

Makes sense. I will use your recommendation of deleting files before downloading the new ones.

Thanks!

|||

I'm having a simlar problem.

These are the first 10 records of a CSV uploaded from an IIS server to a z/OS machine.

119447,20060907,15360,008-0347-00,04,10854
119457,20060907,15360,008-0393-00,04,10854
119447,20060907,7680,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,10854
119457,20060808,15360,008-0393-00,04,10854
119450,20060808,7680,008-0386-00,04,10854
119442,20060808,15360,008-0340-00,04,10854
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31,12443

These are the output records produced in z/OS.

119447,20060907,7680,008-0347-00,04,10854
119447,20060907,15360,008-0347-00,04,
119447,20060907,15360,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,
119453,20060907,7680,008-0390-00,04,10854
119457,20060907,15360,008-0393-00,04,
119457,20060907,15360,008-0393-00,04,10854
119442,20060808,15360,008-0340-00,04
119442,20060808,15360,008-0340-00,04,10854
119450,20060808,7680,008-0386-00,04,
119450,20060808,7680,008-0386-00,04,10854
119457,20060808,15360,008-0393-00,04,
119457,20060808,15360,008-0393-00,04,10854
120101,20060808,25600,019-0061-00,31
120101,20060808,25600,019-0061-00,31,10912
167747,20060808,15000,031-0052-00,31
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31

It appears that the input stream is not being handled correctly. I'm creating a file of 80 byte records. Trying to create a VarBlocked file gives same result.

Any suggestions?

|||

wndii wrote:

I'm having a simlar problem.

These are the first 10 records of a CSV uploaded from an IIS server to a z/OS machine.

119447,20060907,15360,008-0347-00,04,10854
119457,20060907,15360,008-0393-00,04,10854
119447,20060907,7680,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,10854
119457,20060808,15360,008-0393-00,04,10854
119450,20060808,7680,008-0386-00,04,10854
119442,20060808,15360,008-0340-00,04,10854
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31,12443

These are the output records produced in z/OS.

119447,20060907,7680,008-0347-00,04,10854
119447,20060907,15360,008-0347-00,04,
119447,20060907,15360,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,
119453,20060907,7680,008-0390-00,04,10854
119457,20060907,15360,008-0393-00,04,
119457,20060907,15360,008-0393-00,04,10854
119442,20060808,15360,008-0340-00,04
119442,20060808,15360,008-0340-00,04,10854
119450,20060808,7680,008-0386-00,04,
119450,20060808,7680,008-0386-00,04,10854
119457,20060808,15360,008-0393-00,04,
119457,20060808,15360,008-0393-00,04,10854
120101,20060808,25600,019-0061-00,31
120101,20060808,25600,019-0061-00,31,10912
167747,20060808,15000,031-0052-00,31
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31

It appears that the input stream is not being handled correctly. I'm creating a file of 80 byte records. Trying to create a VarBlocked file gives same result.

Any suggestions?

Is this the same issue? Are you using an FTP task? I'm not seeing the whole process here.|||

Is this enough? I am told that server is IIS. It certainly appears to me we are running WS_FTP (Ipswitch). Does that run under IIS? I've tried SITE TRAIL and SITE mode but server doesn't accept them.

Also, CSV file is being created using SAS, in case that's pertinent.

I appreciate your help on this.

220 securetxr.emersonclimate.com X2 WS_FTP Server 5.0.3 (1359104614)
EZA1701I >>> USER ZOSTEST
331 Password required
EZA1701I >>> PASS
230 user logged in
EZA1460I Command:
EZA1736I ASCII
EZA1701I >>> TYPE A
200 Type set to ASCII.
EZA1460I Command:
EZA1736I get poeta.csv //DD:FTPU1
EZA1701I >>> PORT 155,*****************
200 command successful
EZA1701I >>> RETR poeta.csv
150 Opening ASCII data connection for poeta.csv (67678 bytes)
226 transfer complete
EZA1617I 67678 bytes transferred in 0.100 seconds. Transfer rate 676.78 Kbytes/sec.
EZA1460I Command:
EZA1736I QUIT
EZA1701I >>> QUIT
221-Good-Bye

|||

wndii wrote:

Is this enough? I am told that server is IIS. It certainly appears to me we are running WS_FTP (Ipswitch). Does that run under IIS? I've tried SITE TRAIL and SITE mode but server doesn't accept them.

Also, CSV file is being created using SAS, in case that's pertinent.

I appreciate your help on this.

220 securetxr.emersonclimate.com X2 WS_FTP Server 5.0.3 (1359104614)
EZA1701I >>> USER ZOSTEST
331 Password required
EZA1701I >>> PASS
230 user logged in
EZA1460I Command:
EZA1736I ASCII
EZA1701I >>> TYPE A
200 Type set to ASCII.
EZA1460I Command:
EZA1736I get poeta.csv //DD:FTPU1
EZA1701I >>> PORT 155,*****************
200 command successful
EZA1701I >>> RETR poeta.csv
150 Opening ASCII data connection for poeta.csv (67678 bytes)
226 transfer complete
EZA1617I 67678 bytes transferred in 0.100 seconds. Transfer rate 676.78 Kbytes/sec.
EZA1460I Command:
EZA1736I QUIT
EZA1701I >>> QUIT
221-Good-Bye

That's all well and good, but are you having a SQL Server Integration Services issue with respect to their FTP Task, or are you just generally having an FTP issue?