September 12, 2007 at 9:20 am
In SQL Server 2000 I can easily push data to our AS400 using an ODBC connection - which then uses the Client Access (iSeries Navigator) drivers. For AS400's, using IBM's ODBC drivers are still the most desirable method.
Nonetheless, now I am trying to push data through SSIS but ODBC not a valid connection type in the data flow object. So, I am trying to use the OLE DB connection object "IBMDA400" and I am having troubles. I have verified that the OLE DB object is successfully connecting because I have been able to select the correct table and map all of the columns.
However, I am getting the following error on every single column (with the column name changing of course):
An OLE DB record is available. Source: "IBMDA400 Command" Hresult: 0x80004005 Description: "CWBZZ5014 Value of parameter GBMCU could not be converted to the host data type.".
Anyone out there successfully pushing data to an AS400 using SSIS. Any tips you can provide?
Thanks in advance.
Ryan Hunt
September 12, 2007 at 11:07 am
Quick update... My problem appears to be in the conversion of DT_STR (on the SQL Server side) to DT_BYTES on the AS400. I've searched on the issue but there is little information. I am on I5/OS V5R4 with a very recent iSeries Navigator SP.
If anyone has any ideas...
September 13, 2007 at 7:35 am
Found this article, http://technet.microsoft.com/en-us/library/ms141704.aspx, that might help. It basically tells you how to convert data types in SSIS. SO you can convert from DT_STR to DT_BYTES. LIke this:
(DT_BYTES, 5) "GBMCU"
Hope this helps.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 14, 2007 at 2:51 pm
Thanks Jack. I've tried manipulating the "externa'" and "Output/Input" columns in my source and destination objects within the Data Flow. However, I always get an error. I am probably doing something wrong because I am brand new to SSIS. Can you tell me where I can input the conversion expression (DT_BYTES,5)? I am using a SQL query as my source in the OLE DB Source. I've tried using CAST and Convert but I there aren't any SQL data types that seem compatible with DT_BYTES.
DT_BYTES is the destination. The source is DT_STR.
Thanks. RH
September 19, 2007 at 1:08 pm
Just following up on this...hoping for some suggestions/info on the following:
- the convesion document is great but it doesn't say where to enter the conversion expressions in SSIS. Everywhere I have tried has generated an error. Anyone know?
-Is there any way to use ODBC with SSIS? IBM still recommends their ODBC driver as the preferred method for pushing and pulling info for DB2/400. My SQL 7 and 2000 DTS packages using the data pump over ODBC work great.
-Is anyone out there currently pushing data to their AS400 using SSIS in any fashion? If so, I would be glad to follow suit using whatever method.
Thanks in advance.
December 7, 2007 at 1:49 pm
Did you find answer to this, as I am running into the same issue.
December 7, 2007 at 2:05 pm
Yes, I setup the AS400 as a linked server on our SQL 2005 box and pull and push data that way. The linked server definition will still allow you to use ODBC...thereby creating a usable ODBC connection for SSIS.
It used to be with SQL2000 that the linked AS400 servers could be used to pull data but not push to them. Apparently SQL2005 has improved something because I can now update the AS400 linked server without getting MSDTC errors!
Good luck.
Ryan
December 10, 2007 at 9:08 am
I have found that the MS DB2 OLEDB works much better. The only issue is that you have to have SQL Server Enterprise. It still behaves badly though. I was trying to load a flat file to the 400 and it would error. I load the flat file to a SQL Server table and then to the 400, works fine.
I have lots of problems loading into date data type columns on the 400 as well.
Steve Fibich has a serveral blog entries on working with SSIS and the iSeries. http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/default.aspx
Norman
DTS Package Search
January 22, 2008 at 8:51 am
This is loosely related, but are any of you running SQL Server in a Windows virtual environment on the iSeries. This would be a i5/OS V5R4 config. I don't know a lot about IBM hardware but I would be interested if anyone has experience with this - possitive or negative. Thanks.
September 24, 2008 at 12:18 am
Please let me know what to use to connect to DB2 DB2/400 data connection via SSIS?
September 24, 2008 at 2:37 am
ODBC can be accessed in SSIS using ADO.NET connection manager.
To create ADO.NET connection for ODBC, Add new ADO.NET connection manager, go to Provider drop down, select ODBC Data Provider from drop down list.
This ODBC connection can be accessed using DataReader Source in Data Flow Task.
Note that ADO.NET connection manager does not support varchar data type. So automatically columns with varchar data type will be converted to nvarchar data type. In data flow task nvarchar data type can be converted back to varchar.
April 8, 2013 at 6:21 am
Hi,
I am getting error while moving data from SSIS dataflow task using provider "IBM DB2 UDB for iseries IBMDASQL OLEDB provider" , it was running fine,suddenly starts failing I noticed that the rows that are failing contains symbols " – " called as endash having ANSI code 0150. This is stored in varchar field in SQL Server 2008R2, and is mapped to character data type column of AS400/DB2 database. but its failing with error "CWBZZ5014 Value of parameter columnName could not be converted to the host data type."."CWBNL0107 - Converted 80 bytes, 2 errors found beginning at offset 10 ".
AS I found that this special symbol is at 11th position in the given column.
I have tried changing force translation value from 65535 to 1252 (Win Latin-1) but its not working ?
Can someone help here.
Thanks,
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply