Hi Recently, I've developed a SSIS package containing few Control Flow Tasks, which includes one Data Flow task as well and it populates data From One Oracle Data Source Table to a SQL destination Table. My problem is : when I run it from BIDS in my local System, it is working perfectly. Then I deployed it to a SQL Server and when from that server, I'm executing the package from Integration services > Stored Packages > MSDB [right click on package name and 'Run Package' then 'Execute'] there also it is working fine. But while I'm creating a SQL Agent Job Step to execute the package it is even not starting execution of the package. I then created one simple execute sql task in a new package using this Oracle DB and a statement like 'select 1 from dual' ----- there also the observation is same. i.e it is not running from SQL job but is working fine from BIDS and Integration Services MSDB. I also checked TNSNAMES in SQL Server where I deployed it. Oracle is connecting fine with SQL Plus from there. N.B. I used password to encrypt my package always. Please advise if earlier anyone has faced similar issues. It seems I'm missing some simple thing, could it be some Env. variable that I need to check/set in SQL Server??? Thanks... Avik...Regards, Avik M.

create a proxy account for run SSIS under sql server agent: http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/69441/http://www.rad.pasfu.com

Hi Reza, Many thanks for your reply... but I should have already mentioned that I created one proxy too. That one is: 'Adminproxy' and all my packages are running smoothly with this proxy but only that package which is using one Oracle OLE DB connection is not working with it. Is there any special permission/configuration is required for this proxy to enable it to work with Oracle from SQL SSIS? Thanks again.... Regards, Avik M.

Do you have PATH environment variable available on the user account which you bounded to proxy account? for testing this go this way: log on to the server using the same account that proxy account runs under My Computer -> Properties advanced tab -> environment variables Click new, Name=PATH, value=C:\Oralce92\bin (or wherever your oracle drivers are installed) click ok until your back at the desktop, then reboot. reference: http://www.sqldev.org/sql-server-integration-services/running-a-ssis-package-as-a-job-error--with-a-connection-to-an-oracle-database-20867.shtmlhttp://www.rad.pasfu.com

I had a similar problem, and just I want to write for people who may face the same problem in future. Solution is: a. check the Path - usually the oracle path is entered automatically when the client is installed. This will be in the PATH env variable b. Restart the machine - v.v. important. While the SSIS packages can run in BIDS without re-starting the server, but this will "not load" if the package is run with an agent job or by dtexec. No error will be thrown, just the SSIS package will freeze. Just for more clarification, I used the following connection strings in SSIS connection managers: Data Source=XXX;User ID=...;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;Password=xxx OR Data Source=xxx;User ID=xxx;Provider=MSDASQL.1;Persist Security Info=False;Password=xxx Hope that helps !Shan