SSIS Dataflow task to iSeries

Question

SSIS Dataflow task to iSeries

Jim Covington

Old Hand

Points: 348
More actions
June 13, 2009 at 11:24 am

#384769

I have an SSIS dataflow task that retrieves a file from an IBM iseries. All works well.
I want to run as a dtExec at night unattended. However, it always prompts for a password even though I entered a password and checked the "allow saving password" in the IBMDASQL Oledb Provider.
Can anyone help?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

This operation requires Admin permissions in SSIS and ISeries and Admin permissions for the SQL Server Agent, these requirements are new to SQL Server 2005.

Kind regards,
Gift Peddie

Jim Covington Old Hand Points: 348 More actions · Answer 2

Would you give me specifics of how to do this?

Thanks

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

Check this thread below I have posted two MSDN forum threads one covers most of the issues with iSeries using Job to run the package.

http://www.sqlservercentral.com/Forums/Topic651485-148-1.aspx#bm653094

Kind regards,
Gift Peddie

Jim Covington Old Hand Points: 348 More actions · Answer 4

Gift:

I have sifted through the links you provided and I must be missing a step.

The problem I have when executing a package is I get the "Signon to iSeries" login prompt every time.

I want to run this package at night unattended in a batch environment. I could do this in SQL2000 dts.

I have the "Use a Specific User name and Password" checked and have provided a valid User name and Passord. Also, I have the "Allow Saving Password" checked. All of these are in the IBMDASQL data provider.

Why doesn't this work?

If this provider is incorrect, let me know the correct method.

Thank-you in advance.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 5

I know that worked in SQL Server 2000 that is the reason I worked with the person at the MSDN thread to get it to work, Microsoft have changed the whole thing. If you are in the Enterprise edition you could use the Microsoft IBM driver, if not I think you need the IBM ODBC driver. In the first link go to the second to the last post by the owner of the thread and he shows what version failed and what version worked and also explained in details which showed me Microsoft have changed everything we know in 2000. Microsoft wants the package owner to be Admin and the Agent to be Admin and you need to login as Admin to create the package. One more thing I was giving you my own threads I am Caddre.

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/f4104cb9-e734-4a57-a31b-cda42783c5fd/

http://blogs.msdn.com/dotnetinterop/archive/2006/01/19/oledb-provider-for-db2.aspx

Just to clarify, it was the 2nd link that helped me out. What I ended up doing was logging on as administrator to the local server and building the package. I have my personal login already setup as a SQL Server login with rights to run Jobs but it was still failing. I did not change the way the package itself was built. The only difference I can see between the way I was running it before and now is that the 2nd package was built with Administrator. Let me see if I can clarify.
1rst Package - Job Failed
SQL Server Agent running as \Administrator
Package Built with \mmanuel
Proxy AS400 has both logins above setup as principals
Job run as AS400 Proxy
2nd Package - Job Succeeded
SQL Server Agent running as \Administrator
Package Built with \Administrator
Proxy AS400 has both logins above setup as principals
Job run as AS400 Proxy

Kind regards,
Gift Peddie

Brandon Carl Goodman SSCrazy Points: 2458 More actions · Answer 6

SSIS does not store passwords......period. I work a great deal with AS400 data files and have found that the easiest method is to use a SQL configuration table that contains the AS400 connection string with the appropriate password. This allows myself a great deal of flexibility, especially since I have a dev/beta/prod environment.

You could create a job that runs your package however you would have to hardcode the password in which is not what you want as it stores the password in sys job steps. Not good either, plus anytime the job changes the password would need to be readded. Troubleshooting nightmare and not efficient.

As I have stated before the SQL Configuration table is the easiest and most flexible.

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 7

I have several SSIS packages that run queries against our AS400 daily. I have set up a linked server using a Data Source that connects using the iSeries ODBC Access driver. I store the iSeries password in an SSIS package configuration table.

vic_bam85 SSC Enthusiast Points: 180 More actions · Answer 8

I have the same problem, But when I try to use configuration packages it still has the same problem, beacuse it validates the connections before obtaining the informacion from the configuration table