March 3, 2009 at 3:49 pm
Hi,
I am trying to do a simple transfer of data within the same database. I am trying to transfer data from an Account Table (OLE DB Source) to an AccountExtract Table (OLE DB Destination). The Account Table has many fields and there are two fields that have varchar(20). The AccountExtract Table has the same fields, but the two fields mentioned above with varchar(20) have only varchar(15). So all of the mapping was easy to do.
What I am trying to do is use the Error Output page in the OLE DB Source Data Flow transformation to redirect any TRUNCATED columns to a Flat File.
I have connected the green arrow (precedence) from the Source to the Destination. I connected the red arrow (error redirect) to the flat file. Unfortunately when I run the package, all the records get inserted into the Destination table. I expected the records that were truncated to be redirected to the flat file.
Any help on this basic issue would be appreciated.
Thanks.
March 3, 2009 at 4:08 pm
A couple of things you can check. First, do you have the error output (red arrow) connected from the OleDB Source to the flat file, or the OleDB Destination to the flat file? If your column width on the Output is more restrictive than the Source, you should connect the error output of the Output to the flat file.
Second, you need to check the Error Output on your OleDB Destination. Double click that object and go to Error Output. You will need to set the drop down list under Truncate to "Redirect Row". This will send any rows that would otherwise be truncated to your error output. Note that if you want to capture truncation of more than one column, you'll need to update each column with this setting.
If you're interested, you can also accomplish this with the Conditional Split transform. Test the column you wish to check for truncation by creating a new output and using something similar to the following in the Condition column:
LEN(TRIM(my_column_to_test)) > 15
You'll then have multiple outputs from the Conditional Split, and can send the ones longer than 15 to your error file, or clean them up inline if you wish.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
March 3, 2009 at 4:14 pm
you should wire up the error output to the OLE Destination, I think that is what you meant, and you are not getting errors but your fields are still being truncated is that correct?
You could use a data conversion task prior to the destination.
Select the fields that need to be checked for truncation then for each of them change the length to the needed value (15 or whatever). Then click configure error output and there is a truncation column (to specify action on truncation). From that drop down specify "ReDirect Row" (do this for both fields) then wire up your error text file to the red arrow from this task.
This should take care of it. - Tom
March 3, 2009 at 5:10 pm
Thanks, Guys. I did what you suggested and it worked.
Great forum and I am sure I will be back several times over the next year.
Thanks again.
Ease.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply