SSIS package SQL Tasks - Renaming processed files

Question

SSIS package SQL Tasks - Renaming processed files

sumon.mukherjee

SSC-Addicted

Points: 487
More actions
March 29, 2010 at 10:35 pm

#139057

Hi All,
I am working on a SSIS package which does the following -
1) Read data from an excel file placed in a shared location
2) Load the data in the database tables
3) once the data is successfully loaded, send an email notification and then rename the excel file as "Processed_Excel".
I have been successful in the 1st 2 steps and the 3rd one partially, but unable to figure out the part ie, rename the excel file to "Processed_Excel".
Moreover, is there any way of Exception Handling like "File Not Found". (For eg: in the 1st step if the excel file is not present in the shared location, then an exception is thrown and the execution of the package is terminated).
Thanks in advance.
Cheers,
Sumon

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

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

Dave Morrison SSCrazy Points: 2077 More actions · Answer 1

Hi Sumon,

There is a component on the control flow called "file system task". here you can set rename file as the action and go from there

If you go to the event handlers you can create an OnError event task for this here

Hope this helps

Thanks

sumon.mukherjee SSC-Addicted Points: 487 More actions · Answer 2

Hi David,

Thanks a lot for your suggestion.

I have been able to figure out how to use the File System Task to rename a file. This link helped me a lot http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html

Now I am stuck with the following problem -

I am trying to put a check in the 1st step of my package whether the file exists or not. If it exists, it should continue with the normal execution otherwise it should exit the package gracefully. I am stressing more on the graceful exit because I am running this package from a database job. And on failure of the package as it sends the failure notification to a whole group, i do not want the users to panic.

I am trying to do this using the File System Task again. Digging through this forum, i found suggestions that using the Set Attributes operation can help me to handle the error. But I am unable to figure out exactly how to do that. Do you think the OnError event handler of the File System Task can do the trick.

Any help on this will be highly appreciated.

Cheers,

Sumon

Dave Morrison SSCrazy Points: 2077 More actions · Answer 3

sumon.mukherjee (3/30/2010)
Hi David,
Thanks a lot for your suggestion.
I have been able to figure out how to use the File System Task to rename a file. This link helped me a lot http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html
Now I am stuck with the following problem -
I am trying to put a check in the 1st step of my package whether the file exists or not. If it exists, it should continue with the normal execution otherwise it should exit the package gracefully. I am stressing more on the graceful exit because I am running this package from a database job. And on failure of the package as it sends the failure notification to a whole group, i do not want the users to panic.
I am trying to do this using the File System Task again. Digging through this forum, i found suggestions that using the Set Attributes operation can help me to handle the error. But I am unable to figure out exactly how to do that. Do you think the OnError event handler of the File System Task can do the trick.
Any help on this will be highly appreciated.
Cheers,
Sumon

Hi Summon,

No worries on the post. I'll be honest I'm not a total expert on this but my approach would maybe to use a script component at the start of the pack to check the file exists using some VB script and disable the import components if it doesnt (you can access the properties of the objects in the vb script I beleive)

This is obviously a bit of a bodge and relies on you having some VB knowledge. All I can suggest other than that is play with the event handlers and the import object properties. I'm sure I've seen an option somewhere to not report errors on failure.

Good luck!

Dave

Phil Parkin SSC Guru Points: 246962 More actions · Answer 4

There is another option which does not require script. Put the whole process inside a FOREACH container. When the package executes, if the FOREACH container does not find a matching file, the package completes gracefully.

If the specified source file is found, processing continues as required.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Dave Morrison SSCrazy Points: 2077 More actions · Answer 5

Phil Parkin (3/30/2010)
There is another option which does not require script. Put the whole process inside a FOREACH container. When the package executes, if the FOREACH container does not find a matching file, the package completes gracefully.
If the specified source file is found, processing continues as required.

Phil, are you eStalking me!? 😉

Phil Parkin SSC Guru Points: 246962 More actions · Answer 6

david.morrison-1035652 (3/30/2010)
Phil Parkin (3/30/2010)
There is another option which does not require script. Put the whole process inside a FOREACH container. When the package executes, if the FOREACH container does not find a matching file, the package completes gracefully.
If the specified source file is found, processing continues as required.
Phil, are you eStalking me!? 😉

:alien: be afraid. SSIS predators are not to be taken lightly.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

BogdanB Grasshopper Points: 21 More actions · Answer 7

That works on files that have already predefined name.

But what if I want to create the file name from current date and that file name does not exist yet?