File system task fails randomly

Question

Post reply

File system task fails randomly

Pink123

Default port

Points: 1486
More actions
November 4, 2011 at 8:10 am

#142949

I am very new to SSIS .
I am using sql server 2005 and have a job which performs a file system task which
moves file from source to destination.
Sometimes say once a week I face a problem where the file is moved to the destination folder but is also there in the source folder.
Shouldnt this task move from source to destination ,so that we dont have the file at the source location once it is moved.
It happens randomly not always.
Can anyone suggest whats wrong with it.Is it some connection issue ?
I have no ideas whats causing it .
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

Stupid question... are you sure that someone isn't resubmitting a job or manually copying the file back?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 2

And another stupid question: maybe someone has the file opened or being used while your job is trying to delete it?

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

JasonYousef SSCommitted Points: 1769 More actions · Answer 3

codebyo (11/4/2011)
And another stupid question: maybe someone has the file opened or being used while your job is trying to delete it?
Best regards,

wouldn't SSIS yell and gives an error for that?

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 4

huslayer (11/5/2011)
wouldn't SSIS yell and gives an error for that?

Probably yes.

Have you tried to change the operation to "Copy File" instead of "Move File" and then have another File System Task to perform the delete?

If you debug the package inside BIDS does it show any errors?

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

Another question:

what operation is selected in the File System Task?

In my experience, the "Rename" operation works best for moving files.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Pink123 Default port Points: 1486 More actions · Answer 6

Thanks all for the replies.

Its like this the various steps are

1.Generate text file from .dat file(Vb script)

2.Take the txt file and insert data in database

3.Move the original .dat file to a different location

4.Delete the txt file

When the job fails I see the dat file in both source and destination folder

and the number of records inserted in database is not equal to the number of records in the txt file.

So is it possible that the proper insertion did not took place and that causs the move task to be affected or the file is still used for insertion and in the mean time it is being moved by SSIS.

How does SSIS decide that insertion is over now and it should move the file now.

Thanks,

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 7

Pink123 (11/10/2011)
Thanks all for the replies.
Its like this the various steps are
1.Generate text file from .dat file(Vb script)
2.Take the txt file and insert data in database
3.Move the original .dat file to a different location
4.Delete the txt file
When the job fails I see the dat file in both source and destination folder
and the number of records inserted in database is not equal to the number of records in the txt file.
So is it possible that the proper insertion did not took place and that causs the move task to be affected or the file is still used for insertion and in the mean time it is being moved by SSIS.
How does SSIS decide that insertion is over now and it should move the file now.

How do you move the file?

Is there are precedence constraint between that task and the task that transfers the data?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP