importing data from as/400 to sql server 2005

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Data Transformation Services (DTS) » importing data from as/400 to sql server 2005...

18 posts, Page 1 of 2

1 2 »»

importing data from as/400 to sql server 2005

Rate Topic

Display Mode

Topic Options

Author

Message

elonacaci

Posted Thursday, February 07, 2008 6:47 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 23, 2013 2:45 AM
Points: 12, Visits: 61

I am trying to import a table from AS/400 choosing 3 of its fields. The problem arises with column
crura. The error is below. Can anybody help me?

Messages
Error 0xc020901c: Data Flow Task: There was an error with output column "CRURA" (20) on output "OLE DB Source Output" (11). The column status returned was: "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 "CRURA" (20)" failed because truncation occurred, and the truncation row disposition on "output column "CRURA" (20)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Query" (1) returned error code 0xC020902A. 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.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

Post #452702

Norman Kelm

Posted Tuesday, February 12, 2008 7:15 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 04, 2012 1:34 PM
Points: 130, Visits: 322

Is the AlwaysUseDefaultCodePage property set to True on the AS400 Data Source Task?

Which driver is being used, IBM's OLEDB or MS OLEDB for DB2?

Also examine the CCSID for column using the iSeries Navigator. Is it 37?

Norman

DTS Package Search
http://www.dtspackagesearch.com/

Post #454407

elonacaci

Posted Tuesday, February 12, 2008 8:07 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 23, 2013 2:45 AM
Points: 12, Visits: 61

Thanks for helping!

I am using "IBM DB2 UDB for iSeries IBMDA400 OLE DB provider"
Everything goes ok for most of the tables. I am facing this problem only in some tables
As i have seen it has to do something with columns that contain names,surnames,addresses of people.

sorry but how can I check these two:

"Is the AlwaysUseDefaultCodePage property set to True on the AS400 Data Source Task?
Also examine the CCSID for column using the iSeries Navigator. Is it 37?"

I am doing everything from Sql Server Management Studio

Thanks

Post #454449

Norman Kelm

Posted Tuesday, February 12, 2008 10:31 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 04, 2012 1:34 PM
Points: 130, Visits: 322

I see, you are using the Import Export Wizard. Sorry I missed that.

Not much you can do there.

I think it's time to jump in and create an SSIS package to do this transfer.

Unfortunately, I have found the IBM drivers to be very difficult to work with.

The MS OLEDB Provider for DB2 is much better. The only problem is that SQL Server must be the Enterprise version. Developer is OK for dev.
If you can't use MS' driver we can probably get the IBM working, but you need to create the SSIS package so that you can set these items.

I would definitely get the iSereis Navigator tool installed as well, it's the SSMS for DB2. Also called the iSeries Access for Windows. It comes with the iSeries OS so your AS400 group should be able to help you. It is very necessary to look around and explore data and meta-data for the tables that you will be pulling from.

Are you in a foreign country and are there foreign characters in this column?

Norman

DTS Package Search
http://www.dtspackagesearch.com/

Post #454555

elonacaci

Posted Wednesday, February 13, 2008 12:15 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 23, 2013 2:45 AM
Points: 12, Visits: 61

Hi again!
I am from Albania. In fact I have done some tries and I have come in the conclusion that it hasn't have to do with any special character of my language but I think with the way that data is saved in AS/400 library.

When you say "create a SSIS package" you mean it from "Sql Server business intelligence development studio"?
I already save the SSIS package after the wizard.

Thanks

Post #454881

Norman Kelm

Posted Thursday, February 14, 2008 9:04 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 04, 2012 1:34 PM
Points: 130, Visits: 322

Yes that is what I meant.

Is all the data being pulled from table or are you using a query?

If you use a query you might solve the problem by using a CAST on the column that is giving you trouble.

Norman

DTS Package Search
http://www.dtspackagesearch.com/

Post #455783

elonacaci

Posted Friday, February 15, 2008 12:22 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 23, 2013 2:45 AM
Points: 12, Visits: 61

It's really strange but it suceeds only if I configure the error output to ignore failure.

Very strange. Neither cast to character solves it. I hope it hasn't have anything wrong with this!

Post #456175

Norman Kelm

Posted Tuesday, February 19, 2008 6:48 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 04, 2012 1:34 PM
Points: 130, Visits: 322

Yes, that is how it should work if the error handling is configured that way.

Row(s) are probably being missed.

Direct the error output to a flat file to see the rows causing the error. (Unless you have done that already)

Norman

DTS Package Search
http://www.dtspackagesearch.com/

Post #457348

elonacaci

Posted Tuesday, February 19, 2008 7:48 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 23, 2013 2:45 AM
Points: 12, Visits: 61

First of all thank you very much for helping me.
I did as you told me for the flat file and in fact there were 55 rows with problems, 54 of which had in common a letter which exists in my language , it is the letter Ç. One of them had another letter
like A but with a sign over.

Well I have to accept that I had been mistaken for the presence of such characters.

The problem now is that when I use the option ignore failure, these rows come but the leter Ç is
substituted with an ?

Do you have any idea how can I solve this?

Thank you very much!

Post #457396

elonacaci

Posted Friday, February 22, 2008 2:46 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 23, 2013 2:45 AM
Points: 12, Visits: 61

I am really stucked with this problem. I need to schedule also this task, but scheduling is available only from SSMS and not from SSBIDS. When I import the package to SSMS, it gives the
truncation error. It seems that it doesn't take in consideration "the ignor failure option"

Any idea, so I have got 2 problems now, the one that wherever there is "Ç" I get "?" , but I can not also use this partial solution since I can not schedue it.

Any idea?
Thanks!

Post #458976

« Prev Topic | Next Topic »

18 posts, Page 1 of 2

1 2 »»

Permissions