Click here to monitor SSC
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
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: 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-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 242
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
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: 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-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 242
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10111 Visits: 19832
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-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 242
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10111 Visits: 19832
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
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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