ODBC password not saved in configuration file

Hi,

I have an SSIS package that pipes data from an iSeries data source to a SQL Server table. The connection manager I am using is the .Net Providers\Odbc Data Provider

When I run this SSIS package on my developer PC (from inside BIDS or using the Management Studio Execute Package Utility) it works. However when I run the package from within a SQL Server Agent Job it fails.

I have enabled logging and the error message given is:

System.Data.Odbc.OdbcException: ERROR [28000] [IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=8002 - CWBSY0002 - Password for user MALLPRESS on server SMICHC is not correct, Password length = 0, Prompt Mode = Never, System IP Address = 172.16.0.4 ERROR [28000] [IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=8002 - CWBSY0002 - Password for user MALLPRESS on server SMICHC is not correct, Password length = 0, Prompt Mode = Never, System IP Address = 172.16.0.4

It gives the message “Password length = 0” despite the fact that I have specified a password in the package.

To try to get around this I have added an XML configuration file to the SSIS package. In this configuration file I’ve set values for the ConnectionString, UserName, and Password.

I still get the above error about a password length of 0.

I have also tried to execute the package using dtexec, but this gives a similar error.

The SQL used to run this is:

xp_cmdshell 'dtexec /sq "\SCSRV-06\DTS Packages\Staging\StagingBranchImport"'

This has got me stumped, and I’m not sure where to go from here as these packages need to be programmatically run each morning. At the moment I am manually running them from within BIDS.

All packages that don’t use an iSeries connection manager run successfully in SQL Agent jobs.

I have also tried using the OLEDB connection manager (ibm db2 udb for iseries ibmda400 ole db provider) without success.

Any help would be very appreciated.

Thanks, Matt

SSIS will NOT store the password in the file for you. You have to edit the file manually to put the password in there. Have you done that?

-Jamie

|||

Hi Jamie,

Yes I have manually edited the config file and entered the password.

Matt

|||

Just to let anyone who's interested know....

I now have it working. The problem was that I did not have administrator access to the server, and so was using a network share to store the configuration file. I now how admin access to the windows server, and have moved the config file to the server's C:\, which has fixed the problem.

Matt

|||

Thanks for posting the solution, I am just ran into that problem today - ODBC works fine on local machine but when I move to production I get the password length error, even though I set the password in my package. I will try the package configuration file tomorrow.

I'm having the same problem with the iSeries Access OLEDB drivers - I can't seem to get them to work. Did you ever get them to work? I'm using 5v3 iSeries Access.