Following the instructions in books online, I created an Integration Services
Package to copy a table from an oracle database to a sql svr 2005 one. In
the package, i entered the oracle id and password in the oracle connection.
For the package I chose a protection level of encryptsensitivewithpassword
and supplied a password.

I then imported the package into Integration services. It asked me for the
password I had entered for the package which i then supplied.

Next, i created a SQL Server Agent job to run the package and tried to run it.

It failed due to the fact it passes the oracle password as NULL instead of
the one that I entered in the package.

There appears to be no way to get it to supply the oracle password when it
tries to connect to oracle.

What is the solution? Seems like a really easy task in SQL Server 2000 now
became impossible to figure out.

Thx;
R-

Hi Robert,

Which task you are using to copy the table from Oracle to SQL Server?

If you have a DTS package in the SQL Server 2000, you could refer the
articles below to migrate the DTS package directly to see whether Oracle
will pass the Password this time.

How to: Upgrade from Data Transformation Services to Integration Services
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/301cd966-3c69-4dbe-8a41-
36a0b695f9ea.htm

Upgrading or Migrating Data Transformation Services
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/c6294e8e-96b6-4eab-8329-
bc811be0ead6.htm#upgradeISfromEarlierVersion

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

THanks for your response.

I discovered how to correct my problem. For anyone else who gets confused,
when you import your package into SSIS, you can change the protection level.
Specifically, I got it to work by changing the protection level when
importing to be the mode the says "rely on server roles...." for protection.

Then it ran correctly.

thx.

"Michael Cheng [MSFT]" wrote:

Robert wrote:
This may work, but you may like to consider configurations which mean
you never have to save passwords and such like in packages. It makes
deployments, and development that much easier.

Here is a nice thread on the topic-

Re: ssis security short coming, major issues - MSDN Forums
(http://forums.microsoft.com/MSDN/Sho...94092&SiteID=1)


--
Darren
http://www.sqldts.com
http://www.sqlis.com

Thanks for the suggestion. I had already read that article earlier today.
The problem with that approach is that storing a oracle server password
unencrypted in an xml or text file is just not an option where i work.

"Darren Green" wrote:

Hi Robert,

It's great to hear you have resolved it by changing the protection level.

Thanks so much for you and Darren sharing the information here, which I
belive will benefit other communities as well.


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Robert,
I had the same problem with an FTP password. When you import the package
into Integration Services, you have the option selecting the package
protection level again (you can set it to something different than you
selected when designing). If you select the ServerStorage option, it will
save your encrypted credentials in SQL Server, and your job should run
successfully.

David

"Robert" wrote: