Connecting to IBM DB2 USING SSIS

We need to connect to an DB2 server using ODBC provider, so we tried with the folowing:

Driver={IBM DB2 ODBC DRIVER};database=FirstDB; hostname=IP address; port=50000;protocol=TCPIP;

But when we try to connect using the above connection string we get teh following error:

"Test Connection failed because of an error in initializing provider. ERROR [IM004][Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed"

we are not getting wat is the problem. we are able to connect to this DB2 database from the same DB2 server but we are not able to connect to the server from a remote machine.

Thanks in advance.

What source did you use?

If you set up an ODBC entry in Windows, you can use a DataReader Source to go against that ODBC entry.

|||

Do you mean Provider, we used Odbc Data Provider

When we try create an ODBC entry it is throwing the same error.

|||

Murugan S wrote:

Do you mean Provider, we used Odbc Data Provider

When we try create an ODBC entry it is throwing the same error.

Then this doesn't sound like an SSIS problem. Perhaps the driver isn't installed correctly or is corrupt. Have you tried reinstalling the DB2 client?

|||

I have been trying different connection methods for some time trying to make SSIS "play nicely" with DB2 and unfortunately have not found a solution yet.

If someone out there has had success in doing so it would be very much appreciated if you could share the methodology and specific components you used to successfully retrieve data.

We're still using DTS on 2000 to export our IBM related data to SQL.

TR

|||

What type of connection are you using in DTS?

|||

in DTS I'm simply using an "Other Connection" and specifying the DSN that is connecting using the IBM Client Access Driver.

tr

Connecting to IBM DB2 USING SSIS

We need to connect to an DB2 server using ODBC provider, so we tried with the folowing:

Driver={IBM DB2 ODBC DRIVER};database=FirstDB; hostname=IP address; port=50000;protocol=TCPIP;

But when we try to connect using the above connection string we get teh following error:

"Test Connection failed because of an error in initializing provider. ERROR [IM004][Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed"

we are not getting wat is the problem. we are able to connect to this DB2 database from the same DB2 server but we are not able to connect to the server from a remote machine.

Thanks in advance.

What source did you use?

If you set up an ODBC entry in Windows, you can use a DataReader Source to go against that ODBC entry.

|||

Do you mean Provider, we used Odbc Data Provider

When we try create an ODBC entry it is throwing the same error.

|||

Murugan S wrote:

Do you mean Provider, we used Odbc Data Provider

When we try create an ODBC entry it is throwing the same error.

Then this doesn't sound like an SSIS problem. Perhaps the driver isn't installed correctly or is corrupt. Have you tried reinstalling the DB2 client?

|||

I have been trying different connection methods for some time trying to make SSIS "play nicely" with DB2 and unfortunately have not found a solution yet.

If someone out there has had success in doing so it would be very much appreciated if you could share the methodology and specific components you used to successfully retrieve data.

We're still using DTS on 2000 to export our IBM related data to SQL.

TR

|||

What type of connection are you using in DTS?

|||

in DTS I'm simply using an "Other Connection" and specifying the DSN that is connecting using the IBM Client Access Driver.

tr

Connecting to IBM DB2

We need to connect to an DB2 server using ODBC provider, so we tried witht he folowing:

Driver={IBM DB2 ODBC DRIVER};database=FirstDB; hostname=IP address; port=50000;protocol=TCPIP;

But when we try to connect using the above connection string we get teh following error:

"Test Connection failed because of an error in initializing provider. ERROR [IM004][Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed"

we are not getting wat is the problem. we are able to connect to this DB2 database from the same DB2 server but we are not able to connect to the server from a remote machine.

Thanks in advance.

Make sure you have the same driver installed on your remote machine as you have installed on the server. It may be possible that you have an out of date DB2 driver on your remote machine if nothing else is different.

Hope that helps,

John

Connecting to DB2 Database - Drivers Needed

I am trying to connect my reporting services server to a DB2 server - What
drivers / info do i need to accomplish this. I cant seem to figure out how
to get this to work. Any help would be appreciated.You need an ODBC driver or an OLEDB driver. Either will work theorectically.
I go against Sybase and for them I found that the ODBC driver worked better.
Just a few days ago there was someone going against an AS400 running DB2
that has had good luck with ODBC.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PCL" <PCL@.discussions.microsoft.com> wrote in message
news:16E09229-DEDF-4D37-9F9A-5D4897359C54@.microsoft.com...
> I am trying to connect my reporting services server to a DB2 server - What
> drivers / info do i need to accomplish this. I cant seem to figure out
how
> to get this to work. Any help would be appreciated.|||Thank You for your response - I guess i am not sure where i make this ODBC
connection. In .Net when i am creating a report it asks for a datasource
which I can use a DSN for. When i goto make a DSN, i do not see a generic
ODBC connection - I see ODBC for oracle but that is about it. How do i go
about creating this DSN?
Thanx!
"Bruce L-C [MVP]" wrote:
> You need an ODBC driver or an OLEDB driver. Either will work theorectically.
> I go against Sybase and for them I found that the ODBC driver worked better.
> Just a few days ago there was someone going against an AS400 running DB2
> that has had good luck with ODBC.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "PCL" <PCL@.discussions.microsoft.com> wrote in message
> news:16E09229-DEDF-4D37-9F9A-5D4897359C54@.microsoft.com...
> > I am trying to connect my reporting services server to a DB2 server - What
> > drivers / info do i need to accomplish this. I cant seem to figure out
> how
> > to get this to work. Any help would be appreciated.
>
>|||Assuming you have an ODBC driver installed, create a machine datasource
name. Then in RS create a new datasource, select the microsoft oledb for
odbc driver, you will then come to the screen that will allow you to pick
the DSN.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PCL" <PCL@.discussions.microsoft.com> wrote in message
news:C27653D2-CBA2-43BD-A9AF-2A31DF5488FA@.microsoft.com...
> Thank You for your response - I guess i am not sure where i make this ODBC
> connection. In .Net when i am creating a report it asks for a datasource
> which I can use a DSN for. When i goto make a DSN, i do not see a generic
> ODBC connection - I see ODBC for oracle but that is about it. How do i go
> about creating this DSN?
> Thanx!
> "Bruce L-C [MVP]" wrote:
> > You need an ODBC driver or an OLEDB driver. Either will work
theorectically.
> > I go against Sybase and for them I found that the ODBC driver worked
better.
> > Just a few days ago there was someone going against an AS400 running DB2
> > that has had good luck with ODBC.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "PCL" <PCL@.discussions.microsoft.com> wrote in message
> > news:16E09229-DEDF-4D37-9F9A-5D4897359C54@.microsoft.com...
> > > I am trying to connect my reporting services server to a DB2 server -
What
> > > drivers / info do i need to accomplish this. I cant seem to figure
out
> > how
> > > to get this to work. Any help would be appreciated.
> >
> >
> >|||On Fri, 4 Mar 2005 08:59:03 -0800, PCL <PCL@.discussions.microsoft.com>
wrote:
>I am trying to connect my reporting services server to a DB2 server - What
>drivers / info do i need to accomplish this. I cant seem to figure out how
>to get this to work. Any help would be appreciated.
You maybe out of luck. OLE DB Provider for DB2 is not something you
can obtain free or even separately for a fee. As far as I know, it
only comes with MS Host Integration Server 2000 and 2004 (~$2500). We
recently had a discussion with Microsoft about this, and they
confirmed that this is a known business problem.
If you end up buying Host Integration Server, I'd strongly suggest you
get the latest release (2004): the provider seems to be less bugged
(it had some annoying instability issues in 2000), plus they now
include a managed provider as well, which is nice to have, if you're
programming on .NET platform.
There are other options, like getting a provider from IBM (or even
some 3rd party), but those are not free either, and I cannot tell you
much about them...

Connecting to DB2 / AS 400 from SQL Server

Bonjour,
I wish to access a DB2 database wich is on a AS 400.
1) wich OLE db driver must we use ?
2) where can we download it ?
3) is it free ?
3) how to configure it ?
We have try different syntaxes without success :
Example :
sp_addlinkedserver @.server = 'AccesDB2_AS400_TRY1'
, @.srvproduct = 'Microsoft OLE DB Provider for DB2'
, @.provider = 'DB2OLEDB'
, @.location = 'ASILON'
, @.provstr = 'Initial Catalog=EPSILON;Data
Source=DB2;HostCCSID=1252;Network Address=192.168.1.15;Network
Port=50000;Package Collection=admin;Default Schema=admin;'
, @.catalog = 'EPSILON'
Our AS 400 server name is ASILON
Our AS 400 server adress IP is 192.168.1.15
The database name is EPSILON
What are we missing ?
thanks
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************Hi Frdric,
There should be an OLEDB DB2 provider distributed with MDAC. From here you
can download the latest version:
http://msdn.microsoft.com/data/mdac...ds/default.aspx
Here are a couple of good links providing information about formatting
connection strings:
http://www.carlprothman.net/Default.aspx?tabid=81
http://www.connectionstrings.com/
JT
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:%238ucAMsRGHA.2300@.TK2MSFTNGP11.phx.gbl...
> Bonjour,
> I wish to access a DB2 database wich is on a AS 400.
> 1) wich OLE db driver must we use ?
> 2) where can we download it ?
> 3) is it free ?
> 3) how to configure it ?
> We have try different syntaxes without success :
> Example :
> sp_addlinkedserver @.server = 'AccesDB2_AS400_TRY1'
> , @.srvproduct = 'Microsoft OLE DB Provider for DB2'
> , @.provider = 'DB2OLEDB'
> , @.location = 'ASILON'
> , @.provstr = 'Initial Catalog=EPSILON;Data
> Source=DB2;HostCCSID=1252;Network Address=192.168.1.15;Network
> Port=50000;Package Collection=admin;Default Schema=admin;'
> , @.catalog = 'EPSILON'
>
> Our AS 400 server name is ASILON
> Our AS 400 server adress IP is 192.168.1.15
> The database name is EPSILON
> What are we missing ?
>
> thanks
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************|||this posting made my day!!!
thanks!!!
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"JT" wrote:

> Hi Frédéric,
> There should be an OLEDB DB2 provider distributed with MDAC. From here you
> can download the latest version:
> http://msdn.microsoft.com/data/mdac...ds/default.aspx
> Here are a couple of good links providing information about formatting
> connection strings:
> http://www.carlprothman.net/Default.aspx?tabid=81
> http://www.connectionstrings.com/
> JT
> "SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
> news:%238ucAMsRGHA.2300@.TK2MSFTNGP11.phx.gbl...
>
>