Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS package SQL Tasks - Renaming processed files Expand / Collapse
Author
Message
Posted Monday, March 29, 2010 10:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 13, 2010 4:02 AM
Points: 7, 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
Post #892446
Posted Tuesday, March 30, 2010 7:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 3:48 AM
Points: 92, Visits: 201
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
Post #892726
Posted Tuesday, March 30, 2010 7:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 13, 2010 4:02 AM
Points: 7, 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
Post #892755
Posted Tuesday, March 30, 2010 7:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 3:48 AM
Points: 92, Visits: 201
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
Post #892760
Posted Tuesday, March 30, 2010 8:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 5,083, Visits: 11,865
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #892833
Posted Tuesday, March 30, 2010 9:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 3:48 AM
Points: 92, Visits: 201
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!?
Post #892907
Posted Tuesday, March 30, 2010 9:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 5,083, Visits: 11,865
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!?


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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #892918
Posted Tuesday, December 6, 2011 2:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 11, 2012 12:16 PM
Points: 1, 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?
Post #1217434
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse