Link to home
Create AccountLog in
Avatar of Viewpointcs
Viewpointcs

asked on

SSIS Connection to AS/400 DB2 - Cannot install Package Collections

Trying to use SSIS (SQL 2005 or 2008) to connect to DB2 database. SSIS (and MS Data Access Tools) attempts to install packages on DB2 for queries, but fails. We are using the security officer login. Do we need to use another login?
Avatar of PedroCGD
PedroCGD
Flag of Portugal image

You are receiving errors when try to connect to DB2 from SSIS package? Confirm?
Which errors?!
Regards,
Pedro
Avatar of Gary Patterson, CISSP
Are you using the MS or the IBM DB2 provider?  How is the provider configured?

- Gary Patterson
Avatar of Viewpointcs
Viewpointcs

ASKER

We enter the Connection Parameters (TCP/IP Port:446, Datasource Platform: AS/400, Initial Catalog, Package Collection, Default Schema). SSIS or Data Access Tool attempt to install the needed packages but report that package installation unsuccessful. We are using the QFECOFR login, which I would imagine has access to everything we need. I believe our two issues are 1) Does the QFECOFR account need to be granted specific access to a DB2 database, and 2) Since MS connection properties won't allow you to attempt the connection without a Package Collection, how do we find out what collections are on the DB2?

Thanks.
We are using the MS DB2 connector. Albeit crude, I have attached a Word document showing our current settings.
MS-DB2-Connector-Settings.doc
ASKER CERTIFIED SOLUTION
Avatar of Gary Patterson, CISSP
Gary Patterson, CISSP
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you for the info, Gary. One question...do you use WRKLIB to list the available packages or is there another command? Is there a default package name?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Finding the available packages allowed us to find a package name that would work with the SSIS connector. Upon testing, we could enter any of the existing package names into the connector...sort of to jumpstart it...and make the connection. Now, if we understood the impact/benefits of using one package over another...
An SQL package is just a container used to hold prepared SQL statements so that they can be reused (plus some statistics).  You typically organize them along either database or application lines, so that they get saved and restored along with their associated database or program object.

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzate/rzatepackagefaq.htm
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/sqlp/rbafysqlpack2.htm

- Gary Patterson