MS SQL Server DTS - SSIS (SQL Svr 2005) - Oracle Password Always Null

Author

SSIS (SQL Svr 2005) - Oracle Password Always Null

Robert

2006-01-17, 3:23 am

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 encryptsensitivewith
password
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-

Michael Cheng [MSFT]

2006-01-18, 3:23 am

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#upgradeISfromEar
lierVersion

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.

Robert

2006-01-18, 9:23 am

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:

> 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#upgradeISfromEar
lierVersion
>
> 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.
>
>

Darren Green

2006-01-18, 8:24 pm

Robert wrote:
> 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.
>

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/Sh...194092&SiteID=1)

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

Robert

2006-01-19, 3:24 am

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:

> 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/Sh...194092&SiteID=1)
>
>
> --
> Darren
> http://www.sqldts.com
> http://www.sqlis.com
>

Michael Cheng [MSFT]

2006-01-19, 3:24 am

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.

David Siebert

2006-01-20, 1:23 pm

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:

> 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 encryptsensitivewith
password
> 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-

Sponsored Links