June 21, 2012 at 8:29 am
Hi Friends,
i am trying to transfer data from AS400 to SQL through SSIS. i googled about this and i found that i need DB2 provider or IBM iseries providers..
i downloaded Microsoft DB2 provider but when i installed in production server, it says it is supported for 2005 which is not installed. i tried on my machine which is having sql 2008 and it worked out. Production server is having sql 2008 r2 but it gives such kind of error...
so i am looking to download IBM iseries access but i am getting any link where to download it....
Could guys help me? your help would avoid me going under bad situation..
Thanks,
Charmer
June 21, 2012 at 9:16 am
MS ole db provider for db2 requires Enterprise edition of sql.
..and I believe for you to obtain IBM IseriesClientAccess software from IBM site, you'll need IBM userid/password if you are entitled for a software support.
I use both Iseries Client access and oledb provider for db2 and either works pretty well. Our production servers use the ClientAccess on our ssis packages to ETL between SQL and as400.
My client machine has developer edition of sql, so I use MS oledb to connect to iseries through linked server.
_____________
Donn Policarpio
June 21, 2012 at 9:29 am
donpolix-313947 (6/21/2012)
MS ole db provider for db2 requires Enterprise edition of sql...and I believe for you to obtain IBM IseriesClientAccess software from IBM site, you'll need IBM userid/password if you are entitled for a software support.
I use both Iseries Client access and oledb provider for db2 and either works pretty well. Our production servers use the ClientAccess on our ssis packages to ETL between SQL and as400.
My client machine has developer edition of sql, so I use MS oledb to connect to iseries through linked server.
could you explain how to connect through linked server?
Here i have attached the error ...Please take a look at it...
Thanks,
Charmer
June 21, 2012 at 1:57 pm
What edition of sql server do you have in your production server? Verify if you have the enterprise edition.
You may download DB2OLEDB packages here (not sure if these are the latest for each sql versions though)
DB2OLEDB (sql2005)
http://www.microsoft.com/en-us/download/details.aspx?id=15748
DB2OLEDB (sql2008)
http://www.microsoft.com/en-us/download/details.aspx?id=16978
I only use linked server so I can run distributed queries against as400 from my SSMS.
Once you have the ibm client access installed, you'll have "IBMDA400" available as your provider (In SSMS, expand Server Objects->Linked Servers->Providers) and you can use it to execute your distributed query.
--add IBMDA400 linked server
sp_addlinkedserver
@server=N'MY_AS400_SRV',
@srvproduct=N'IBM AS400 OLEDB Provider',
@provider=N'IBMDA400',
@datasrc=N'MY_AS400_SRV',
@provstr=N'Initial Catalog=MY_AS400_SRV;Default Collection=MY_LIBRARY_LIBF;Force Translate=65535;Transport Product=Client Access;Convert Date Time To Char=FALSE;Block Size=1024;'
go
sp_addlinkedsrvlogin @rmtsrvname=N'MY_LIBRARY_LIBF',
@useself='false',
@rmtuser=N'MYUSERNAME',
@rmtpassword='MYPASSWORD'
go
--Here's how you can add a linked server using DB2OLEDB:
sp_addlinkedserver
@server=N'MY_AS400_SRV',
@srvproduct=N'Microsoft OLE DB Provider for DB2',
@provider=N'DB2OLEDB',
@datasrc=N'MY_AS400_SRV',
@provstr='NetLib=TCPIP;NetAddr=MY_AS400_SRV;NetPort=446;RemoteLU=GENESIS;LocalLU=LOCAL;ModeName=QPCSUPP;User ID=MYUSERNAME;Password=MYPASSWORD;InitCat=MY_AS400_SRV;Default Schema=MY_LIBRARY_LIBF;PkgCol=MY_LIBRARY_LIBF;TPName=;Commit=YES;IsoLvl=NC;AccMode=;CCSID=37;PCCodePage=1252;BinAsChar=NO;Data Source=MY_AS400_SRV',
@catalog='MY_AS400_SRV'
GO
--to drop the linked server
sp_droplinkedsrvlogin @rmtsrvname=N'MY_AS400_SRV',@locallogin=Null
go
sp_dropserver @server=N'MY_AS400_SRV'
go
--Note: You may need to enable “Allow inprocess” for IBMDA400 provider. In management studio, go to Linked Servers->Providers->IBMDA400->Right click->Properties
Once you have your linked server in place you can run your four part name queries,..
For example:
SELECT * FROM MY_AS400_SRV.MY_AS400_SRV.MY_LIBRARY_LIBF.MYAS400FILE
Or use OPENROWSET...
--Using IBMDA400 in openrowset:
SELECT * FROM OPENROWSET
('IBMDA400','Data Source=MY_AS400_SRV;Initial Catalog=MY_AS400_SRV;USER ID=MYUSERNAME;Password=MYPASSWORD;Default Collection=MY_LIBRARY_LIBF;Force Translate=65535;Transport Product=Client Access;Convert Date Time To Char=FALSE;Block Size=1024;',
'
SELECT * FROM MY_LIBRARY_LIBF.MYAS400FILE WHERE FIELD1 = ''TEST''
'
)
--Or using DB2OLEDB in openrowset:
SELECT * FROM OPENROWSET
('DB2OLEDB','User ID=MYUSERNAME;Password=MYPASSWORD;Initial Catalog=MY_AS400_SRV;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=MY_AS400_SRV;Network Port=446;Package Collection=MY_LIBRARY_LIBF;Default Schema=MY_LIBRARY_LIBF;Process Binary as Character=False;Units of Work=RUW;Default Qualifier=MY_LIBRARY_LIBF;DBMS Platform=DB2/AS400;Defer Prepare=False;Persist Security Info=False;Connection Pooling=False;Derive Parameters=False;',
'
SELECT * FROM MY_LIBRARY_LIBF.MYAS400FILE WHERE FIELD1 = ''TEST''
'
)
When you design or execute your ssis packages, you should have either of the two providers installed on the machine. When you create a new OLEDB connection, you should see something like "IBMDA400 OLE DB Provider" or "Microsoft OLE DB Provider for DB2" depending on which package is installed.
Hope this helps.
_____________
Donn Policarpio
June 22, 2012 at 2:31 am
The client server has standard edition of sql 2008R2...
what shall i do..?
Thanks,
Charmer
June 22, 2012 at 8:56 am
Since you have AS400 systems, I'm assuming you're entitled for ibm support, so you may wanna go for ClientAccess and have it installed on your standard ed sql. If you have 64 bit system, there is a patch you need to apply before you install clientaccess.
Whether you wanna upgrade to sql enterprise and use db2oledb, or use clientaccess is up to you.
There are other software out there for sure that serves the same function so you may wanna search if you haven't done so.
_____________
Donn Policarpio
June 22, 2012 at 11:08 am
For a SQL Server 2008 R2 64 bit System I use IBM System Access for Windows V6R1 SI42423 (64-bit) on Windows 2008 R1 64-bit with SQL Server.
As was previously stated you will need an IBM Account to get the drivers.
The following are Microsoft prerequisites for the 64-bit version of IBM iSeries Client v6.1 SI42423.
1. Microsoft Visual C++ 2005 SP1 Redistributable Package (x86)
http://www.microsoft.com/download/en/details.aspx?id=5638
Note - This 32-bit fix needs to be applied even though it is a 64-bit operating system
2. Microsoft Visual C++ 2005 SP1 Redistributable Package (x64)
http://www.microsoft.com/download/en/details.aspx?id=18471
3. Both the 32-bit and 64-bit versions of Microsoft Visual C++ 2005 Service Pack 1 Redistributable Package ATL Security Update
http://www.microsoft.com/download/en/details.aspx?id=14431
Note - download and install both vcredist_x64.exe (64-bit) and vcredist_x86.exe (32-bit) even though it is a 64-bit operating system
When you create the ODBC Connections pay close attention to the settings.
HTH.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 22, 2012 at 11:13 am
Welsh Corgi (6/22/2012)
Thanks for providing some feedback. I'm sure the OP appreciates it also.
June 25, 2012 at 6:59 am
Welsh Corgi (6/22/2012)
For a SQL Server 2008 R2 64 bit System I use IBM System Access for Windows V6R1 SI42423 (64-bit) on Windows 2008 R1 64-bit with SQL Server.As was previously stated you will need an IBM Account to get the drivers.
The following are Microsoft prerequisites for the 64-bit version of IBM iSeries Client v6.1 SI42423.
1. Microsoft Visual C++ 2005 SP1 Redistributable Package (x86)
http://www.microsoft.com/download/en/details.aspx?id=5638
Note - This 32-bit fix needs to be applied even though it is a 64-bit operating system
2. Microsoft Visual C++ 2005 SP1 Redistributable Package (x64)
http://www.microsoft.com/download/en/details.aspx?id=18471
3. Both the 32-bit and 64-bit versions of Microsoft Visual C++ 2005 Service Pack 1 Redistributable Package ATL Security Update
http://www.microsoft.com/download/en/details.aspx?id=14431
Note - download and install both vcredist_x64.exe (64-bit) and vcredist_x86.exe (32-bit) even though it is a 64-bit operating system
When you create the ODBC Connections pay close attention to the settings.
HTH.
Hi Corgi, Thank you for the ideas...
i am having some concerns....If my client machine does not agree with upgrading from sql standard edition to enterprise edition, what should i do? And i explained to my higher authorities that "we can ask our client to get the IBM drivers since they use AS400, they must have entitled for software support with IBM...so that they can get the drivers from IBM and we can use it for our data transformation to SQL..." but my authority said that "we must not ask clients for anything unless we have no other option".....So what should i do now?
I am still unable to connect to the AS400 to SQL server....i am out of ideas....so i am totally depend on you guys..
Help me please...
Thanks,
Charmer
June 25, 2012 at 7:02 am
Lynn Pettis (6/22/2012)
Welsh Corgi (6/22/2012)
Thanks for providing some feedback. I'm sure the OP appreciates it also.
yes of course Lynn, I totally appreciate it..because feed back is not just a feed back...its a help when ever we are out of help...Thank you to Lynn and Welsh Corgi....
Thanks,
Charmer
June 25, 2012 at 1:04 pm
Charmer (6/25/2012)
i am having some concerns....If my client machine does not agree with upgrading from sql standard edition to enterprise edition, what should i do?
Perhaps I'm missing something but the Edition is not a factor with respect to client drivers.
And i explained to my higher authorities that "we can ask our client to get the IBM drivers since they use AS400, they must have entitled for software support with IBM...so that they can get the drivers from IBM and we can use it for our data transformation to SQL..." but my authority said that "we must not ask clients for anything unless we have no other option".....So what should i do now?
The only other option would be to pay IBM.
I would ask the client for the drivers.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 25, 2012 at 1:29 pm
For Microsoft OLEDB Provider for DB2, it requires enterprise or developer edition.
For IBM client access, yes, standard ed is just fine.
_____________
Donn Policarpio
June 25, 2012 at 3:22 pm
The company that hosts our AS400 (huge company) recommends the iSeries Client Access Drivers.
They have a lot of clients and they use these drivers in their proprietary DTS and SSIS Packages.
I use the iSeries as well and I have not tried the Microsoft driver.
I have used the Microsoft and Oracle Drivers when connecting to Oracle and I prefer the Oracle Drivers as opposed to Microsoft.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 26, 2012 at 2:31 am
I conveyed the problem to my higher authorities and clients. And they told that they will try upgrading to enterprise edition. So once if they upgraded it, i believe that i am surely able to use Microsoft OLEDB DB2 drivers for data transformation unless i am not going to get any IBM iseries drivers from them. So for God's sake, upgrading SQL server, it is going to happen atleast .... phewwww.....:satisfied:
Thanks,
Charmer
June 26, 2012 at 5:47 am
Is the driver issue the only justification that you have for upgrading to the Enterprise Edition?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply