Forcing an error on a row in SSIS

I'm importing a CSV file into a database. The file includes a couple
of date columns. I've added a Data Conversion step in my Data Flow to
convert those columns. Unfortunately, SSIS does something which I
consider to be nothing short of horrible...

Some of the rows include typos in the data, so there are dates such as
"0505-04-21". No problem there, as I've got the error output
configured and I capture those into an error file (I love this
feature). The problem is, that when it encounters a date such as
"0021-01-02" it ignores the leading zeroes and calculates the date
based on my machine settings for how to handle 2-character years. Now
I not only end up with bad data in the database, but it happens with
NO warning at all to me. It's a very good thing that my tests caught
this situation.

So, two questions really I guess...

1. Is there any way that I can change this behavior for the package
(NOT through a machine setting - I DO NOT want to rely on my package
always being run on a specific machine that has to have a specific
setting)?

2. Is there any way to force an error on a data row so that it goes
through the error output path instead of continuing on to my output?
(i.e., I don't want to cause the whole task to fail, just a given row)

Thanks!
-Tom.

>I DO NOT want to rely on my package always being run on a specific machine
that has to have a specific setting.
Bravo! Wise choice.

When you extract the data from the CSV, is this date info being sent into
the pipeline as a DATE, or string?

You may need to keep it as a string so you can examine the first four
characters. Use multipe Conditional Split Transforms to break up the data to
different pipes and massage and 'fix' it accordingly. In the end do a big
UNION Transform to pull it back together.

I know, not real elegant but you could have VERY fine control over the
behavior if you 1) use Variables to compare against instead of literals, and
2) store those Variables in a Configuration.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Aardvark" wrote:

> I'm importing a CSV file into a database. The file includes a couple
> of date columns. I've added a Data Conversion step in my Data Flow to
> convert those columns. Unfortunately, SSIS does something which I
> consider to be nothing short of horrible...
>
> Some of the rows include typos in the data, so there are dates such as
> "0505-04-21". No problem there, as I've got the error output
> configured and I capture those into an error file (I love this
> feature). The problem is, that when it encounters a date such as
> "0021-01-02" it ignores the leading zeroes and calculates the date
> based on my machine settings for how to handle 2-character years. Now
> I not only end up with bad data in the database, but it happens with
> NO warning at all to me. It's a very good thing that my tests caught
> this situation.
>
> So, two questions really I guess...
>
> 1. Is there any way that I can change this behavior for the package
> (NOT through a machine setting - I DO NOT want to rely on my package
> always being run on a specific machine that has to have a specific
> setting)?
>
> 2. Is there any way to force an error on a data row so that it goes
> through the error output path instead of continuing on to my output?
> (i.e., I don't want to cause the whole task to fail, just a given row)
>
> Thanks!
> -Tom.
>

On Aug 22, 1:29*pm, Todd C wrote:
> When you extract the data from the CSV, is this date info being sent into
> the pipeline as a DATE, or string?

It's set in the CSV file definition as a string if that's what you
mean.

> You may need to keep it as a string so you can examine the first four
> characters. Use multipe Conditional Split Transforms to break up the datato
> different pipes and massage and 'fix' it accordingly. In the end do a big
> UNION Transform to pull it back together.

This was exactly what I started thinking through in my head. I was
actually playing with the conditional split when I got back to
checking the newsgroup. Great minds and all that

Thanks for the response and the suggestion!

-Tom.