SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


importing data from as/400 to sql server 2005


importing data from as/400 to sql server 2005

Author
Message
elonacaci
elonacaci
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 75
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)
Norman Kelm
Norman Kelm
SSC-Addicted
SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)

Group: General Forum Members
Points: 454 Visits: 325
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/



elonacaci
elonacaci
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 75
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
Norman Kelm
Norman Kelm
SSC-Addicted
SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)

Group: General Forum Members
Points: 454 Visits: 325
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/



elonacaci
elonacaci
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 75
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
Norman Kelm
Norman Kelm
SSC-Addicted
SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)

Group: General Forum Members
Points: 454 Visits: 325
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/



elonacaci
elonacaci
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 75
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!
Norman Kelm
Norman Kelm
SSC-Addicted
SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)

Group: General Forum Members
Points: 454 Visits: 325
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/



elonacaci
elonacaci
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 75
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!
elonacaci
elonacaci
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 75
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search