SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS package SQL Tasks - Renaming processed files


SSIS package SQL Tasks - Renaming processed files

Author
Message
sumon.mukherjee
sumon.mukherjee
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 57
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
Dave Morrison
Dave Morrison
SSC Eights!
SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)

Group: General Forum Members
Points: 863 Visits: 248
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
sumon.mukherjee
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 57
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
Dave Morrison
SSC Eights!
SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)

Group: General Forum Members
Points: 863 Visits: 248
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
Phil Parkin
SSC Guru
SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)

Group: General Forum Members
Points: 108510 Visits: 22255
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Dave Morrison
Dave Morrison
SSC Eights!
SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)

Group: General Forum Members
Points: 863 Visits: 248
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
Phil Parkin
SSC Guru
SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)

Group: General Forum Members
Points: 108510 Visits: 22255
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
BogdanB
BogdanB
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 29
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search