Reading a DB2 database in SSIS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Reading a DB2 database in SSIS

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-04 : 11:43:08

How do I set up an ODBC data source (in data flow) to connect to DB2 file?

I'm using Visual Studio 2005 Service Pack 2?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-04 : 12:02:18

Make sure you have the DB2 driver installed. What platform does DB2 live on?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-04 : 13:15:28

It's on Sql Server 2005. I don't think I have that driver installed where do I get it?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-04 : 13:34:04

http://www.microsoft.com/downloads/details.aspx?FamilyId=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-04 : 14:15:59

Thanks but which directory do I install it in?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-04 : 14:29:36

I don't think it matters, the program will work regardless of where it's installed. In SSIS, Data Flow, OLEDB source you'll see the option for Microsoft DB2 OLE DB Provider.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-04 : 14:48:59

Tried to run the setup.exe but got failed to load resourse dll setupUl.dll. I guess I don't have administrative rights. When I added the OLE DB Source and clicked on new I have some of these options:

IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider
IBM DB2 UDB for iSeries IBMDARLA OLE DB Provider
IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider
IBM OLE DB Provider for DB2
Microsoft OLE DB Provider for SQL Server

Can I use any of these?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-04 : 15:01:38

Okay I'm back and was able to install the driver.

In the provider I have
Native OLE DB\Microsoft OLE DB Provider for DB2

For server or filename I've entered the name of the server which is sebcd1

When I selected Use Windows NT Integrated Security I don't get an options for any initial catalog.

When I tried User a specific user name and password and entered the info and test connection I got:

Test connection failed because of an error in initializing provider. The parameter is incorrect. Any suggestions?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-04 : 15:33:42

What platform does DB2 live on? Is it an AS/400? I'll use the Client Access driver in that case.

Integrated security isn't going to work if you're connecting to an AS/400.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-05 : 08:26:34

Sorry for just getting back to you I left for the day. Yes it sits on the AS/400 So would I use this one

IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-05 : 08:31:31

I'm sorry the DB2 connection sits on SQL Server 2005 not the AS/400

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-05 : 08:50:39

let's see the connection string. Again, can't use integrated security, has to be username/pwd

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-05 : 09:06:53

How do I find my port number?

Server=seb2a21:yourPortNumber;Database=Claims;UID=589457;PWD=claims;

Which connection manager do I select?