Importing data using OLE DB Destination Task

Question

Importing data using OLE DB Destination Task

f.khan

SSC Rookie

Points: 28
More actions
July 29, 2010 at 3:39 am

#148275

Hi,
Im having a problem where i am trying to load a text file (csv) into a SQL Server 2005 Database.
This file has a problem in row 306. I have a OLE DB Destination Task which does the importing part, however it will import 305 rows, and then fail the import.
Is there any way to make it not import any rows, or roll back what it has already imported when it throws an error.
Background.
I have 10 files i want imported, so i am using a for each loop, to go though each file and try to import it into SQL Server database table.
The loop, checks to see file exists, if it exists, uses flat file source task to get the file, a derived column task to reformat teh data a little, then an OLE DB Destination task to import the data.
If any Flat file source, Derived column or OLE DB destination fails, it will move to the next task.
If any of these fail,the package returns Fail on completion. is there any way to prevent this?
Thanks in adavance

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

If you open the OLE DB Destination component dialog, there is a selection called "Error Output". The default is to fail the component, in case of errors. You can change to "Redirect row" and then insert the error row into appropriate table. You can then check this table and take the required action.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

f.khan SSC Rookie Points: 28 More actions · Answer 2

Thanks for the reply Cozy.

I was hopeing to totaly abort the import if there was a problem rather then just redirect the error rows. So if there is a problem with the file it is trying to import it should not import anything at all.

I would have thought this would be possible but cant see an option or it.

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

f.khan (8/2/2010)
Thanks for the reply Cozy.
I was hopeing to totaly abort the import if there was a problem rather then just redirect the error rows. So if there is a problem with the file it is trying to import it should not import anything at all.
I would have thought this would be possible but cant see an option or it.

Yes, it is possible. Have the load process inside transaction and in case you have issues with some records, the entire load will be aborted.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

f.khan SSC Rookie Points: 28 More actions · Answer 4

i'm not sure what you mean there Cozy.

Currently the load process is inside a dataflow task.

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

f.khan (8/2/2010)
i'm not sure what you mean there Cozy.
Currently the load process is inside a dataflow task.

Are you familiar with the database transactions?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

f.khan SSC Rookie Points: 28 More actions · Answer 6

the instructions for a database to execute a job?

I dont have a lot of experience in database design and SQL Server and how it works behind the scenes. I am currently using SQL server 2005, and have only recently started using it.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 7

Have a look here[/url].

You should be able to set the TransactionOption property of your DF to 'Required' to get the behaviour you require.

COZYROC One Orange Chip Points: 28499 More actions · Answer 8

f.khan (8/2/2010)
the instructions for a database to execute a job?
I dont have a lot of experience in database design and SQL Server and how it works behind the scenes. I am currently using SQL server 2005, and have only recently started using it.

I would recommend you find a good book about relational databases. The transactions are very important part . In simple terms the transaction process allows you to send a set of commands to the database and the engine will make sure all these commands are either committed "as one" or no change takes place.

Perhaps other forum readers can suggest good books about the topic?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/