Discussion:
the flat file destination component's error redirect
(too old to reply)
Feng Xiaohe
2009-06-02 02:11:01 UTC
Permalink
I used to import data from csv file with the flat file destination component.
But I want to redirect the data flow to a error log table when the importing
csv file's data length is too long, then I found I can't get the actual data
while error occured. Is there any good method to solve it?

Thank you
Feng Xiaohe
Todd C
2009-06-03 13:12:02 UTC
Permalink
Can we assume you are using SQL 2005/08 and SSIS, not SQL 2000 and DTS?

If so, set up your Data Flow task with the Flat File source, a Data
Conversion, and your SQL Destination (or whatever destination you are using).
The key here is in the Data Conversion. Convert your respective fields
(giving each a new name) and new (shorter?) character lengths. Now add a new
Flat File Destination, and drag the red arrow from the output of the Data
Conversion to the new Flat File. When you do, you should be presented with a
dialog box for you to configure the Error rows of the Data Conversion Task.
Set fields to "Redirect" under the Truncation event. Configure the new Flat
File Destination to save to a new Flat File Connection Manager (NOT your CSV
source).

When you run the package, any field that would get truncated by the Data
Conversion will be sent down the error (red) path to the flat file. Examine
the contents of the flat file after running to see those rows.

HTH
=====
Todd C
Post by Feng Xiaohe
I used to import data from csv file with the flat file destination component.
But I want to redirect the data flow to a error log table when the importing
csv file's data length is too long, then I found I can't get the actual data
while error occured. Is there any good method to solve it?
Thank you
Feng Xiaohe
Feng Xiaohe
2009-06-04 14:30:01 UTC
Permalink
I'm sorry. I know to set the redirect properties in the SqlServer 2005 SSIS.
But when the data length is too long, it is failed to import. So although be
redirect, I can't get the bad data. How to solve it? It seemed to be
difficult.
Post by Todd C
Can we assume you are using SQL 2005/08 and SSIS, not SQL 2000 and DTS?
If so, set up your Data Flow task with the Flat File source, a Data
Conversion, and your SQL Destination (or whatever destination you are using).
The key here is in the Data Conversion. Convert your respective fields
(giving each a new name) and new (shorter?) character lengths. Now add a new
Flat File Destination, and drag the red arrow from the output of the Data
Conversion to the new Flat File. When you do, you should be presented with a
dialog box for you to configure the Error rows of the Data Conversion Task.
Set fields to "Redirect" under the Truncation event. Configure the new Flat
File Destination to save to a new Flat File Connection Manager (NOT your CSV
source).
When you run the package, any field that would get truncated by the Data
Conversion will be sent down the error (red) path to the flat file. Examine
the contents of the flat file after running to see those rows.
HTH
=====
Todd C
Post by Feng Xiaohe
I used to import data from csv file with the flat file destination component.
But I want to redirect the data flow to a error log table when the importing
csv file's data length is too long, then I found I can't get the actual data
while error occured. Is there any good method to solve it?
Thank you
Feng Xiaohe
Todd C
2009-06-04 14:56:02 UTC
Permalink
So if I understand you correctly now, the errors come when the Flat File
Source goes to retrieve data from the file itself? And you cannot even get
those rows into the Data Flow pipeline?

Are you using a Flat File Connection Manager for the Source? Double-click it
in the Connection Manager tray down below. On one of the pages you can set
the character length of the fields. You should be able to make this value as
large as needed so all records get included.


=====
Todd C
Post by Feng Xiaohe
I'm sorry. I know to set the redirect properties in the SqlServer 2005 SSIS.
But when the data length is too long, it is failed to import. So although be
redirect, I can't get the bad data. How to solve it? It seemed to be
difficult.
Post by Todd C
Can we assume you are using SQL 2005/08 and SSIS, not SQL 2000 and DTS?
If so, set up your Data Flow task with the Flat File source, a Data
Conversion, and your SQL Destination (or whatever destination you are using).
The key here is in the Data Conversion. Convert your respective fields
(giving each a new name) and new (shorter?) character lengths. Now add a new
Flat File Destination, and drag the red arrow from the output of the Data
Conversion to the new Flat File. When you do, you should be presented with a
dialog box for you to configure the Error rows of the Data Conversion Task.
Set fields to "Redirect" under the Truncation event. Configure the new Flat
File Destination to save to a new Flat File Connection Manager (NOT your CSV
source).
When you run the package, any field that would get truncated by the Data
Conversion will be sent down the error (red) path to the flat file. Examine
the contents of the flat file after running to see those rows.
HTH
=====
Todd C
Post by Feng Xiaohe
I used to import data from csv file with the flat file destination component.
But I want to redirect the data flow to a error log table when the importing
csv file's data length is too long, then I found I can't get the actual data
while error occured. Is there any good method to solve it?
Thank you
Feng Xiaohe
Feng Xiaohe
2009-06-05 10:06:03 UTC
Permalink
I'm developing the SSIS package in the SqlServer 2005 that is used to tranfer
data from CSV file to database. And I factly desired to get the wrong data
from the CSV file into a special table, especially, the length is too long.
As you know, in the CSV file, these situation frequently happed. But I can't
find a method to solve it because if the CSV's column is longer, the data
will be cut automatically when inserting into the database. Perhaps my
English is not very well, but i also hope you can undstand me.Thank you.

Feng Xiaohe
Thank you
Post by Todd C
So if I understand you correctly now, the errors come when the Flat File
Source goes to retrieve data from the file itself? And you cannot even get
those rows into the Data Flow pipeline?
Are you using a Flat File Connection Manager for the Source? Double-click it
in the Connection Manager tray down below. On one of the pages you can set
the character length of the fields. You should be able to make this value as
large as needed so all records get included.
=====
Todd C
Post by Feng Xiaohe
I'm sorry. I know to set the redirect properties in the SqlServer 2005 SSIS.
But when the data length is too long, it is failed to import. So although be
redirect, I can't get the bad data. How to solve it? It seemed to be
difficult.
Post by Todd C
Can we assume you are using SQL 2005/08 and SSIS, not SQL 2000 and DTS?
If so, set up your Data Flow task with the Flat File source, a Data
Conversion, and your SQL Destination (or whatever destination you are using).
The key here is in the Data Conversion. Convert your respective fields
(giving each a new name) and new (shorter?) character lengths. Now add a new
Flat File Destination, and drag the red arrow from the output of the Data
Conversion to the new Flat File. When you do, you should be presented with a
dialog box for you to configure the Error rows of the Data Conversion Task.
Set fields to "Redirect" under the Truncation event. Configure the new Flat
File Destination to save to a new Flat File Connection Manager (NOT your CSV
source).
When you run the package, any field that would get truncated by the Data
Conversion will be sent down the error (red) path to the flat file. Examine
the contents of the flat file after running to see those rows.
HTH
=====
Todd C
Post by Feng Xiaohe
I used to import data from csv file with the flat file destination component.
But I want to redirect the data flow to a error log table when the importing
csv file's data length is too long, then I found I can't get the actual data
while error occured. Is there any good method to solve it?
Thank you
Feng Xiaohe
Todd C
2009-06-03 13:12:02 UTC
Permalink
Can we assume you are using SQL 2005/08 and SSIS, not SQL 2000 and DTS?

If so, set up your Data Flow task with the Flat File source, a Data
Conversion, and your SQL Destination (or whatever destination you are using).
The key here is in the Data Conversion. Convert your respective fields
(giving each a new name) and new (shorter?) character lengths. Now add a new
Flat File Destination, and drag the red arrow from the output of the Data
Conversion to the new Flat File. When you do, you should be presented with a
dialog box for you to configure the Error rows of the Data Conversion Task.
Set fields to "Redirect" under the Truncation event. Configure the new Flat
File Destination to save to a new Flat File Connection Manager (NOT your CSV
source).

When you run the package, any field that would get truncated by the Data
Conversion will be sent down the error (red) path to the flat file. Examine
the contents of the flat file after running to see those rows.

HTH
=====
Todd C
Post by Feng Xiaohe
I used to import data from csv file with the flat file destination component.
But I want to redirect the data flow to a error log table when the importing
csv file's data length is too long, then I found I can't get the actual data
while error occured. Is there any good method to solve it?
Thank you
Feng Xiaohe
Loading...