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?
Operating SystemsMicrosoft SQL ServerDB2

Avatar of undefined
Last Comment
Gary Patterson, CISSP

8/22/2022 - Mon
PedroCGD

You are receiving errors when try to connect to DB2 from SSIS package? Confirm?
Which errors?!
Regards,
Pedro
Gary Patterson, CISSP

Are you using the MS or the IBM DB2 provider?  How is the provider configured?

- Gary Patterson
ASKER
Viewpointcs

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
Viewpointcs

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
Gary Patterson, CISSP

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Viewpointcs

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Viewpointcs

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...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gary Patterson, CISSP

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