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


Pushing data to an AS400


Pushing data to an AS400

Author
Message
rhunt
rhunt
SSC-Addicted
SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)

Group: General Forum Members
Points: 488 Visits: 181

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





rhunt
rhunt
SSC-Addicted
SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)

Group: General Forum Members
Points: 488 Visits: 181

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





Jack Corbett
  Jack Corbett
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19242 Visits: 14900

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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
rhunt
rhunt
SSC-Addicted
SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)

Group: General Forum Members
Points: 488 Visits: 181

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





rhunt
rhunt
SSC-Addicted
SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)

Group: General Forum Members
Points: 488 Visits: 181

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.





dzimmerman
dzimmerman
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 22
Did you find answer to this, as I am running into the same issue.
rhunt
rhunt
SSC-Addicted
SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)

Group: General Forum Members
Points: 488 Visits: 181
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



Norman Kelm
Norman Kelm
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

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



SQL-DBA
SQL-DBA
Old Hand
Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)

Group: General Forum Members
Points: 379 Visits: 462
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.
rubyengg1
rubyengg1
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 60
Please let me know what to use to connect to DB2 DB2/400 data connection via SSIS?
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