February 7, 2008 at 6:47 am
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)
February 12, 2008 at 7:15 am
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
February 12, 2008 at 8:07 am
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
February 12, 2008 at 10:31 am
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
February 13, 2008 at 12:15 am
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
February 14, 2008 at 9:04 am
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
February 15, 2008 at 12:22 am
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!
February 19, 2008 at 6:48 am
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
February 19, 2008 at 7:48 am
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!
February 22, 2008 at 2:46 am
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!
February 26, 2008 at 7:14 am
Sorry for the delay in answering.
What is the CCSID of the column on the AS400?
What is the data type of the destination column in the SSIS package?
Norman Kelm
DTS Package Search
February 27, 2008 at 12:37 am
Thank you very much for answering!
So, I was not very sure how to see the CCSID of the column but I saw in the properties of the emulator somewhere where the port number was specified, that there was also something that said " Host code page : 037 United States"
I set the destination column to char(35) since with varchar I used to come to failure.
Thank you very much,
Waiting...
February 27, 2008 at 12:04 pm
CCSID 35 would be a the connection level. Each character column in the table can have a different CCSID. Search the internet for CCSID DB2, there are many different ids.
Do you have the iSeires Navigator installed on your workstation?
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzahg/rzahginav.htm scroll down to see the picture.
It provides SSMS/Entriprise Manager functionallity for ISeries DB2, you iSeries admin should be able to help you. You really NEED this tool to help you with this problem.
For the package right click on the AS400 OLEDB source and pick Show Advanced Editor...
Here is an example: http://www.ssistalk.com/wp-content/uploads/2007/03/issorted03.JPG
Look at the External Columns too, what is the codepage property and the datatype. DT_STR? If we can change this to DT_WSTR(Unicode) then you may be able to load the data. Also is your target table column data type an NVARCHAR or NCHAR?
Norman
DTS Package Search
February 28, 2008 at 8:02 am
If I used DT_WSTR then it said it couldn't convert non-unicode to unicode string data.
It failed also if I used varchar.
But then I tried from the SSMS using .Net Framework Data Provider for ODBC.
Import wizard completed successfully.
The problem then arouse when I wanted to schedule this using a job.
It used to give error message saying "data source name and driver not found"
The connection string is:
uid=username;Dsn=dnsname;system=IPAdressOfSystem;dbq=dbqname;dftpkglib=dbqname;
languageid=ENU;pkg=dbqname/DEFAULT(IBM),2,0,1,0,512;desc=Client Access Express ODBC data source;
There must be something here.
Thanks
February 28, 2008 at 8:38 am
Is the Client Access ODBC driver installed on the server where the job is scheduled and is there a dsn of that name created?
Norman Kelm
DTS Package Search
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply
