Badfile creation

[topherdel]

I am wanting to duplicate the functionality of oracle sqlloader badfile. When loading a flat file, if a row in the flat file fails to load, the row is written to another flatfile called the bad file. After the loader is complete, the bad file should contain all the rows from the input file that did not load into the database.

With SSIS, a simple data flow would consist of a flat file input and an ole db command task. I am able to use the error output of the flat file input task to send the failed rows read to another flat file. However, if i try the same thing on error output of the ole db command task to the same flat file, it tells me the flat file is locked and cannot be opened. This is because the first error task must still have the file open. Is there a way to create a "badfile" if anything errors in the data flow? Preferably without having to create error tasks for each task in the data flow.

 

[mrdenny]

No, you have to create error tasks for each data pump task.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[MDXer]

Maybe I am missing something but why are you using an OLE DB Command task to load records?

 

[topherdel]

I appreciate the information mrdenny.

The reason i am using the Ole DB task is because I need to issue an update statement. Actually, it is an Ole DB Statement transformation within a dataflow task.