Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2053
  • Last Modified:

SSIS IMPORT FROM AS400 FAILS TO EXECUTE

Hello experts,  I am using the SSIS import and export wizard to migrate tables from our as400 data server to SQL server 2008 , the process fails.
as im new to the SSIS Im not sure where to start looking.
The wizard reports: [Source Information]
Cannot locate the mapping file to map the provider types to SSIS types
and when I run the package I get execution errors regarding Data Conversion.
any ideas?
0
Hagita
Asked:
Hagita
  • 7
  • 4
  • 4
  • +2
5 Solutions
 
HagitaAuthor Commented:
BTW
I am using ADO.NET:System.Data.Odbc.OdbcConnection
0
 
theo kouwenhovenCommented:
Can you post the exact error code and text?
0
 
HagitaAuthor Commented:
[Data Conversion 0 - 0 [409]] Error: Data conversion failed while converting column "HTDES" (49) to column "HTDES" (437).  The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

[Data Conversion 0 - 0 [409]] Error: The "output column "HTDES" (437)" failed because truncation occurred, and the truncation row disposition on "output column "HTDES" (437)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Data Conversion 0 - 0" (409) failed with error code 0xC020902A while processing input "Data Conversion Input" (410). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

[Source - ECH [1]] Error: The component "Source - ECH" (1) was unable to process the data. Exception from HRESULT: 0xC0047020

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Source - ECH" (1) returned error code 0xC02090F5.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
theo kouwenhovenCommented:
Hi Haqita,

The first [Data Conversion 0 - 0 [409]] error is clear (I think) you obvisiously can't convert data between 2 diferent code pages. First transfer the data from the source file to a version with the right codepage. (if no othe option is available, export the file to an IFS CSV file)

 The second [Data Conversion 0 - 0 [409]] : check the max field length of your target file and change that to be able to store the whole fields from the source file, If that's not possible, Substring the left part of the source field (HTDES i think) to the max size of the target field. (some info will be lost)

Keep in mind that the AS/400 works with fixed field length.

Good Luck

0
 
HagitaAuthor Commented:
Hi Murphey2 and thanks.
can you please explain how to do the transfer to "a version with the right codepage"? and what is the right code page please?

about the second : where do I do the substring action? is it a part of theData Flow  tasks?
0
 
HagitaAuthor Commented:
T o add top this Question and clarify:
I keep getting data conversion errors and the Data Task Flow fails. the errors concern only staring data types.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
[Data Conversion 0 - 0 [409]] Error: Data conversion failed while converting column "HTDES" (49) to column "HTDES" (437).  The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

[Data Conversion 0 - 0 [409]] Error: The "output column "HTDES" (437)" failed because truncation occurred, and the truncation row disposition on "output column "HTDES" (437)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

These errors indicate that when converting the AS/400 column HTDES to the SQL Server column of the same name, SSIS had to perform truncation (AS/400 column is longer than the SQL Server column), and the SSIS job was set up to fail if truncation occurred on this column.  

What is the length and data type of the AS/400 columns HTDES?
What is the length and data type of the SQL Server column HTDES?  

If the target column is shorter than the AS/400 column, then you have to decide what you want to do here.  Either alter the target table to make the SQL Server column at least as long as the AS/400 column, or substring out a portion of the source column that will fit in the target column, or change the truncation row disposition for this column to not fail.

- Gary Patterson
0
 
tliottaCommented:
Well, 437 is practically UTF-8 (often 850 is used instead) and is named "USA PC-DATA". But I'm somewhat lost on what "49" would be. Is this an attempted CCSID or code page conversion that results in a change in length?

If data type/length is shown for the data elements, it might also be necessary to show the encoding.

Tom
0
 
theo kouwenhovenCommented:
If you are not familiar with the as/400 DB2, the most easy way is to changen your target DB so that all strings will fit in all fields. otherwise, you have to do the substring somewhere in your SSIS transfer.

About the code page, afaik you can create IFS files with a specific code-page and that will be used while transfering data from the AS/400.
0
 
tliottaCommented:
Keep in mind that the AS/400 works with fixed field length.

Only if you define the columns as fixed-length. Variable length has been around for maybe 20 years (though RPG had to wait longer to use them natively).

That would be another data attribute that should probably be shown.

Tom
0
 
theo kouwenhovenCommented:
Yes Tom,

I know, but I think no-one is using that except for long fields.
do you create var.length fields for customer numbers, sales values etc?
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
I'm only use SSIS occasionally, so I'm definitely not an SSIS expert.  

As a result, I may just be completely missing the boat, but I think this is truncation, not translation.  Here's why:

I'm pretty sure that the (49) and (437) designators DON'T represent CCSID's or code pages.  I think they are internal descriptors, in this case for the two different HTDES columns, probably either the LineageID, or the ColumnID from the dataflow metadata (http://bennyaustin.wordpress.com/2011/02/04/ssis-name-of-errorcolumn/).  

These first two error messages were generated during Row Conversion in the "Data Conversion Transformation":  http://msdn.microsoft.com/en-us/library/ms141713.aspx

The first error is a dual-purpose error that indicates that -EITHER- a truncation error occurred, -OR- a translation error occurred.

[Data Conversion 0 - 0 [409]] Error: Data conversion failed while converting column "HTDES" (49) to column "HTDES" (437).  The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

I think the next informational message hones in on the specific error, explaining that it is specifically a truncation error:

[Data Conversion 0 - 0 [409]] Error: The "output column "HTDES" (437)" failed because truncation occurred, and the truncation row disposition on "output column "HTDES" (437)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Data Conversion then fails back to the the main SSIS.Pipeline.  As seen in the error chain above, the Pipeline received error code 0xC020902A from "Data Conversion", which is listed in the SSIS Reference as a "truncation error": DTS_E_INDUCEDTRANSFORMFAILUREONTRUNCATION.   http://msdn.microsoft.com/en-us/library/ms345164.aspx

If so, then this puts us back to:  expand the size of the target field to match or exceed the length of the source field; substring the source field to a shorter length; or set the Truncation option for this column to "ignore the failure" on the Configure Error Output screen in BIDS:

http://msdn.microsoft.com/en-us/library/ms140321.aspx

- Gary Patterson


0
 
Gary PattersonVP Technology / Senior Consultant Commented:
As far as your original "Mapping file" problem goes, if this is really causing problems, then you might want to try switching data providers to either the ADO.NET or OLEDB data provider that comes with IBM iSeries Access (IBM i Access), instead of using the ADO.NET-ODBC bridge to access an ODBC driver.  

ADO.NET provider: IBM.Data.DB2.iSeries (http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Frzaik%2Frzaikdotnetprovider.htm)
OLEDB Provider: IBMDASQL

- Gary Patterson
0
 
tliottaCommented:
If there was a button we could click to add a bonus for a well-written, well-researched post, I'd click it for http:#a37047092 . Nice.

Tom
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
:-) Thanks.




0
 
Alpesh PatelAssistant ConsultantCommented:
Please use the conversion task or derived column to convert data.
0
 
HagitaAuthor Commented:
Hi guys, I am so thankful for the discussion and help but still no luck om my side:
the length and datatype of the columns (HTDES for example) are the same on the source and destination.
the error messages and failures happen with ODBC and ADO.net. I will try the OLEDB but right now I have a bug on tha side either..
I am using the wizard so the conversion task is used. I will try the derived column for conversion and see what.
0
 
HagitaAuthor Commented:
Okay..After a weak of struggling I  finally succeeded. and to let others know:

1)Use the OLED provider to connect to the AS400 source.
2)The tables and views list wont populate so use the SQL statement for each table.
3)you need to uninstall all .net framework sp from 3.5 and down in order to workaround a microsoft bug.
it is documented in (http://support.microsoft.com/?id=969845) and also :
http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-dts/12464/DTS-E-INDUCEDTRANSFORMFAILUREONERROR

0
 
HagitaAuthor Commented:
made a workaround to a microsoft bug.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now