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 validatio
n,
> 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, b
ut
> 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:
> 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 t
o
> 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
>

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

How to make input columns unavailable for downstream components?

Hi,

In a SSIS Data flow task, whatever you are doing with the input columns that come from a Data source like a Flat File Source, these columns are always visible and available as input columns for all Transformation and Destination components in the Data flow.

Our Custom Component is a column mapping component that transforms many input columns into many oputput columns and we would like that the used input columns are not available anymore to the downstream components...

Is that possible?

I saw that with the Unpivot component it is possible to make some input columns unavailable to downstream components...So I think there is a way to do the same in a custom component...

Thanks for any help,

David

David-Paris wrote:

Hi,

In a SSIS Data flow task, whatever you are doing with the input columns that come from a Data source like a Flat File Source, these columns are always visible and available as input columns for all Transformation and Destination components in the Data flow.

Our Custom Component is a column mapping component that transforms many input columns into many oputput columns and we would like that the used input columns are not available anymore to the downstream components...

Is that possible?

I saw that with the Unpivot component it is possible to make some input columns unavailable to downstream components...So I think there is a way to do the same in a custom component...

Thanks for any help,

David

If the component you are building is a synchronous component then the rows will be available downstream. That is simply inherent in the nature of the data-flow.

Unpivot is an asynchronous component (just like Merge Join, Union All, Sort etc...). You can think of asynchronous as meaning that the "shape" of the data changes when it goes through the component (that's not really what it means but for simplicity - it works).

You can make your custom component asynchronous if you want but there isn't much point - it will degrade performance.

-Jamie

How to load data into a table using a synonym?

I'd like to use a data flow task to load data into a table by specifying the synonym name of the destination table, instead of the actual table name.

The OLE DB Destination is forcing me to pick an actual table or view from a drop down list. Any ideas on how to get around this?

Thank you.

This will be fixed in SP1, so that synonyms also appear in the UI in the drop down list. For RTM, the workaround is to use SqlCommand access mode, and then if you specify "select * from synonymTable", that will work.

|||

Using a SELECT statement (which is what Ranjeeta has said) is what you should be doing anyway - don't use the drop-down.

Simon explains why here: http://sqljunkies.com/WebLog/simons/archive/2006/01/20/17865.aspx

-Jamie

|||Jamie,

Is that your recommendation even for OLE DB Destinations? I can

certainly understand why you would recommend this for Sources/Lookups,

but for a destination it seems odd because you lose some options

(fast-load, keep identity, check constraints,etc) when using a SQL

statement.

Also do know or have experienced the "feature" or "bug" that Simon describes as being applicable to the OLE DB Destination?

Thanks,

Larry Pope|||

Larry,

No, I was talking about source adapters only.

-Jamie

how to know status of previous control flow

Hi,

I have a package which has 3 data flows and a script component to write log file. Those 3 data flows are in sequence (mean upon success, next will run). If the first 2 data flows fails, control will go to script and upon completion of 3rd, control will go to script to create log file.

Now, my question is, within my script component, is there any way to find out what was the last ran data flow and its status? What I know is, adding a 'activex task' between each data flow and script component which updates a variable and then I can use that variable in the script component to know the last ran data flow. But what I want to know is, is there any system variable (or something) which will do the samething without any need to add additional task?

Thx.

Where you say script component I assume you mean script task? (the two are different you see)

There is no way of knowing what the previous task is unless you tell it. You are proposing to do this with an ActiveX Script task. I recommend you don't do that as the are only there for backward compatibility. Use a script task instead.

-Jamie

|||

If you mean a script task, you can do this as follows:

Create a string variable at the scope of the package - call it something like LastTask. http://msdn2.microsoft.com/en-us/library/ms141670(SQL.90).aspx

Create a sequence container and build your control flow inside the sequence - you can drag in existing objects, or copy and paste them in. http://msdn2.microsoft.com/en-us/library/ms139855.aspx

Create an OnPostExecute EventHandler for the sequence container. http://msdn2.microsoft.com/en-us/library/ms139744(SQL.90).aspx

Add a Script Task to the EventHandler. Configure the Script Task to use your LastTask variable as a ReadWrite variable and the system variable SourceName. (For detailed info on using variables with the script task see http://msdn2.microsoft.com/en-us/library/ms135941.aspx)

In the ScriptTask you could use the following line to set LastTask to the name of the last task to execute within the sequence ...

Dts.Variables("LastTask").Value = Dts.Variables("SourceName").Value

If you need to insert this value into a data flow, you could use the DerivedColumn component to insert the value of LastTask into a new column in each row passing.

hth

Donald

|||

Hi Donald,

Thx for giving me the perfect solution. I have couple of questions to that. The above method will tell me which is the last task. But how do I know the status of the task? Also in case of failure how do I know the error message?

Thx again.