Data Transfer to the AS400 --> SQL Server 2005 using SSIS: Driver problem copying non-numeric data

Question

Data Transfer to the AS400 --> SQL Server 2005 using SSIS: Driver problem copying non-numeric data

john.decoville

SSC Enthusiast

Points: 156
More actions
June 16, 2009 at 10:21 am

#135209

Both IBM's drivers and the HiT driver do not play nicely with MS's OLEDB. Using a stardard AS/400 in the connection driver as a Target Driver gives us only numeric data.
Does anyone know when this is going to be fixed? Or is there a fix oiut there we do not know about? Our work around is to use LINQ-Server on our Source SQL Server and do DELETE step followed by an INSERT step.
Thanks in advance for any advice!
--John 🙂

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

The IBM ODBC driver works and the Microsoft OLE DB driver also works if you are running SQL Server Enteprise edition. It is better if you run SSIS operations with IBM AS400 as SQL Server Admin instead of a user account.

http://blogs.msdn.com/dotnetinterop/archive/2006/01/19/oledb-provider-for-db2.aspx

Kind regards,
Gift Peddie

john.decoville SSC Enthusiast Points: 156 More actions · Answer 2

Thank you for your prompt response!:-)

We are running as Admin and it still gives us numbers.

Warmly,

-John

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

You need to make sure you are not transfering those special IBM columns and if all fails use the IBM native .NET provider, Check the IBM site for it.

Kind regards,
Gift Peddie

john.decoville SSC Enthusiast Points: 156 More actions · Answer 4

Have used the IBM .NET provider, drivers, etc.

Had IBM skilled help on the phone for hours. We have an Enterprise Help contract for software as well as hardware.

given the abandonment of the AS/400 by IBM I praise my lucky stars I have been transitioning to MS Development over the past 7 years. All my good contacts either do not work at IBM anymore or have been moved from the System i arena such as DataBases.

Regards,

--John

john.decoville SSC Enthusiast Points: 156 More actions · Answer 5

[font="Courier New"][/font]

Oh, and SS Crazy, I am not transferring those IBM Special columns either. We have some war stories about the Dat/Time Stamp to tell. I just converted them to NUMERIC(8, 0) for date for example.

We have been in touch with Both HiT in San Francisco and IBM about these issues and we have been told "No ETA in sight.." Just wondered if any of you are more up-to-date than we are.

Thanks!

--John 🙂

john.decoville SSC Enthusiast Points: 156 More actions · Answer 6

Only using CHAR(... and numeric( from IBM. Date/Time stamps are DOA.

Thanks,

--John

Gift Peddie SSC Guru Points: 73570 More actions · Answer 7

I cannot understand why your employer will buy a driver that is not better than the free versions because most users use the free versions which works. You could try connecting without the driver and see if there is a difference. I found some very strange data type mapping at the end of the page below so your problem is Hit and neither IBM nor Microsoft.

http://www.hitsw.com/products_services/sql400/odbc400/odbc400_dsheet.html

Kind regards,
Gift Peddie

john.decoville SSC Enthusiast Points: 156 More actions · Answer 8

We have the IBM version too. It does not work. That is why all the hours spent on the phone with IBM. We also got MS and HiT on the phone. MS's answer was "No ETA"

So you are suggesting we get free downloads and try them out?

Will try a few.

MS's answer was that IBM in the target connection manager would not work, period -- with the Drivers we gave them on a list. IBM and HiT were just a few.

Regards,

--John

Gift Peddie SSC Guru Points: 73570 More actions · Answer 9

Here are two free drivers one from Microsoft require Enterprise edition and the IBM .NET provider.

http://download.microsoft.com/download/4/4/D/44DBDE61-B385-4FC2-A67D-48053B8F9FAD/DB2OLEDB.exe

http://www-03.ibm.com/systems/i/software/access/windows/

Kind regards,
Gift Peddie

john.decoville SSC Enthusiast Points: 156 More actions · Answer 10

Thank you Gift Peddie for the tip. I am pursuing.

Also thank you for some of the great links you have proposed in other posts

Cheers!

🙂

-John

Gift Peddie SSC Guru Points: 73570 More actions · Answer 11

john.decoville (6/16/2009)
Thank you Gift Peddie for the tip. I am pursuing.
Also thank you for some of the great links you have proposed in other posts
Cheers!
🙂
-John

Thanks I have maintained an automated DTS package moving data from AS400 to 2000 so I know it works.

Kind regards,
Gift Peddie

john.decoville SSC Enthusiast Points: 156 More actions · Answer 12

Gift Peddie,

I have no problem with SQL Server 2000 for sending or receiving files to/from the AS/400. There seems to be a problem pushing files to the AS/400 except when we use INSERT instead of the normal send.

I am not a DBA and have had a problem opened on this for 18 months with a server specialist (DBA).

Open for ideas on how to light a fire under them.

Thank you,

--John

Gift Peddie SSC Guru Points: 73570 More actions · Answer 13

Here is a thread with Linked server code and most users say the Microsoft IBM driver works.

http://www.sqlservercentral.com/Forums/Topic666436-146-2.aspx

Kind regards,
Gift Peddie