Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

importing data from as/400 to sql server 2005 Expand / Collapse
Author
Message
Posted Thursday, February 7, 2008 6:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 2:06 AM
Points: 13, Visits: 74
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
Posted Tuesday, February 12, 2008 7:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 4, 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
Posted Tuesday, February 12, 2008 8:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 2:06 AM
Points: 13, Visits: 74
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
Posted Tuesday, February 12, 2008 10:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 4, 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
Posted Wednesday, February 13, 2008 12:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 2:06 AM
Points: 13, Visits: 74
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
Posted Thursday, February 14, 2008 9:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 4, 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
Posted Friday, February 15, 2008 12:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 2:06 AM
Points: 13, Visits: 74
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
Posted Tuesday, February 19, 2008 6:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 4, 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
Posted Tuesday, February 19, 2008 7:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 2:06 AM
Points: 13, Visits: 74
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
Posted Friday, February 22, 2008 2:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 2:06 AM
Points: 13, Visits: 74
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 »

Add to briefcase 12»»

Permissions Expand / Collapse