how to treat truncations as a warning

I'm using SSIS to migrate data from one system to another. This is a usual extract, transform, cleanse and load type task.

The error handling is critical to get right. E.g. truncation of data on one column should stop that row being loaded but for other columns I might be happy to carry on loading the row but record a warning.

I'm finding the error disposition a bit limiting. I really feel the need for an 'Issue Warning' disposition which will act the same way as 'Ignore Error' in that the row continues being processed but will in addition copy a row to a warning output so that I can write a message to a log file for someone to manually investigate and correct that item of data post the conversion. Alternatively it would be useful to specify a severity (at a column level) when redirecting error output. This way I can put logic into a downstream component which would treat the error row differently depending on the severity of the error.

Am I missing a trick?

There's no built-in switch for enabling this behavior, but you can accomplish it with creative use of error redirection. I use error redirection or a conditional split to identify rows that either cause warnings or errors, flag them appropriately, then send the errors to a logging table. I send the warnings to a multicast that outputs the rows to both a logging table and to a Union All to put them back into the main flow.|||this will work for me, though it's a shame there isn't a built-in feature...|||

Nick Corrie wrote:

this will work for me, though it's a shame there isn't a built-in feature...

Nick,

That could be a good suggestion to make; you can post it at the connect site: http://connect.microsoft.com/VisualStudio/Feedback

How to translate into SSIS?

Hi everyone,

My current dutie is translate Vb code into SSIS. The following structure is getting me totally crazy:

sql = "SELECT * FROM TABLE1 WHERE FIELD = XXXX"

rs.execute sql

WHILE NOT RS.EOF

STUFF

ANOTHER SELECT AND ANOTHER LOOP

...

...

INSERT AND DELETE STUFF

END WHILE

Which is the best method in order to reach this goal? I'm trying to by means of OLEDB Source Editor connected to Script Component Task on data flow layer but I'm stuck with this.

I don't want to do cursors or something like that with T-SQL.

Thanks in advance for your inputs/help and regards,

Could you use nested FOREACH containers? Store your initial resultset in a variable, then iterate through that, executing the second SQL statement, storing the results in a variable, and using a second FOREACH to iterate that.|||

hi,

using ForEach Loop container on control flow, you mean?

should I use ForEach ADO enumerator, shouldn't?

tia

|||Yes, control flow. Yes, ADO recordset.|||Thank you. I'll test it|||

It's easier than I though. I'll use a Script Task on Control Flow task. Just that task,

I don't need nothing else

|||Could you share the script? Stripped of anything specific to your business, of course.

How to translate into SSIS?

Hi everyone,

My current dutie is translate Vb code into SSIS. The following structure is getting me totally crazy:

sql = "SELECT * FROM TABLE1 WHERE FIELD = XXXX"

rs.execute sql

WHILE NOT RS.EOF

STUFF

ANOTHER SELECT AND ANOTHER LOOP

...

...

INSERT AND DELETE STUFF

END WHILE

Which is the best method in order to reach this goal? I'm trying to by means of OLEDB Source Editor connected to Script Component Task on data flow layer but I'm stuck with this.

I don't want to do cursors or something like that with T-SQL.

Thanks in advance for your inputs/help and regards,

Could you use nested FOREACH containers? Store your initial resultset in a variable, then iterate through that, executing the second SQL statement, storing the results in a variable, and using a second FOREACH to iterate that.|||

hi,

using ForEach Loop container on control flow, you mean?

should I use ForEach ADO enumerator, shouldn't?

tia

|||Yes, control flow. Yes, ADO recordset.|||Thank you. I'll test it|||

It's easier than I though. I'll use a Script Task on Control Flow task. Just that task,

I don't need nothing else

|||Could you share the script? Stripped of anything specific to your business, of course.

How to translate DTSStepScriptResult_DontExecuteTask into SSIS?

Hi everyone,

We're struggling ourselves with this and we are stuck.

Which is the equivalent for a SSIS in a Script Task component on CONTROL FLOW layer?

Main = DTSStepScriptResult_ExecuteTask

Thanks for your input and regards,

Set the script result to a variable and then use that variable in a precedence constraint on the Execute XXX Task.|||

To try and expand on Phil's reply. The constant you mention does not exist, because you no longer have workflow scripts as you did in DTS. What you do have is a much richer set of precedence constraint functionality. As well as having the normal succes and failure stype stuff, you can also set an expression on the constraint. The expression uses a new syntax, which is quite simple, but maybe you could replace your script logic with an expression. Your logic may be too complicated for this, so you would have to use an Script Task and from that your result in variable. The variable can easily be referenced in an expressionon the constraint.

I would avoid any ActiveX Script in SSIS, use the new features, including the Script Task if required. The support for ActiveX is not good, things like error information are very poor now.

How to stop a SSIS package execution from code?

Hi everyone,

After a Execute method I would need to stop a package but I don't know why:

sResultDts = pkg.Execute(Nothing, Nothing, EventsSSIS, Nothing, Nothing)

I have a Events class (EventSSIS) which implements IDTSEvents and have an OnQueryCancel event but without parameters, such so:

Function OnQueryCancel() AsBooleanImplements IDTSEvents.OnQueryCancel

ReturnFalse

EndFunction

Let me know how to pass a boolean parameter because of I can't overloaded OnQueryCancel method

TIA-

I'm sorry, issue is solved.

happy coding,