ODBC password not saved in configuration file

• Matt Allpress-274863

  Old Hand

  Points: 395

  More actions

  April 23, 2006 at 2:20 pm

  #113006

  Hi,

  Apologies if you've seen this before as I've posted this in the MSDN forums without a solution.

  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 have manually entered the password in the file.

  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. Has anyone else seen this problem before?

  Any help would be very appreciated.

  Thanks, Matt

• April 26, 2006 at 8:00 am

  This was removed by the editor as SPAM

• John R. Wuelzer

  SSC-Addicted

  Points: 410

  More actions

  May 2, 2006 at 6:57 am

  #635285

  I had the same issue, to get around it I am using configuration file stored in the file system that I had to manually open up and add the password to. Here is the contents of that config file (obviously the login and password have been changed to protect the innocent....)

  <?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading><DTSConfigurationFileInfo GeneratedBy="DOMAIN\net.user" GeneratedFromPackageName="DailyDWLoadfromAILINC" GeneratedFromPackageID="{2154B23A-ED78-4EB9-A626-AA873CC7A72B}" GeneratedDate="4/18/2006 9:02:26 AM"/></DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="\Package.Connections[AILINC.ISUSER].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>uid=ISUSER;pwd=PASSWORD;Dsn=AILINC;</ConfiguredValue></Configuration></DTSConfiguration>

  Hope that helps