SSIS Import problem

[Lez]

Hi Guys,

I am having a problem using the SSIS application, I have a CSV file to
import into a temp table, which will be done every 24 hours, then run an
update into the main table.

I am having a problem as for some reason and I cannot figure out why, the
import fails.

I have tried as a CSV file and as in this example as a TXT file. both or
which fail.

For the import table, I have checked that the field Product is a
nvarchar(255) which is the maximum the data field that is being imported in
can contain.

Field Product Row 18 contains the value : Wedge-E -Stohlquist

Which is the same as row 17 : Wedge-E -Stohlquist

The only one that does concern me is Product Row 604: RiverTech Cartwheel
Pants 3/4 Length Trousers- Palm

Just wondering if the error is due to the 3/4 in the field, the field are
not text qualified.

What is status value 4?

This is the error I receive:

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
conversion for column "Product" returned status value 4 and status text
"Text was truncated or one or more characters had no match in the target
code page.".
(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task: The "output column "Product" (18)" failed
because truncation occurred, and the truncation row disposition on "output
column "Product" (18)" specifies failure on truncation. A truncation error
occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task: An error occurred while processing file
"C:\brookbank\prod.txt" on data row 604.
(SQL Server Import and Export Wizard)

Thanks in advance
Lez

[Uri Dimant]

Lez
Hmm, it should not failed, but you know , what if you remove 3/4 ,does it
work?
I did some testing and it worked just fine, can you send the TXT file and
SSIS package if you still fail with the solution

"Lez" <lj_nospam_girvan@/noSpam\hotmail.co.uk> wrote in message
news:35D3E0ED-125C-4128...@microsoft.com...

[Lez]

Hi Uri,

I have removed the 3/4 and ran the import with a small sample and it did
work ok. I also ran the import without the 'Product' column included and
that worked fine also.

So I think it fair to say that the issue is around characters within this
column causing the problem. I am going to try and see if using text
qualifiers will resolve the problem and advise accordingly.

Can you tell me however, is it possible once I have the SSIS working
correctly to include it as a windows task to update the products at regular
intervals? or the best option for doing this.

Many thanks
Lez

"Uri Dimant" <ur...@iscar.co.il> wrote in message
news:O15YzWVI...@TK2MSFTNGP04.phx.gbl...

[Lez]

Hi Uri,

To update you further, I created a sample text qualified CSV file and went
through the SSIS creation and completed an import of the data successfully.

However, when I try to run this a 2nd time, to test it from the SSIS saved
file it fails with the error:

Error: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW the product level is
insufficient for component "Source - PROD03_csv" row(1)
Error: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW the product level is
insufficient for component "Data Conversion 1" (92)
Task Data Flow Task Failed

If you can advise what that means?

Regards
Lez

"Lez" <lj_nospam_girvan@/noSpam\hotmail.co.uk> wrote in message

news:19D0A90E-EA0D-45ED...@microsoft.com...

[Uri Dimant]

Lez
Do you use Data Converion component? It is hard to say without testing

"Lez" <lj_nospam_girvan@/noSpam\hotmail.co.uk> wrote in message

news:F1011BA8-0740-4500...@microsoft.com...

[ananthramasamymeenachi]

Problem: Since the source file column data length increases the default max 255 chars, this causes the error. Reason for this is SSIS scans just the source top 10 rows or ramdom rows to analyse the max width of any field.

Solution: Go to Flat File Source properties, click "Show Advance Editior", Choose input output properties tab, under Flat file source output we have external columns and output columns, do the same for both as mentioned below. Select the column which is creating this issue, go to DataType Properties and below that we have length, override that with max lengh of that field which creates the issue i.e. MAX(LEN(column) of the
eg. SELECT MAX(LEN(ColumnName)) FROM tablename

Note: Set the field lengh for both External Column & Output Column

Now what ever you get its all unicode, you have to convert it to non unicode if needed. For this scenario we have to choose two data conversion task. One for converting to unicode and another for converting back to non unicode data format for selected fields needed. Basically UNICODE format used to hold muntilingual characters they can have equalent SQL data format with 'N' prefix (NATIONAL).

Please let me know incase you have any doubts.

Ananth Ramasamy Meenachi
www.msarm.com

Lez wrote:

Hi Uri,To update you further, I created a sample text qualified CSV file and
20-Aug-09

Hi Uri,

To update you further, I created a sample text qualified CSV file and went
through the SSIS creation and completed an import of the data successfully.

However, when I try to run this a 2nd time, to test it from the SSIS saved
file it fails with the error:

Error: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW the product level is
insufficient for component "Source - PROD03_csv" row(1)
Error: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW the product level is
insufficient for component "Data Conversion 1" (92)
Task Data Flow Task Failed

If you can advise what that means?

Regards
Lez

Previous Posts In This Thread:

On Wednesday, August 19, 2009 6:39 AM
Lez wrote:

SSIS Import problem
Hi Guys,

Thanks in advance
Lez

On Thursday, August 20, 2009 1:19 AM
Uri Dimant wrote:

LezHmm, it should not failed, but you know , what if you remove 3/4 ,does

Lez
Hmm, it should not failed, but you know , what if you remove 3/4 ,does it
work?
I did some testing and it worked just fine, can you send the TXT file and
SSIS package if you still fail with the solution

On Thursday, August 20, 2009 3:19 AM
Lez wrote:

Hi Uri,I have removed the 3/4 and ran the import with a small sample and it
Hi Uri,

I have removed the 3/4 and ran the import with a small sample and it did
work ok. I also ran the import without the 'Product' column included and
that worked fine also.

So I think it fair to say that the issue is around characters within this
column causing the problem. I am going to try and see if using text
qualifiers will resolve the problem and advise accordingly.

Can you tell me however, is it possible once I have the SSIS working
correctly to include it as a windows task to update the products at regular
intervals? or the best option for doing this.

Many thanks
Lez

On Thursday, August 20, 2009 4:41 AM
Lez wrote:

Hi Uri,To update you further, I created a sample text qualified CSV file and
Hi Uri,

To update you further, I created a sample text qualified CSV file and went
through the SSIS creation and completed an import of the data successfully.

However, when I try to run this a 2nd time, to test it from the SSIS saved
file it fails with the error:

Error: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW the product level is
insufficient for component "Source - PROD03_csv" row(1)
Error: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW the product level is
insufficient for component "Data Conversion 1" (92)
Task Data Flow Task Failed

If you can advise what that means?

Regards
Lez

On Friday, August 21, 2009 10:01 AM
Uri Dimant wrote:

LezDo you use Data Converion component? It is hard to say without testing

Lez
Do you use Data Converion component? It is hard to say without testing

EggHeadCafe - Software Developer Portal of Choice
Building Websites with DotNetNuke
http://www.eggheadcafe.com/tutorials/aspnet/1e4e724d-84d9-4c94-8838-3b2ec766fe52/building-websites-with-do.aspx