AS400/Iseries SSIS Connection Forgets Password From Agent 2

[Skittle]

I have a simple working package that reads data from an AS400/Iseries connection and writes it to an SQL server destination after a bit of data conversion.

The problem is that it only works as an interactive BIDS/Visual Studio 2005 run.

When I run the same package as a scheduled job, the error logging shows that the password for the AS400/Iseries account is not saved.

I have tried the obvious 'EncryptSensitiveWithUserKey' but still the password is not saved.

I am using a Data Reader source in my SSIS package that connects to a '.Net Providors\ODBC Data Providors' to a Data Source Name defined as a system ODBC connection ( Iseries Access ODBC ).

Has anybody managed to set up a successful read connection in SSIS 2005 to an AS400? Searching the internet it seems a common problem. I'm getting desperate enough to start thinking about writing a VB.Net program instead. I can work with the AS400/Iseries with a hard coded connection string that I simply cannot set up in SSIS.


Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[fredericofonseca]

Ive seen that happen with some client software.

Best thing I can suggest is that you save the password on a configuration table on SQL Server and populate it at runtime.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[RiverGuy]

EncryptSensitiveWithUserKey Means to encrypt it with your credentials. So in BIDS, you are running as something like SomeDomain\Skittle, which is what it will use to encrypt it. Your SQL Server Agent service account is probably something else, so it will not work.

If you choose EncryptWithPassword, you'll put your own password on the package, and the SQL Agent job step will ask you for that password when you are setting up the job. It will run fine that way.

Or, like Frederico said, use a configuration. You can also use configuration files.

 

[Skittle]

Thanks chaps. Very helpful. That one was getting to me.

Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.