How to make SSIS 2005 work like DTS 2000?

Hi!
Is it possible to make data flow in SQL Server 2005 Integration Services
looks like in SQL Server 2000 DTS?
I need to acive 2 goals:
1. If bcp fails on INSERT statement, for example, on primary key validation,
it must go on to the next row, but not to break SQL threat and stop.
2. I need log for failed errors. Not the log about which process failed, but
just the error source line.
And the final question: in SQL Server 2000 it was no probem to do it, so why
it has been removed from SQL Server 2005?
Thank you
Igor A. ChechetHi,
Igor A. Chechet wrote:
> Is it possible to make data flow in SQL Server 2005 Integration Services
> looks like in SQL Server 2000 DTS?
> I need to acive 2 goals:
> 1. If bcp fails on INSERT statement, for example, on primary key validation,
> it must go on to the next row, but not to break SQL threat and stop.
> 2. I need log for failed errors. Not the log about which process failed, but
> just the error source line.
>
your goals are easy to achieve with SIIS, too:
You have to open the editor of the task producing the error and
hit the "configure error output" button. There you have three
options:
- fail component
default, causes the package to stop
- ignore error
errors are ignored (see goal 1)
- redirect row
data is redirected to the error output (red arrow)
you can attach another Data Flow Destination component here
to write the failed rows to another table (see goal 2)
In the books online there's a tutorial for SIIS which shows the
most important features like logging, variables and configuration
files.
HTH,
Gerald|||Gerald, thank you for your advise!
I think that your suported method will help, but the problem is that the
error occured on the SQL Server Destination Task, and that's not possible to
create error output for it. BCP fails always on this task. Any ideas how to
handle it?
Thank you
Igor A. Chechet
"Gerald Aichholzer" <gerald.aichholzer@.gmx.net> '?/'' ? ''
'?: news:eYFYlFRpGHA.504@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Igor A. Chechet wrote:
>> Is it possible to make data flow in SQL Server 2005 Integration Services
>> looks like in SQL Server 2000 DTS?
>> I need to acive 2 goals:
>> 1. If bcp fails on INSERT statement, for example, on primary key
>> validation, it must go on to the next row, but not to break SQL threat
>> and stop.
>> 2. I need log for failed errors. Not the log about which process failed,
>> but just the error source line.
> your goals are easy to achieve with SIIS, too:
> You have to open the editor of the task producing the error and
> hit the "configure error output" button. There you have three
> options:
> - fail component
> default, causes the package to stop
> - ignore error
> errors are ignored (see goal 1)
> - redirect row
> data is redirected to the error output (red arrow)
> you can attach another Data Flow Destination component here
> to write the failed rows to another table (see goal 2)
> In the books online there's a tutorial for SIIS which shows the
> most important features like logging, variables and configuration
> files.
>
> HTH,
> Gerald|||Hi Igor,
Igor A. Chechet wrote:
> Gerald, thank you for your advise!
> I think that your suported method will help, but the problem is that the
> error occured on the SQL Server Destination Task, and that's not possible to
> create error output for it. BCP fails always on this task. Any ideas how to
> handle it?
>
I'm not quite sure about this (I started with SIIS only two weeks ago),
but AFAIR you have to disable bulk insert to be able to create error
output. I think you have to select "table or view" (without "fast load")
in "data access mode".
HTH,
Gerald|||to log the rows in error, you have to disable the fast load option (like in
SQL 2000)
but this greatly slowdown the loading process.
regarding the size of the file you have to load, it could be bettter to load
the file into a temporary table (with no PK constraint)
then execute some SQL statement to identify duplicated rows and remove these
duplicated (or moving these rows into another table)
another option is to try to play with the sort task in the dataflow (there
is an option to remove duplicated rows)
or the aggregate flow to count the rows in double, then split the stream
into 2 streams (1 with unique rows (count=1), 1 with duplicated rows
(count>1))
finally the stream 1 with the unique rows will be inserted into the
destination using the fast load option and duplicated rows moved to the
error log table.
and another option...
simply insert the rows using the fast load option and use only 1 transaction
(no batch size)
if the insertion failed, then load again the rows but with a destination
where the fastload option is disabled and where the error output can be
used.
(solution not tested)
"Igor A. Chechet" <ichechet@.mail.ru> wrote in message
news:eMksqaPpGHA.4424@.TK2MSFTNGP05.phx.gbl...
> Hi!
> Is it possible to make data flow in SQL Server 2005 Integration Services
> looks like in SQL Server 2000 DTS?
> I need to acive 2 goals:
> 1. If bcp fails on INSERT statement, for example, on primary key
> validation, it must go on to the next row, but not to break SQL threat and
> stop.
> 2. I need log for failed errors. Not the log about which process failed,
> but just the error source line.
> And the final question: in SQL Server 2000 it was no probem to do it, so
> why it has been removed from SQL Server 2005?
> Thank you
> Igor A. Chechet
>sql