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

How to move file in archive folder when package failed in ssis. Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 1:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 11:40 PM
Points: 11, Visits: 53
Hi All,

I have one scenario I create one package in which file is coming at daily basis and data is load in table but in case bad data will come and it fails to load data in table in that case file should be move at error archive folder and that source location will be free for next file. it get copy but i want to move file permanently at error archive folder when package get failed .
So please anybody look into this..

Thanks in Advance..
Post #1465488
Posted Thursday, June 20, 2013 2:23 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:53 AM
Points: 702, Visits: 2,178
Hi,

If you're already copying the file then can't you just change the file system task to complete a "Move File" operarion rather than a "Copy File" one?

Or have i mis-understood your problem?

Thanks,


Simon




MCSA: SQL Server 2012
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1465507
Posted Thursday, June 20, 2013 2:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 11:40 PM
Points: 11, Visits: 53
actually it hold the file so moving file is not possible but copying is done but we have to move it.
Post #1465521
Posted Thursday, June 20, 2013 2:55 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:53 AM
Points: 702, Visits: 2,178
Is the file system task in an event handler, or is it a separate task on the control flow?

Thanks,


Simon




MCSA: SQL Server 2012
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1465524
Posted Thursday, June 20, 2013 3:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 11:40 PM
Points: 11, Visits: 53
it is a separate task on control flow...
Post #1465536
Posted Thursday, June 20, 2013 3:27 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:53 AM
Points: 702, Visits: 2,178
OK so try doing a "Move File" file system task in an OnError event handler on the data flow.

Thanks,


Simon




MCSA: SQL Server 2012
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1465537
Posted Friday, June 21, 2013 5:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 11:40 PM
Points: 11, Visits: 53
Could you please elaborate the steps what you have try to saying.
Post #1466143
Posted Friday, June 21, 2013 6:31 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:53 AM
Points: 702, Visits: 2,178
Hi,

So i've included a screenshot....

There should be an event handlers tab when you open the package. On there you can select an event handler for a particular executable, or the entire package. Select you data flow in the executable drop down, then all you have to do is create a "Move File" file system task.

Hope that helps a bit more.

Thanks,


Simon




MCSA: SQL Server 2012
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne


  Post Attachments 
EventHandler.png (13 views, 13.79 KB)
Post #1466156
Posted Friday, June 21, 2013 8:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 11:40 PM
Points: 11, Visits: 53
Its working now ..

Thanks
Post #1466210
Posted Friday, June 21, 2013 10:18 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:53 AM
Points: 702, Visits: 2,178
Great!



MCSA: SQL Server 2012
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1466298
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse