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»»

Pushing data to an AS400 Expand / Collapse
Author
Message
Posted Wednesday, September 12, 2007 9:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:40 PM
Points: 326, Visits: 180

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




Post #398567
Posted Wednesday, September 12, 2007 11:07 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:40 PM
Points: 326, Visits: 180

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...




Post #398635
Posted Thursday, September 13, 2007 7:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:38 PM
Points: 10,381, Visits: 13,440

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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #398939
Posted Friday, September 14, 2007 2:51 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:40 PM
Points: 326, Visits: 180

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




Post #399472
Posted Wednesday, September 19, 2007 1:08 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:40 PM
Points: 326, Visits: 180

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.




Post #400500
Posted Friday, December 7, 2007 1:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 3, 2009 11:21 AM
Points: 1, Visits: 22
Did you find answer to this, as I am running into the same issue.
Post #430859
Posted Friday, December 7, 2007 2:05 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:40 PM
Points: 326, Visits: 180
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



Post #430865
Posted Monday, December 10, 2007 9:08 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 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
http://www.dtspackagesearch.com/



Post #431380
Posted Tuesday, January 22, 2008 8:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 2:55 AM
Points: 80, Visits: 337
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.
Post #445905
Posted Wednesday, September 24, 2008 12:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 21, 2011 5:23 PM
Points: 4, Visits: 60
Please let me know what to use to connect to DB2 DB2/400 data connection via SSIS?
Post #574912
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse