I am a complete newbie to the world of SSIS so please bear with me if this seems a stupid question.
I am testing something as part of some self-learning and have a script that extracts data from a database table, pushes it out to a text file and then reads it back into another table in the database together with a value looked up from elsewhere. It all works fine.
So I've deliberately corrupted the initial output so that there is text instead of a monetary value, the idea being I thought I could redirect the error to a flat file (all part of my learning).
Except the package does the first bit ok (i.e writes out to a text file) but falls over when reading it back in, even though I've set it up to "redirect errors". The error is "unable to convert nvarchar to int".
Is SSIS not able to redirect these kinds of errors? Or does my learning have a long way to go?
Many thanks for any help.
Just bumping. Anyone?
Or is the "redirect error" SSIS function not fit for purpose?
Could you please post a screen shot of your ssis to see how you are doing things
The redirect occurs on output from a component. In this case - the error is occurring in input to the file source component and therefore never gets to the redirect.
The file source component uses a specification (same as a BCP format file) to define each column in the file and the data type of that column. Since the component has that column defined as money and the data in the file does not conform to the money data type - the column value cannot be read into the component.
If you expect that there could be data type issues - and you want to be able to redirect - then you need to define the input data type as a string and then use a data conversion component to attempt to convert to the appropriate data type. Then - it will fail on output and can be redirected.
Thanks for the clear explanation