Trying to set up as/400 connection to tranfer data to SQL 2005

[swoodring]

I am trying to import data from our as/400 to SQL server. We have a new server with SQL 2005, data is restored so now I am trying to convert our DTS over to SSIS but I can's seem to get a connection set up that will talk with our as/400. One note on our current sql 2000 I have no issues. Our sql 2005 is 64 bit, not sure if it makes a difference but thought I would mention it.



I am trying to set up connections so I can copy data from as/400 to sql 2005. I have a DNS set up on server called IPTSFIL which point to library iptsfil. I then need to create my source. There is no longer an ODBC option so I am trying ole db source. I created a connection but it’s not working. In the connection there is a data link if I click on that I can point it to my DSN (IPTSFIL). But when I try to test it then thinks server name is IPTSFIL which is not the case and so it comes back with a port error due to that not being the system name. What am i doing wrong?



I read some people use data reader but I don't understand how to config that. Any input on how to help me get this working either using ole db or datareader would be appreciated.



I did test a client access session on server and I connect just fine. Also if I do a cwbping everything comes back sucessful.



Thanks in Advance,

Stacy

 

[RiverGuy]

If you are using the IBM Client Access OLEDB driver, then you do not need to use your DSN. You configure the connection object in your SSIS package with the Server, Library list, etc.

 

[swoodring]

Thanks for your reply.

I think I know what your trying to tell me. Let me make sure.

do a new connection within SSIS select my as400 name for server name key in profile/password. and set initial catalog to library I want.

I tried this and hit test connection and it tell me test connetion failed because of an error initializing provider. CBZZ5024 the intial catalog propery is set to an invalid catalog

Below are my values when setting up connection
OLE Db provider - IBM DB2 UDB for ISERIES OLE DB Provider
Server of File Name - our as 400 name
Select user specific user profile password and I fill it in
Initial Catalog - put in the library I needed. I also tried system name.library name both come back with same error.

Am I do what your asking. Any other suggestions?

Stacy

 

[RiverGuy]

I am not sure why you would be getting that error. Maybe it is an issue with the 64 bit installation. The only time I used an AS400 connection on 64 bit SQL Server 2005, we used a Microsoft developed DB2 driver. But I think it's only licensed for Enterpise Edition. Check out this link if interested.

http://msdn2.microsoft.com/en-us/library/ms178027.aspx

 

[swoodring]

Ok I figured it out. I had a paramter wrong. So connectioin looks like it is working. I now I can see the tables and when I test it works. But now when I select one of the tables as my source it gives me the following error. What does this mean and what do I do to resolve.

TITLE: Microsoft Visual Studio
------------------------------

The component reported the following warnings:

Warning at {D34B185E-3C8D-45B0-A617-7F2FBFE297BF} [OLE DB Source [267]]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.


Choose OK if you want to continue with the operation.
Choose Cancel if you want to stop the operation.

------------------------------
BUTTONS:

OK
Cancel
------------------------------

 

[swoodring]

I figured it out. I had to set the AlwaysUseDefaultCodePage to True on the OLE DB Source component for your AS400 pull. Now on to seeing if I can figure out how to copy data now. SSIS is so much different than DTS. It's a learning curve for me.